Jump to content

Select Name From ID In A Different Table


Ryno

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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;
}

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.