Posted by Tom on 01/31/07 19:57

On 27 Jan 2007 14:56:52 -0800, laredotornado@zipmail.com wrote...
>
>Hi,
>
>I have a MySQL 5 table with the following data
>
>DAY SALES
>-----------------------------
>2006-01-14 24.00
>2006-01-18 36.00
>
>How can I write a query that returns other days in January, 2006 with
>"0" listed as total sales. So, the first 15 rows of this query would
>look like
>
>DAY SALES
>------------------------------
>2006-01-01 0
>2006-01-02 0
>2006-01-03 0
>2006-01-04 0
>2006-01-05 0
>2006-01-06 0
>2006-01-07 0
>2006-01-08 0
>2006-01-09 0
>2006-01-10 0
>2006-01-11 0
>2006-01-12 0
>2006-01-13 0
>2006-01-14 24.00
>2006-01-15 0
>
>Thanks for all your help, - Dave
>


Along the lines of what strawberry said, you'd probably be better off leaving
the data alone in your SQL and design a "foreach" or "while" type loop, to check
against a list of dates for the range you need.

Maybe build an array of the individual dates you need to check, and build
another with the results from your SQL query. You can then loop through your
dates array and if you find a match you have the value or default it to "0".

Tom
--
Help a Community by Participating in Ours
We donate your subscription fees to the charity you choose
100% of your first month, 10% thereafter.
http://newsguy.com/charity.asp

• Next in forum: Re: install and run php, apache, mysql
• Prev in forum: Re: Pear db to create tables
• Thread view: Re: How to write a query, filling in dates that aren't there.

[Reply to this message]