How to write a query, filling in dates that aren"t there.
am 27.01.2007 23:56:52 von laredotornado
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
Re: How to write a query, filling in dates that aren"t there.
am 31.01.2007 12:52:06 von zac.carey
On 27 Jan, 22:56, "laredotorn...@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
Seeing as you've posted this in alt.php.sql, I think a better solution
than creating a new calendar table in your db would be to just output
the results using some kind of while loop in php. There must be
examples out there but I imagine the syntax could look something like
this:
while there are results
start a counter from the start to the end of the given month (this
could also be determined from the result set)
echo the date for the current counter position
if there's a result for the current counter position echo it, else
echo '0'