[Resolved] Looping through records

[riojimmy]

My loop is only returning the first record:
I need to get ALL Customers their info and then all products they ordered.
I get the first customer and the products ordered but the loop stops there. How do I get it to continue looping through the rest of the records?

First query:
$query = "SELECT * FROM customers";
$results = mysql_query ($query)
or die(mysql_error());
$customers_custnum=$id;
while ($row = mysql_fetch_array ($results)) {
extract($row);

echo customer information
then the second query

if($id=$customers_custnum) {

$query = "SELECT * FROM orders, customers WHERE orders_custnum = '$id'";
$results = mysql_query ($query)
or die(mysql_error());
while ($row = mysql_fetch_array ($results)) {
extract($row);

echo products ordered

}
}

[liquorvicar]

is your second query inside your first query loop? if so, you should rename $results and $row in the second query to stop them overwriting the values from the first query.

[riojimmy]

thank you liguorvicar,
That should have occured to me--DUH--but it didn't.

I renamed them and now I'm getting all the records, however
if customer b ordered three products I get:

Customer b
product 1
product 2
product 3
Customer b
product 1
product 2
product 3
Customer b
product 1
product 2
product 3

??

[liquorvicar]

can you post your current code so we can have a proper look?

btw, it's liquorvicar with a Q not a G.

[riojimmy]

<table width="100%" border="1" cellspacing="0" cellpadding="2" bgcolor="FFFFFF">';
$query = "SELECT * FROM customers";

$results = mysql_query ($query)
or die(mysql_error());
$customers_custnum=$id;
while ($row = mysql_fetch_array ($results)) {
extract($row);

echo '<tr>

<td bgcolor="#DDDDDD">Agent Name:</td>
<td" bgcolor="#DDDDDD">';
echo $customers_firstname;
echo ' ';
echo $customers_lastname;
echo '&nbsp;</td>
<td bgcolor="#DDDDDD">E-mail: </td>
<td bgcolor="#DDDDDD">';
echo $customers_email;
echo '&nbsp;</td>
<td bgcolor="#DDDDDD">Agent Code: </td>
<td bgcolor="#DDDDDD">';
echo $customers_agentno;
echo '&nbsp;</td>
</tr>
<tr><td colspan="6" bgcolor="#CCFFFF">';

if($id=$customers_custnum) {
$query = "SELECT * FROM orders, customers WHERE orders_custnum = '$id'";
products_prodnum";
$results3 = mysql_query ($query)
or die(mysql_error());
while ($row3 = mysql_fetch_array ($results3)) {
extract($row3);
echo '<tr>
<td bgcolor="#FFFF99">Product:</td>
<td bgcolor="#FFFF99">';
echo $orders_name;
echo '</td>
<td bgcolor="#FFFF99">Quantity:</td>
<td bgcolor="#FFFF99">';
echo $orders_qty;
echo '</td>
<td bgcolor="#CCFF99">Customized:</td>
<td bgcolor="#CCFF99">';
echo $orders_cust;
echo '</td>
</tr>';
}
}
}
echo '</table>';

[liquorvicar]

if($id=$customers_custnum) { 

this will always evaluate to TRUE.
do you mean:

if($id==$customers_custnum) { 

that may solve your problem.
= is the assignment operator (i.e. make left side equal to right side).
== is the comparison operator (i.e. check to see if the left side equals the right side)
also do you need to do the query on both tables like this:

$query = "SELECT * FROM orders, customers WHERE orders_custnum = '$id'";

you may be better off with a data structure where cutnum is stored in the orders table and then just:

$query = "SELECT * FROM orders WHERE orders_custnum = '$id'";

would work

[riojimmy]

Thanks again liquorvicar

I tried that but now all I get is customers info, it returns nothing from the orders table. Have I assigned the value to ID correctly?

$query = "SELECT * FROM customers";
$results = mysql_query ($query)
or die(mysql_error());
$customers_custnum=$id;
while ($row = mysql_fetch_array ($results)) {
extract($row);
echo '<tr>
<td>Agent Name:</td>
<td>';
echo $customers_firstname;
echo ' ';
echo $customers_lastname;
echo ' </td>
<td>E-mail: </td>
<td>';
echo $customers_email;
echo ' </td>
<td>Agent Code: </td>
<td>';
echo $customers_agentno;
echo ' </td>
</tr>
<tr><td colspan="6" bgcolor="#CCFFFF">';

if($id==$customers_custnum) {
$query = "SELECT * FROM ordermain WHERE ordermain_custnum = '$id'";
$results3 = mysql_query ($query)
or die(mysql_error());
while ($row3 = mysql_fetch_array ($results3)) {
extract($row3);
echo '<tr>
<td>Product:</td>
<td>';
echo $orders_name;
echo '</td>
<td>Quantity:</td>
<td>';
echo $orders_qty;
echo '</td>
<td>Customized:</td>
<td>';
echo $orders_cust;
echo '</td>
</tr>';
}
}
}
echo '</table>';

(oh, ever given any thought to the Chaucerian spelling licuorvicar?)

[liquorvicar]

well, as it stands, you're not actually assigning a value to $id to use in your second query. i assume you could use something like $customers_custnum in the second query. (i don't know what your data structure is, that last variable should be whatever picks up the customer number in the first query as referred to in the second query).
do you actually need your 'if' statement for the second query? i can't quite see what work it's doing.

a board poster who's into chaucer?

[riojimmy]

I don't know I just can't get this to work,
I need to get the customer and the products ordered by that customer

I as it stands I get only a list of customers or

Each customer with all products ordered by everyone

I can't seem to winnow it down to the individual customer.

(actually blame it on higher education and Pasolini's Canterbury Tales)

[riojimmy]

I got it working, I just used and if clause :

while ($row3 = mysql_fetch_array ($results3)) {
extract($row3);
if($customers_custnum==$orders_custnum) {
etc. etc

now I get:
Customer1
Item 1
Item 2
Customer2
Item 1
Customer3
Item 1
Item 2

etc....

liquorvicar
Thanks for talking it out
As they would have done in the olden days, in a cold damp hovel around a crackling fire.