I have four tables:
products
products_prodnum - products_name
orderdet (details of each product ordered)
orderdet_ordernum - orderdet_prodnum
ordermain (general info of whole order)
ordermain_ordernum - ordermain_custnum
customers
customers_custnum
Right now I'm querying to get All Products purchased by Month
I'm getting the month correct and I'm actually getting the products ordered for that Month But I'm getting them twice.
CODE:
//GET PRODUCT LIST
$query = "SELECT * FROM products, ordermain, customers, orderdet
WHERE ordermain_custnum = customers_custnum
AND orderdet_prodnum = products_prodnum
AND orderdet_ordernum = ordermain_ordernum
AND MONTH(ordermain_orderdate) = MONTH(CURDATE())";
$results2 = mysql_query ($query)
or die(mysql_error());
while ($row2 = mysql_fetch_array ($results2)) {
extract($row2);
echo 'PRODUCT NAME
//GET PRODUCTS ORDERED
$query = "SELECT * FROM orderdet, ordermain
WHERE ordermain_ordernum = orderdet_ordernum
AND orderdet_prodnum = '$products_prodnum'
AND MONTHNAME(ordermain_orderdate)='$month'";
$results3 = mysql_query ($query)
or die(mysql_error());
while ($row3 = mysql_fetch_array ($results3)) {
extract($row3);
echo 'PRODUCTS ORDERED...
OUTPUT: (note that Product No. 0020 is listed twice:
Product No.: 0024 Product Name: #10 STANDARD 2 COLOR ENVELOPE
Order No.: 95 #10 STANDARD 2 COLOR ENVELOPE Quantity: 600
Product No.: 0001 Product Name: BROCHURE PACKAGE #1
Order No.: 95 BROCHURE PACKAGE #1 Quantity: 600
Product No.: 0020 Product Name: TRI-FOLD BROCHURE #1
Order No.: 95 TRI-FOLD BROCHURE #1 Quantity: 600
Order No.: 94 TRI-FOLD BROCHURE #1 Quantity: 400
Product No.: 0020 Product Name: TRI-FOLD BROCHURE #1
Order No.: 95 TRI-FOLD BROCHURE #1 Quantity: 600
Order No.: 94 TRI-FOLD BROCHURE #1 Quantity: 400
I can't see it for the trees....
