Problem with while loop

[Lassie]

I am trying to show a history of items customers have ordered.

This requires retrieving the customer_id the order number and the order contents and then iterating the the items and descriptions.

I have several problems.

First when i retrieve the product-id and quanitity only the last item is retrieved.

Second I thought I would need to use an array to go through the variables and print them but this does not work.

 

$customer_id=1;//set to test code
$query= "SELECT order_number From customer_order Where customer_id = '$customer_id'";
$result = mysql_query($query);
$num_ord = mysql_num_rows($result);
  
  				if ($num_ord>0)
  
  			{
    			while($row = mysql_fetch_array($result))
    			{
    					$order_number=$row['order_number'];
    					echo "$order_number";//retieves all the order nos for a customer
    			}
    
   			}
				else
			{
  				echo "You have no previous purchases"; 
			}


			//select order items from from order contents and display

			$query= "SELECT product_id, quantity 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'];
			  $quantity=$row['quantity'];
			  echo"$product_id";//only shows last item
			  echo"$quantity";
			}
//retrieve the title of each product
			$query= "SELECT title FROM products Where product_id = '$product_id'";
			$result = mysql_query($query);
			while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
			{
			  $title=$row['title'];
			  
			  echo"$title";
			}

			//create an array to hold the variables
			$history= array($order_number,$product_id,$title,$quantity,);			

			echo '<pre>';
			print_r ($history);
			echo '</pre>';

							// Table header.
			echo '<table align="center" cellspacing="0" cellpadding="5">
			<tr>
			<td align="left"><b>order Reference</b></td>
			<td align="left"><b>Product Reference</b></td>
			<td align="left"><b>title</b></td>
			<td align="left"><b>Quantity</b></td>

			</tr>
			';

			// Fetch and print all the records.
			$bg = '#eeeeee'; // Set the background color.



			$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); // Switch the background color.
			echo '<tr bgcolor="' . $bg . '">
			<td align="left">"$history[0]"</td>
			<td align="left">"$history[1]</td>
			<td align="left">"$history[2]</td>
			<td align="left">"$history[3]</td>

			</tr>
			';


			echo '</table>';

mysql_free_result ($result); // Free up the resources.	

mysql_close(); // Close the database connection.

[boo_lolly]

i have no idea what you are trying to do here. why don't you give us more information about what you are trying to accomplish, what the problem is, and what your database tables look like.

[Lassie]

Hi,

Sorry I will restate my problem as follows:-

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 errors i encounter are:

While the order query gives multiple values ( where applicable) the the query against the order contents only returns 1 record.

Similary my array $history only holds 1 record.

 

The database tables are:

 

customer_order

 

order_number , int, auto_inc, primary

customer_id, int

total, decimal(10)

order_date, date

 

order_contents (No primary index set)

 

order_number, int

product_id, int

quantity, decimal(3 0)

price decimal(6 2)

ship_date, datetime

 

products

 

product_id, int, auto_inc, primary

cat_id, int

title, varchar60

product_desc , varchar 255

price, decimal(6.2)

 

The code is:-

 

<p>View Your previous choices</p>
<?php
//connect to database
//retrieve customer_id to query order contents
$customer_id=3;
$query= "SELECT order_number From customer_order Where customer_id = '$customer_id'";
$result = mysql_query($query);
$num_ord = mysql_num_rows($result);
  
  				if ($num_ord>0)
  
  			{
    			while($row = mysql_fetch_array($result))
    			{
    					$order_number=$row['order_number'];
    					echo "$order_number";
    			}
    
   			}
				else
			{
  				echo "You have no previous purchases"; 
			}


			//select order items from from order contents and display


			$query= "SELECT product_id, quantity 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'];
			  $quantity=$row['quantity'];
			  echo"$product_id";
			  echo"$quantity";
			}





			$query= "SELECT title FROM products Where product_id = '$product_id'";
			$result = mysql_query($query);
			while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
			{
			  $title=$row['title'];
			  
			  echo"$title";
			}

			//create an array to hold the variables
			$history= array($order_number,$product_id,$title,$quantity,);			

			echo '<pre>';
			print_r ($history);
			echo '</pre>';

							// Table header.
			echo '<table align="center" cellspacing="0" cellpadding="5">
			<tr>
			<td align="left"><b>order Reference</b></td>
			<td align="left"><b>Product Reference</b></td>
			<td align="left"><b>title</b></td>
			<td align="left"><b>Quantity</b></td>

			</tr>
			';

			// Fetch and print all the records.
			$bg = '#eeeeee'; // Set the background color.



			$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); // Switch the background color.
			echo '<tr bgcolor="' . $bg . '">
			<td align="left">'. $history[0] . '</td>
			<td align="left">'. $history[1] . '</td>
			<td align="left">'. $history[2] . '</td>
			<td align="left">'. $history[3] . '</td>

			</tr>
			';


			echo '</table>';

mysql_free_result ($result); // Free up the resources.	

mysql_close(); // Close the database connection.


// Include the HTML footer file.
include ('./includes/footer.html');
?>

 

 

i appreciate your help.

Lassie

 

 

[OOP]

Hi there,

 

In the first query $order_number holds the value of last result in the loop iteration so when you use it in the second query you will get only one record.The same thing in your third query,$product_id holds the value of last record in the second query.

[Lassie]

Thanks for your reply. How do I populate the variables with multiple values?

I am missing something basic I guess.

[Lassie]

Just to add that after the first query $order_number does hold multiple values. I echo out $order_number as a debug, but as you say this is not the fact in the second query. Should I be looping through $order_number

and running the 2nd query?