STA-001
A zero-filled daily series for charts
GROUP BY date skips days with no rows, which makes every chart lie. This fills the gaps before the data reaches the chart.
A grouped count returns only the days something happened. Plot that directly and a quiet Tuesday does not flatten the line, it disappears from the axis, so a week with three active days looks like a week with three consecutive busy ones.
The fix is to build the full range of dates first and overlay the query results onto it. Two lines of PHP, and every chart you draw afterwards is honest.
function daily_series(PDO $pdo, string $sql, array $params, int $days = 30): array
{
// Build the full window first: every day present, every day zero.
$out = [];
for ($i = $days - 1; $i >= 0; $i--) {
$out[date('Y-m-d', strtotime("-$i days"))] = 0;
}
// The query must return columns named d (a DATE) and n (a count).
$st = $pdo->prepare($sql);
$st->execute($params);
foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $row) {
$d = (string) $row['d'];
if (array_key_exists($d, $out)) {
$out[$d] = (int) $row['n'];
}
}
return $out;
}Using it
Call it with any grouped query that aliases its columns to d and n:
daily_series($pdo, 'SELECT DATE(created_at) d, COUNT(*) n FROM claims WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY) GROUP BY d', [30])
The returned array is already keyed by date in order, so it can go straight into a chart function or a CSV export.
What bites people
Watch the timezone. If MySQL and PHP disagree about what day it is, the first and last buckets will be wrong and nobody will notice for weeks.
For ranges beyond a few hundred days, group by week or month instead. A line chart with 730 points is a smear.