Ryno Posted June 29, 2008 Share Posted June 29, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/112421-select-name-from-id-in-a-different-table/ Share on other sites More sharing options...
br0ken Posted June 29, 2008 Share Posted June 29, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/112421-select-name-from-id-in-a-different-table/#findComment-577294 Share on other sites More sharing options...
Ryno Posted June 29, 2008 Author Share Posted June 29, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/112421-select-name-from-id-in-a-different-table/#findComment-577329 Share on other sites More sharing options...
br0ken Posted June 29, 2008 Share Posted June 29, 2008 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; } Quote Link to comment https://forums.phpfreaks.com/topic/112421-select-name-from-id-in-a-different-table/#findComment-577333 Share on other sites More sharing options...
Ryno Posted June 29, 2008 Author Share Posted June 29, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/112421-select-name-from-id-in-a-different-table/#findComment-577355 Share on other sites More sharing options...
br0ken Posted June 29, 2008 Share Posted June 29, 2008 Koo, Test1, Test1, Test1, Test1 are the names of the products?! Do you not understand what this query is doing? Quote Link to comment https://forums.phpfreaks.com/topic/112421-select-name-from-id-in-a-different-table/#findComment-577364 Share on other sites More sharing options...
Ryno Posted June 29, 2008 Author Share Posted June 29, 2008 Not really. Its displaying the 1st record four times, and the second once. Quote Link to comment https://forums.phpfreaks.com/topic/112421-select-name-from-id-in-a-different-table/#findComment-577374 Share on other sites More sharing options...
br0ken Posted June 29, 2008 Share Posted June 29, 2008 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"); Quote Link to comment https://forums.phpfreaks.com/topic/112421-select-name-from-id-in-a-different-table/#findComment-577387 Share on other sites More sharing options...
Ryno Posted June 29, 2008 Author Share Posted June 29, 2008 No. Is it only displaying the info from the first record in the orders table. Quote Link to comment https://forums.phpfreaks.com/topic/112421-select-name-from-id-in-a-different-table/#findComment-577395 Share on other sites More sharing options...
br0ken Posted June 29, 2008 Share Posted June 29, 2008 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?! Quote Link to comment https://forums.phpfreaks.com/topic/112421-select-name-from-id-in-a-different-table/#findComment-577399 Share on other sites More sharing options...
Ryno Posted June 29, 2008 Author Share Posted June 29, 2008 Dont worry anymore Its getting too confusing. Quote Link to comment https://forums.phpfreaks.com/topic/112421-select-name-from-id-in-a-different-table/#findComment-577401 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.