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

On 27 Jan, 22:56, "laredotorn... [at] zipmail.com"
<laredotorn... [at] 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'

Post removed (X-No-Archive: yes)