My own eyes are bloodshot a bit too, but there’s gotta be an easier way.... here are a couple thoughts.
I’m sure there’s a way to tweak PHP’s dates to make them match up how you want, but I suspect that’s the wrong way to go about this. Can you back up a bit? I can’t tell exactly what the end result is. Can you describe what you want to see without any code or queries?
Have you looked at MySQL’s DAYNAME() function? It’ll return "Monday" instead of a number.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
Hopefully your columns are using actual date types and not integer representations. If you’re stuck with integers masquerading as dates, I would seriously consider restructuring your table, but you can use MySQL’s STR_TO_DATE function... something like this:
DAYNAME(STR_TO_DATE( release_date, '%d%m%Y'))
It’s probably a pain to force MySQL to return null values for dates in this case, simply because you may not have data for those days. But once you get the data organized you should be able to iterate over an array of days.
Something like:
SELECT
DAYNAME(`entry_date`) as `day`
,COUNT(*) as `entry_count`
FROM
LB_forms LEFT JOIN LB_entries ON LB_forms.form_id = LB_entries.entry_form
WHERE
`entry_date` BETWEEN '20090921' AND '20090927'
GROUP BY
DAYNAME(`entry_date`)
Exactly how you fetch that data and how it’s packaged depends on the exact mysql driver and function you use, but imagine something like this:
$rs = $mydb->query($sql); // I'm assuming this returns an array of hashes
$days_of_week = array('Sunday','Monday', ... );
foreach ($days_of_week as $day) {
foreach ($rs as $row) {
if ($day == $row['day']) {
print "Day: $d had " . $row[$d] . " entries.";
}
}
}
Are you charting this? Have you looked at Google Chart? Did you post simplified code? Do you have your query in a dedicated function? I always shudder when I see SQL queries mixed with display code. Your query would be easier to read if you included table prefixes on all columns -- e.g. is entry_date LB_entries.entry_date or LB_forms.entry_date? entry_count?
☆ A M B ☆