displaying from multiple tables

[michlcamp]

I know how to query two tables for the data I'm looking for, but I don't know how to display it. One table has customer information, the other table has orders placed by the customers.

I'm using this query:
SELECT customers.realname, orders.product FROM customers, orders WHERE customers.realname=orders.realname;

now do I write a WHILE statement?
How to display the data on a page?

looing to display it like this:

Customer:Product
Customer:Product
Customer:Product

All help much appreciated!

[dreamcatcher]

Hi michlcamp,

When you loop build your table data cells as needed. Something like this:

echo "<table>\n";

while ($row = mysql_fetch_assoc($query))
{
echo "<tr>\n";
echo "<td>".$row['customer']."</td>\n";
echo "<td>".$row['product']."</td>\n";
echo "</tr>\n";
}

echo "</table>\n";

Something like that should be ok.

dc

[michlcamp]

I'm lost in the php triangle tonight!

first my very ugly query:
$query = "SELECT customers.realname, products.item, orders.date, orders.qty1, orders.qty2, orders.qty3, orders.qty4, orders.qty5, orders.qty6,orders.qty7, orders.qty8, orders.qty9 FROM customers, products, orders WHERE customers.realname=orders.realname AND customers.date=orders.date";

what I'm actually trying to do (now) is show the quantity ordered for each product item, to produce a report for each/all/any online order.

No doubt I'll have to modify the query against the two tables: 'products','orders'

'products' has a field named 'item' for product names
(item1,item2,item3,etc)

'orders' has qty field for quantity of each item ordered
(qty1,qty2,qty3,etc)

I want the loop to display:
item1:qty1
item2:qty2
item3:qty3
item4:qty4
etc

Your code gave me my list of products BUT NOT QUANTITY
echo "<tr>\n";
echo "<td>".$row['item']."</td>\n";
echo "<td>"BUT WHAT DO I PUT RIGHT HERE?"</td>\n";
echo "</tr>\n";
any thoughts?

[roldar]

while ($row = mysql_fetch_assoc($query))
{
echo "<tr>\n";
echo "<td>".$row['customer']."</td>\n";
echo "<td>".$row['product']."</td>\n";
echo "</tr>\n";
}

Is this what you're looking for?

-------

$number_of_products=mysql_num_rows($query);

for($m=1;$m<=$number_of_products;m++)
{
$row=mysql_fetch_array($query, MYSQL_ASSOC);
$qtynum="qty".$m;
$itemnum="item".$m;
echo "<tr>\n";
echo "<td>".$row[$itemnum]."</td>\n";
echo "<td>".$row[$qtynum]."</td>\n";
echo "</tr>\n";
}

---------

If this is in fact what you're looking for, I would suggest you look at your table structure before you go too far with this.

I'd ditch the hardcoded "qtyx" and "itemx" columns. You should have a separate row for each item in your product table rather than a single row with all the different products listed in columns. Each row/item should also have a unique productID.

Same goes for the orders table. I'd put an orderID, batchID, and quantityOrdered in there, as well as references to the customerID, productID. The batchID will be used to tie all the different item types in an order together. The orderID will be unique to each type of product a customer buys in a single order.

If you had it set up this way you'd have to do a little more looping but it will be much cleaner and will scale better.

<edit>so sleepy, so many errors... I think I got most of them.. off to bed</edit>

Actually, looking back I'm kind of confused. Do you only have a single row in your products table with all the item names in separate "itemx" columns? If so, there's only one record to be had there. For the method I have above, and assuming I understand how you've got your tables set up, you will have to make a separate query for the product names...

-------
$products=mysql_query("SELECT * FROM products");
$productsArray=mysql_fetch_array($products, MYSQL_ASSOC);

$number_of_products=mysql_num_rows($query);

for($m=1;$m<=$number_of_products;m++)
{
$row=mysql_fetch_array($query, MYSQL_ASSOC);
$qtynum="qty".$m;
$itemnum="item".$m;
echo "<tr>\n";
echo "<td>".$productsArray[$itemnum]."</td>\n";
echo "<td>".$row[$qtynum]."</td>\n";
echo "</tr>\n";
}

---------

Then ditch the part of the original query that was grabbing the product names from the products table.