View Full Version : Help with matching two tables

01-23-2007, 01:09 AM

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.


$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.

01-23-2007, 02:47 AM
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.

01-23-2007, 04:26 AM
Ok so do a join and then display the information? I'm not going to need another table or anything am I?

01-23-2007, 09:25 AM
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

01-23-2007, 01:50 PM
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.

01-24-2007, 02:23 AM
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.

01-24-2007, 06:12 AM
What do you mean, call it? You certainly can include it in your SELECT clause, if that's what you're asking.

01-24-2007, 07:20 AM
This is my 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?

01-24-2007, 07:34 AM
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.

SELECT products_image,
FROM storeproducts
JOIN storeproducts ON storeproducts_description.products_id=storeproducts.products_id

01-24-2007, 07:50 AM
YES thank you very much, I knew it had to be something simple.

01-25-2007, 08:22 AM
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.

01-25-2007, 02:16 PM
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.

01-26-2007, 04:42 AM
I repeated the code thats about just changing the variables names adding 1 and then 2 ie res1 prod1 yada yada

01-26-2007, 06:31 PM
Use a loop instead. I like "for" loops-- they have an incrementer built in.

$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);

01-26-2007, 10:37 PM
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.

01-27-2007, 01:25 AM
I was just going by what you wrote-- you didn't say anything about picking 3 random objects.

01-27-2007, 02:04 AM
Sorry about that I mentioned it in the first post :). SO what should I do then, would a Loop still be good or should I do as I mentioned before a If statement. I was having a little trouble trying to figure out if that would work.