Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
|
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 26th, 2004, 10:57 PM
Authorized User
 
Join Date: Jun 2003
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Script question...

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.
 
Old December 28th, 2004, 10:31 AM
Authorized User
 
Join Date: Dec 2004
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to peg110
Default

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
 
Old January 5th, 2005, 02:24 PM
Authorized User
 
Join Date: Jun 2003
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old January 6th, 2005, 11:35 AM
Authorized User
 
Join Date: Dec 2004
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to peg110
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
How Run .sql Script file in MS SQL Server 2000? aarkaycee SQL Server 2000 5 October 12th, 2009 05:43 AM
Question in login script zone8567 Classic ASP Basics 1 May 25th, 2008 06:57 PM
Question on appendix B - sql script Daniel.K62 BOOK: Beginning C# 2005 Databases 1 December 30th, 2006 06:21 PM
ActiveX Script Question kwilliams VB Databases Basics 0 January 23rd, 2006 06:48 PM
action script question IP076 Flash (all versions) 1 January 10th, 2005 07:55 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.