Hello to all,

I've build a web based app that populates a mysql db...

My table has five fields...

product_num varchar(20) qty int(5) 0 date timestamp(14) YES PRI (NULL) complete tinyint(1) 0 comments varchar(200) YES No comments

The product_num is pre-generated dropdown box. When a user orders X number of product_num A it is submited to the orders table where it's date stamped and ready to be pulled... if i order another product_num A with X qty, all it does is date stamp it and it create a new row with the same product_num A... I want to consolidate the Product num A's qty and have only one row of the same product_num for that specific day(one day consolidation)


I hope that makes sense, I appreciate any assistance...

Thank you.

First of all... Could you edit your post so I (as well as other users) don't
have to scroll off the screen to read the post.

Next, Could you show us your SQL statement?

Finally, By the sounds of it you can just update the Quantity of Product num A
(or whatever product) but then you lose the meaning of the date stamp. It will
only show the last time a product was requested (and not necessarily pulled).

Sounds like you want to have a separate table One for the Overall quantities
and one for the orders.



Paul Gardner
------------------
PHP-LIVE help
Via Web @ http://www.mnetweb.co.uk/irc
Via IRC Client pgardner.net:6667
room #PHP

Paul, thanks for responding!

here is my sql statement...
SELECT product_num, qty , comments, complete FROM orders WHERE complete=0;

very simple, but the complete field is a int, that has a default of 0(indicates that it hasn't been fulfilled).

What I would like to do is have the complete filed associated with a check box so as soon as its checked and submitted it would change to value of 1 in the db. That way it won't show on my page and it indicates completion.

But What I really want to figure out is how do i aggregate two of the same product_num that has a separate qty. I want to aggregate it where it shows only one of the product_num with the total qty. Should i be using the sum() function in sql, if so how to i utilize it?


Thank you.

Yes, you could use the SUM() function.

SELECT SUM(qty) FROM orders WHERE complete=0 GROUP BY product_num

Paul Gardner
------------------
PHP-LIVE help
Via Web @ http://www.mnetweb.co.uk/irc
Via IRC Client pgardner.net:6667
room #PHP