What is wrong with this query?

[Guest]

I would like to have a report of the total weight of products per order and a weight total per month.

 

This I would like to add to the report Detail Monthly Sales which uses data from table orders_total.

 

Someone was kind enough to offer to help me but we are stock now...Here is the plan:

 

This query below got me the product ids for lets say order 391

 

SELECT `products_id`

FROM `orders_products`

WHERE `orders_id` = '391'

 

And this SQL query got me the sum of the item weights for order 391

 

SELECT SUM( products.products_weight )

FROM products

INNER JOIN orders_products ON orders_products.products_id = products.products_id

WHERE orders_products.orders_id = '391'

 

So this is what we added to the php file:

 

$weight_total_query = tep_db_query( "SELECT SUM( products.products_weight ) FROM products INNER JOIN orders_products ON orders_products.products_id = products.products_id WHERE orders_products.orders_id WHERE orders_id = " . $orders " );

$weight_total = tep_db_fetch_array( $weight_total_query );

 

Unfortunately it didn't work.

 

Any suggestions?

 

Thanks

 

Ricardo

[♥mdtaylorlrim]

Have you tried it in phpMyAdmin? Verified that there is a valid product_id in the orders_products and a valid weight in the products table?

 

Try this form....

 

SELECT SUM( products.products_weight )
FROM products, orders_products where orders_products.products_id = products.products_id
and orders_products.orders_id = '10005'       <------- put a valid number here

[MrPhil]

Did you really have this code...

WHERE orders_products.orders_id WHERE orders_id = " . $orders " );

Of course that will fail! You have two major errors in it: two WHEREs and a stray " at the end. Here's your corrected query:

$weight_total_query = tep_db_query( "SELECT SUM( products.products_weight ) AS total 
FROM products p INNER JOIN orders_products op ON op.products_id = p.products_id 
WHERE op.orders_id = '" . $orders . "';");

If your manual query in phpMyAdmin worked, then this should work (assuming $orders is a valid value, such as 391. $weight_total will be an array with one element 'total'. Be sure to check if there are 0 rows or more than 1 row.