Lassie Posted March 8, 2007 Share Posted March 8, 2007 I need to combine the result of 2 querys on my database to provide a lists of purchase history information. The logic is:- 1 Select the order_number(s) from the customer order details. 2.Select the product_id and quantity from the order_contents table 3.Using the product_id Select the title of the product form the product table. 4 Put all the variables into an array and print the results. The problem I have is that the whilst the first query returns all the orders from a selected customer the subsequent SELECT querys will return only the last entry. For example a customer may have placed 2 orders, no 4 & 5 but my SELECT query gives me only the detail of order 5.This detail would be the product id which is then used to retireve the product description. Again, the order could have several items and therefore I need the query to return all the product descriptions. I am giving this explantion because I think I am approaching the task wrongly and would welcome any suggestions. My basic question is, how do I retireve multiple results form a select query. I post the product query below as an example of where I have got to. $query= "SELECT product_id From order_contents Where order_number = '$order_number'"; $result = mysql_query($query); while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) { $product_id=$row['product_id']; } $query= "SELECT title FROM products Where product_id = '$product_id'"; $result = mysql_query($query); while ($row = mysql_fetch_array($result)) { $title=$row['title']; } Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted March 8, 2007 Share Posted March 8, 2007 Use a join to grab the info you want in a single query.... SELECT p.title FROM order_contents oc LEFT JOIN products p ON oc.product_id = p.product_id WHERE oc.order_number = $order_number There is no need to wrap the $order_number in single quotes assuming that the order_number column is an INT...when you do it causes MySQL to have to do a type conversion. Quote Link to comment Share on other sites More sharing options...
Lassie Posted March 8, 2007 Author Share Posted March 8, 2007 Thanks for coming back.Yes the order_number is an int. I think you are saying that I can select the product_id form the order contents table and using that query the product table to retirieve the title. This would be good. If that is the case how do I retireve the same set of information for the next order. As it is at present I can get all the orders for a particular customer eg customer 3 has 2 orders 4&5, but I cant work out how to get my query, that gets the product id from the order contents table, to retrieve the details for order 4.It will do just the last numeric order no. Do I need to loop through the query somehow? Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted March 8, 2007 Share Posted March 8, 2007 Just change the where clause: SELECT oc.order_number, p.product_id, p.title FROM order_contents oc LEFT JOIN products p ON oc.product_id = p.product_id WHERE oc.customer_name = "Some Customer" ORDER BY oc.order_number Then use php to generate a table: $result = mysql_query("...") or die(mysql_error()); echo '<table>'; while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo ' <tr> <td>' . $row['order_number'] . '</td> <td>' . $row['prodcut_id'] . '</td> <td>' . $row['title'] . '</td> </tr>'; } echo '</table>'; Quote Link to comment Share on other sites More sharing options...
Lassie Posted March 8, 2007 Author Share Posted March 8, 2007 Thanks. I will tyr and understand that. Quote Link to comment 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.