Select Name From ID In A Different Table

[Ryno]

Okay I've Read http://www.phpfreaks.com/forums/index.php/topic,203921.msg923805.html on query's but I'm not sure how to make it work. I have a shopping cart the same as that post but i'm not sure how to do that. I have the ids of the products ordered in Orders->ordered like this 1,3,2,1,3,4 but the name of the products is in Products->name and the id column in that table is ID.

 

How can i make it select the ID in the products table from the orders table and display the name from the name column?

[br0ken]

I haven't seen the thread you link to but my guess is you would do a join.

 

SELECT p.id, p.name FROM tblProduct p, tblOrder o WHERE p.id = o.productID

 

You'll probably have to change some field names and table names around to get it to work but that should help you understand what is required.

[Ryno]

Okay Thanks.

Ive changed it to

<?php $product_order = mysql_query('SELECT id, name FROM Products p, Orders o WHERE id = o.id') ?>

Now can I just echo $product_order. Have I changed the query right?

[br0ken]

You changed some of the query around right but you didn't access it's results right at all. The query returns a set of results which you must iterate over and extract each result like this.

 

You must change where it says o.productID in the where clause to what ever the field is called in the Orders table that stores the product ID and you must put o. before it.

 

$rs	= mysql_query("SELECT p.id, p.name FROM Products p, Orders o WHERE p.id = o.productID order by p.name ASC");
$row	= 0;

while(mysql_fetch_row($rs))
{
$id	= mysql_result($rs, $row, "id");
$name	= mysql_result($rs, $row, "name");

echo "The product called $name has the id $id.<br />";
++$row;
}

[Ryno]

Ive changed the code like this:

<?php  
			  
			  $rs	= mysql_query("SELECT p.id, p.name FROM Products p, Orders o WHERE p.id = o.ordered order by p.name ASC");
$row	= 0;

while(mysql_fetch_row($rs))
{
$id	= mysql_result($rs, $row, "id");
$name	= mysql_result($rs, $row, "name");

echo "The product called $name has the id $id.<br />";
++$row;
}
			  
			  ?>

But it just displays this

 

The product called koo has the id 2.

The product called Test1 has the id 1.

The product called Test1 has the id 1.

The product called Test1 has the id 1.

 

How can I make it display the names of the products selected?

[br0ken]

Koo, Test1, Test1, Test1, Test1 are the names of the products?! Do you not understand what this query is doing?

[Ryno]

Not really. Its displaying the 1st record four times, and the second once.

[br0ken]

It's displayed two different records, ID 1, and ID 2 however it is displaying ID 1 more times. This is because you're linking the products table with the order table and the product with ID 1 must have been purchased several times.

 

If you want to see all products purchased but no duplicates, use this.

 

$rs	= mysql_query("SELECT DISTINCT p.id, p.name FROM Products p, Orders o WHERE p.id = o.ordered order by p.name ASC");

[Ryno]

No. Is it only displaying the info from the first record in the orders table.

[br0ken]

The product called koo has the id 2.
The product called Test1 has the id 1.
The product called Test1 has the id 1.
The product called Test1 has the id 1.

 

Maybe I'm confused or just dumb but I can see two different ID's in there. What do you mean?!

[Ryno]

Dont worry anymore Its getting too confusing.