Sorry about that. Let me try with actual dates instead of timestamps so that it seems to make more sense.
there were two donations to charity 29 on 2009/09/01, and 1 donation to charity 29 on 2009/09/05. Which leaves 2009/09/02, 2009/09/03, and 2009/09/04 with no donations. So in order for the graph to go to zero and stay there for those 3 days, i need to get those 3 missing days into my string, which currently is [200/909/01, 2],[2009/09/05,1] (with timestamps instead of dates, of course)
Heres what the end result should look like: [2009/09/01, 2], [2009/09/02, 0], [2009/09/03, 0], [2009/09/04, 0], [2009/09/05, 1] (again, timestamps instead of dates)
Right now, the query gives me 2 2009/09/01 and 1 2009/09/05, so i need to figure out the best way to get in between those days, put [timestamp, 0] in my string in between those dates, and so on, for 4+ years worth of data.
Heres some code i came up with that is half working, and 100% wtfy and screaming "there has to be a better way!!" at me.
$res = $db->query("SELECT COUNT(*) AS total, UNIX_TIMESTAMP(DATE_FORMAT(donation_date, '%Y%m%d')) AS donationdate FROM donations WHERE donation_charity_id = '$getid' GROUP BY DATE_FORMAT(donation_date, '%Y%m%d') ORDER BY donation_id DESC");
$donationSums = array();
while($donations = $db->assoc($res)) {
$donationSums[] = $donations;
} //while
$donationStr = '';
$max = 0;
$i=0;
foreach($donationSums as $k => $v) {
$thisDay = $v['donationdate'];
if(array_key_exists($i+1, $donationSums)) {
$nextDay = $donationSums[$i+1]['donationdate'];
}
$thisMax = $v['total'];
if(($thisDay - $nextDay)/86400 > 1) {
for($x=1;$x<($thisDay - $nextDay)/86400;$x++) {
$donationStr .= '[' . ($thisDay+86400)*1000 . ', 0], ';
} //for
} else {
$donationStr .= '[' . ($v['donationdate']*1000) . ', ' . $v['total'] . '], ';
}
if($thisMax > $max)
$max = $thisMax;
$i++;} //foreach
$max is only there to figure out the maximum donation amount given on any day, so the JS knows how tall to make the graph. nothing serious there. This seems to kinda sorta work, in so much that it gives me timestamps for the dates in between a donation in the query results, except that, for example, one charity which has had a total of 3 donations, after running this code on it, i got only one, so it was a bunch of [timestamp, 0]'s, then one [timestamp, 2] in the string.
It should have had 3 [timestamp, (some value greater than 0)]...
Kind of at a loss here, this just seems...ridiculously bad. Though suprisingly the page load time for one of these with a few thousand timestamps isnt all that bad...