|
|
Posted by strawberry on 01/31/07 11:52
On 27 Jan, 22:56, "laredotorn...@zipmail.com"
<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'
Navigation:
[Reply to this message]
|