Problem joining tables with sum()

General PHP Help
#1

I am trying to create a table that shows each order and the amount of product ordered,shipped and remaining. so far no luck. im not seeing any errors but the table isnt displaying correctly.
here is the code please help!!

[php]<?php

$result = mysql_query(“SELECT orders.ID, orders.two_ply, orders.three_ply, orders.ordered_by, orders.ponumber, orders.end_shipping, shipping.ponumber, SUM(shipping.twoply) AS twoply, SUM(shipping.threeply) AS threeply
FROM orders, shipping
WHERE orders.ponumber=shipping.ponumber”) or die( mysql_error());

$orderprogress="

"; echo $orderprogress;

while($row = mysql_fetch_array($result))
{
echo “

”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;

}
echo “

Order ID 2ply Ordered 2ply Shipped 2ply Remaining 3ply Ordered 3ply Shipped 3ply Remaining Ordered By PO Number Ship by Date
” . $row[‘ID’] . “” . $row[‘two_ply’] . “” . $row[‘twoply’] . “” . ($row[‘two_ply’]-$row[‘twoply’]) . “” . $row[‘three_ply’] . “” . $row[‘threeply’] . “” . ($row[‘three_ply’]-$row[‘threeply’]) . “” . $row[‘ordered_by’] . “” . $row[‘ponumber’] . “” . $row[‘end_shipping’] . “
”;

mysql_close();
?>[/php]

#2

Hi,

I should be able to help you with this, but I need to know a little more.

It looks like you have two tables.

The first is named “orders” and the second named “shipping”

Orders has the following columns: ID, two_ply, three_ply, ordered_by, ponumber, end_shipping

Shipping has the following columns: ponumber, twoply, threeply

You are using the ponumber as a common column to join the two tables with

There are no other common column names in the two tables

It appears that a given ponumber will appear in more than one row of the shipping table and that you wish to sum the totals for twoply and threeply for all rows that have this ponumber.

Will the ponumber appear only once in the order table? If so, and all of the above is correct, try this:SELECT ID, two_ply, three_ply, ordered_by, ponumber, end_shipping, SUM(twoply) AS twoply, SUM(threeply) AS threeply FROM orders JOIN shipping USING(ponumber) GROUP BY ponumber

#3

This worked perfectly. Thank you Malasho. I Actually had an ID field in both tables so i just changed the SELECT ID to SELECT orders.ID

Now it works great. Appreciate the help! I’m looking forward to getting to the point where i can help others myself. learning more and more every day! Have a good one!

#4

Glad it worked for you! Let us know anytime you have questions, and feel free to offer any answers you have!

Best,

jay

Sponsor our Newsletter | Privacy Policy | Terms of Service