Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Apr 2004
    Location
    CA, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mutliple table query/result problem

    At this point, this is more a matter of theory as opposed to what line for line code I should use.

    I populate an array with `mfgID` and `mfgName` from table `mfg`.

    Then I pull all the `prodName` and `prodMfg` (which is equal to the mfgID. I know, I should have just called it `mfgID`, but the `mfg` table came about after the original db was setup. I could change it over if it is necessary to accomplish the goal) out of `products` and loop through the results.

    THE PROBLEM:
    I need to associate the `prodMfg` with `mfgName` to display the manufacturer of the product. I haven't been able to get this to work. Originally, I had put a query to `mfg` to get the `mfgName` inside the loop results, but this made the original results stop looping and I ended up with one product.

    I am definitely open to a better way to go about this.

  • #2
    Regular Coder
    Join Date
    May 2002
    Location
    Virginia, USA
    Posts
    620
    Thanks
    0
    Thanked 6 Times in 6 Posts
    This isn't hard, I'm just not sure exactly what you want....

    You could possibly do:

    "select mfgID FROM onetable WHERE onetable.mfgName = othertable.prodMfg"

    I'm not sure that is entirely correct, though

    er wait....yeh, I'm not exactly sure what you want. Give us some code, lol.

  • #3
    New Coder
    Join Date
    Apr 2004
    Location
    CA, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have stripped away all the html, here is the code I am working with:
    PHP Code:
    <? 
    $query 
    "SELECT * FROM mfg";
         
    $result mysql_select_db ($db$connection);    
         
    $result mysql_query ($query);
        while (
    $myrow mysql_fetch_array($result)) {
            
    $mfgName $myrow[1];
            
    $mfgID $myrow[0];
    }
    ?>
    I need to get the $mfgName to display as $prodMfg in the results below:
    PHP Code:
    <?

     $query 
    "SELECT * FROM  products WHERE prodStore = 'Health Food' ORDER BY prodName";
        
    $result mysql_select_db ($db$connection);
         
    $result mysql_query ($query);
         while (
    $myrow mysql_fetch_array($result)) {  
            
    $prodID $myrow[0];
            
    $catID $myrow[1];
            
    $prodName $myrow[2];
            
    $prodMfg $myrow[3];
            
    $prodDesc $myrow[5];
            
    $photoID $myrow[10];
                            
                                
    ?>

    <? echo $prodName ?> by 
    <? echo $prodMfg //(This needs to be the $mfgName) ?>

    <? ?>
    $prodMfg stores a $mfgID.
    I need to echo the $mfgName (as $prodMfg) WHERE $prodMfg = $mfgID, and have it continue to loop through all the results.

    Does this clarify anything? I am still at a point where I can change table/db structure if I really have to.
    Last edited by empiredesign; 04-27-2004 at 06:43 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •