Reference identifying row in 2 different tables, combining records from the 2nd table

[sleepyw]

Sorry for the confusing title....best way I could explain it briefly.

 

I have 2 tables. For the sake of discussion, we'll say TABLE1 is a db of customer information, and TABLE2 is a db of that customer's orders (so there are multiple rows in TABLE2 with each order they've placed).

 

In TABLE1, there is an ID field (the unique customer number). In TABLE2, that number is also used for each row for that customer's orders (to identify that customer).

 

What I'm trying to do is create a HTML table as an output on a page that will display some info from the customer from TABLE1, but then I want to total up the customer's orders from TABLE2 and display that combined amount in the same HTML table.

 

I thought I could do it, but after sitting and trying to code this for a couple hours, it became clear I was missing something, as what I'm doing to combine and total the relevant info from TABLE2 is not working.

 

Here's what I'm doing, even though the 2nd half of it is wrong. I'm hoping someone can spot my error(s) and point me in the correct direction. I'm obviously not going about it the right way.

<?
// get id numbers for relevant customers
   $result1 = mysql_query('SELECT id, customer_name FROM TABLE1');
   while($row1 = mysql_fetch_array($result1)){

// run query of order amounts in TABLE2 based on customer id from TABLE1
   $result2 = mysql_query('SELECT order_total FROM TABLE2 WHERE id="$row1[id]"');
   while($row2 = mysql_fetch_array($result2)){
   $totaldollars += $row2['order_total'];}
?>

<tr>
   <td align="center" valign="top"><? echo $row1['customer_name']; ?> </td>
   <td align="center" valign="top">$ <? echo $totaldollars; ?> </td>
</tr>

Edited November 11, 2013 by sleepyw

[mac_gyver]

your most immediate problem is because php variables are not replaced with their value when used inside of a single-quoted string. you should use double-quotes for the initial and final quotes around sql query strings, with single-quotes around pieces of string data within it.

 

however, for what you are doing, you need to learn how to write JOIN'ed queries and do this using one query. there's plenty of information to be found on the Internet.

[sleepyw]

I managed to figure it out. Here's essentially what I did:

mysql_query('SELECT TABLE1.id, TABLE1.customer.name, SUM(TABLE2.orders) as total_dollars FROM TABLE1, TABLE2 WHERE TABLE1.id = TABLE2.id GROUP BY id');