[Question] PHP pad out results of sql query which are 0

garlad

Hi,
Want to run a query to show number of transactions in a date range, grouped by Day for example (will also need to group by hour, week, month)

My result returns something like this for a week:
Mon, 7
Tue, 2
Thu, 1
Fri, 9

As you can see there were 0 records for Wednesday, but mysql returns no record for wed.

On my php page, i want to show results in a table with col headings mon, tue, wed, thu, fri.

If I loop through resultset I only have 4 records instead of 5.

Any wasy way to pad this out correctly before I start looking at arrays and matching days to resultset etc?

Cheers

Adam

what does your current query / db schema look like?

garlad

Table called Audit


`AUDIT_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`COMPUTER` varchar(45) NOT NULL,
`DATE` datetime NOT NULL,
`TRANSACTIONCOUNT` int(10) unsigned NOT NULL,


SELECT COUNT( computer ) AS total, HOUR(`date`) AS `hour`, SUM( transactioncount )
FROM audit
where date between '2011-01-21 00:00:00' and '2011-01-21 23:59:59'
GROUP BY HOUR(`date`);

Returns:
TOTAL | HOUR | SUM
4 | 15 | 8
9 | 16 | 12

but what i would like is for mysql to return a record for each hour even if total is 0

TOTAL | HOUR | SUM
0 | 00 | 0
0 | 01 | 0
..
..
..
4 | 15 | 8
9 | 16 | 12
0 | 17 | 0
0 | 18 | 0
0 | 19 | 0
0 | 20 | 0
0 | 21 | 0
0 | 22 | 0
0 | 23 | 0



or else sort it out in php

Cheers

Adam

set up a php loop that runs from 0 to 24,

for hour equal to 0 less than 24
    if mysql.hour equals php.hour echo resultset
else
    echo default values

brain is fried so there could be many flaws to that logic but i think it could work, i'm not great at visualising code at this hour!

garlad

Cheers Adam,
Was thinking that something like that in php might be the way to do it.
Dont think I'm going to be able to get it in a mysql resultset

Brain a bit fried here too. Will revisit in the morning.
Cheers again, appreciate the replies

Adam

no problem, let me know how you get on!