Can anyone suggest how approach this requirement

[Lassie]

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

[hitman6003]

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.

[Lassie]

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?

[hitman6003]

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

[Lassie]

Thanks. I will tyr and understand that.