Page 1 of 1

Return 0 for no matches, rather than nothing

Posted: Sun Aug 10, 2008 3:58 pm
by jayshields
I'm just wondering if this can be done in MySQL, because I feel it probably is possible, but I just don't know what to search for.

As an example, if I've got a query like this:

Code: Select all

    SELECT 
        DATE_FORMAT(`time_ordered`, '%d/%m/%y') AS `date`, 
        SUM(`total_price`) AS `total_takings`
    FROM 
        `orders` 
    WHERE 
        `completed` = 'Y' 
    AND 
        `time_ordered`
        BETWEEN 
            '2008-07-07' 
        AND
            '2008-08-01'
        GROUP BY 
            YEAR(`time_ordered`) AND DAYOFYEAR(`time_ordered`)
It might only return one row (which when print_r()'ed could look like this):

Code: Select all

Array ( [0] => Array ( [date] => 08/07/08 [total_takings] => 129.00 ) )
But what about if I want to return an array like this:

Code: Select all

Array ( 
[0] => Array ( [date] => 07/07/08 [total_takings] => 0.00 ),
[1] => Array ( [date] => 08/07/08 [total_takings] => 129.00 ),
[2] => Array ( [date] => 09/07/08 [total_takings] => 0.00 ),
[3] => Array ( [date] => 10/07/08 [total_takings] => 0.00 ),
[4] => Array ( [date] => 11/07/08 [total_takings] => 0.00 ),
... etc, etc through to the max BETWEEN clause...
)
Is that possible? I know I could fool around with the array in PHP and get it like that, but it just seems like extra hassle.

Even if that isn't possible, is it possible to run a query in MySQL to just fetch every date between 2 dates?

Thanks.

Re: Return 0 for no matches, rather than nothing

Posted: Sun Aug 10, 2008 11:55 pm
by califdon
I can't think of a way to do it with SQL without creating a Temp table first, which would be a whole lot messier than just getting the actual data and filling in the sparse matrix in PHP.

Here's a question I found on a forum that is essentially like yours, with an answer that I don't totally understand:
http://archives.postgresql.org/pgsql-ge ... g01636.php

Re: Return 0 for no matches, rather than nothing

Posted: Mon Aug 11, 2008 6:43 am
by jayshields
I think that approach is similar to what you suggested, with a temp table, but what he's proposed doesn't include a temp table, just a seperate table. I can see how that could work (not that I understand the query...), but I would need to incorporate a temp table which would end up being messier than just filling in an array with PHP.

I'll see what I can come up with using PHP and my current query.

Thanks anyway.