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

garlad · 26-01-2011 10:40PM #1

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 · 26-01-2011 10:58PM

what does your current query / db schema look like?

garlad · 26-01-2011 11:33PM

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 · 27-01-2011 12:20AM

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 · 27-01-2011 12:42AM

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 · 27-01-2011 10:03AM

no problem, let me know how you get on!

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

Comments