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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Coder
    Join Date
    Jan 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with matching two tables

    Hello

    I am trying to pull information from two different tables to be displayed in a php file. Both of these tables share the same field of product_id. I need to pull the product name from one table and the location of the image from another table and display them. This is for a store system and I'm randomly trying to show three products on my homepage. I am just unsure as to how to pull the information. I have code that connects to the DB already and randomly grabs a product name. My next step I am guess is to match the product_id of the name pulled to the other table and get the filename of the image stored in the other table. This is the code I already have.

    Code:
    <?php
    
    $link = mysql_connect("localhost", "tristate_store", "pword") or die (mysql_error ());
    
    mysql_select_db ('tristate_store', $link) or die (mysql_error ());
    
    $desc = mysql_query ("SELECT * FROM `storeproducts_description` ORDER BY RAND() LIMIT 0,1") or die (mysql_error ());
    
    $res = mysql_fetch_assoc ($desc);
    
    echo ''. $res['products_name'] . ' Click here ' . $res['products_id'];
    
    ?>
    Thanks in advanced for the help.
    Last edited by DJDex; 01-23-2007 at 03:21 AM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    look up how to do joins. you want to do a join of the two tables and get all the information you need at once, not the way you suggested it.

  • #3
    New Coder
    Join Date
    Jan 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok so do a join and then display the information? I'm not going to need another table or anything am I?

  • #4
    New Coder
    Join Date
    Jan 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok so I'm still stuck here guys. I'm not trying to display every product and its image. I dont know if I made that clear or not. what I am trying to do is only display like 3 or 4 items on my sites home page. it seems the join is gonna display all of the products. Can anyone else shed a little more light onto the subject

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    why don't you show us your join and we can help you from there. Note that you can use LIMIT with any number to decide what to show.

  • #6
    New Coder
    Join Date
    Jan 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Actually ok I got it to join, but my question now is since i used the field to join can I not call it? Cause I can't seem to call it.

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    What do you mean, call it? You certainly can include it in your SELECT clause, if that's what you're asking.

  • #8
    New Coder
    Join Date
    Jan 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is my code,

    Code:
    mysql_select_db ('tristate_store', $link) or die (mysql_error ());
    
    $query_prod = "SELECT products_image, products_name FROM storeproducts_description JOIN storeproducts ON storeproducts_description.products_id=storeproducts.products_id ORDER BY rand() LIMIT 1;";
    
    $prod = mysql_query($query_prod, $link) or die(mysql_error()); 
    
    $res = mysql_fetch_assoc ($prod);
    
    echo '<img width="150" src="http://www.tristatetuners.com/shop/images/' . $res['products_image'] . '"> <br>' . $res['products_name'] . ' ID ' . $res['storeproducts.products_id'] ;
    I have it pulling and doing what I need already but I need the product_id and when I added products_id to the select it said that it was ambiguis(sp?) so how would I go ahead and select it. I'm guessing I need a diff kind of join?

  • #9
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Ah ok that's clear. You are pulling data from two tables that both have a column named the same thing. Simply preface the column name with the table name (or the alias) followed by a dot to specify which table's column you want the data to come from. In your case, you are joining on the id, so it won't really matter which table you pull it from-- but you do need to specify a table or you will have that ambiguity issue.

    Code:
    SELECT products_image, 
    products_name,
    storeproducts.product_id
    FROM storeproducts
    JOIN storeproducts ON storeproducts_description.products_id=storeproducts.products_id

  • #10
    New Coder
    Join Date
    Jan 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    YES thank you very much, I knew it had to be something simple.

  • #11
    New Coder
    Join Date
    Jan 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Alright NOW I got another one, so I've repeated the code 3 times in my table to show 3 products. However, what way can I have it so that it doesn't repeat an item. Thanks a lot for the help guys I really appreciate it. I believe it'd be a if command but I could be wrong. Thanks again.

  • #12
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    what code did you repeat three times? the output php to show the items or the code grabbing the data from the database? You shouldn't be doing the latter.

    Have a quick look at the albums and artists link in my signature. while it might not directly apply, you will see how you can step through categories without having to repeat those for each subcategory item beneath it.

  • #13
    New Coder
    Join Date
    Jan 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I repeated the code thats about just changing the variables names adding 1 and then 2 ie res1 prod1 yada yada

  • #14
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Use a loop instead. I like "for" loops-- they have an incrementer built in.

    PHP Code:
    $prod mysql_query($query_prod$link) or die(mysql_error());
    for (
    $i 0$i mysql_num_rows($prod); $i++) {
        
    $productData[$i] = mysql_fetch_assoc($prod);


  • #15
    New Coder
    Join Date
    Jan 2007
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I dont see how that works. I only wanna display three random objects what your showing me seems that its going to run the entire loop and display all the products.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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