Welcome to WebmasterWorld Guest from 34.229.126.29

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

displaying from multiple tables

     
4:32 am on Aug 25, 2005 (gmt 0)

New User

10+ Year Member

joined:July 16, 2005
posts:37
votes: 0


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!

6:56 am on Aug 25, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Mar 30, 2003
posts:3719
votes: 0


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

7:29 am on Aug 25, 2005 (gmt 0)

New User

10+ Year Member

joined:July 16, 2005
posts:37
votes: 0


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?

8:17 am on Aug 25, 2005 (gmt 0)

Full Member

10+ Year Member

joined:Apr 21, 2004
posts:306
votes: 0


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.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members