Killing the Fake Numbers — Real Stats

Inventory of lies

Let's name every fake number on the Home tab so we know what we're hunting. Open index.php, find the home section, and you'll see:

• The stat rows — FOCUS "2h 14m", STREAK "11 days", and friends — hardcoded strings.
• The weekly metric bars — PHP/LANG/MUSIC/WORK/SLEEP with fixed percentages.
• The activity log — a literal array of "17:42 · finished MariaDB module" style rows.
• The today's queue — which you already made real in chapter 5. (One down before we even started.)

The mindset shift this chapter teaches is the big one: don't store derived numbers, derive them on demand. We're not going to add a "streak" column that we have to remember to update. Instead we log raw events — "a chapter was finished," "a task was completed," "30 minutes of practice happened" — and compute streaks, totals, and percentages from those events whenever the page renders. Raw facts in, computed insights out. Store less, calculate more.

🐍 Python brain: if you've used pandas, this is df.groupby('day').sum() living in SQL instead. A GROUP BY with SUM() is a groupby-aggregate; COUNT(*) is len(); the streak calc is the kind of thing you'd do with a sorted date series. Letting the database do the aggregation is faster than pulling rows into PHP and looping.

One generic event log to rule them all

The activity table you created in chapter 4 is deliberately simple: a tag, a message, a timestamp. A one-line helper writes to it, and we sprinkle calls to that helper wherever something noteworthy happens:

function log_activity(string $tag, string $message): void {
    $stmt = db()->prepare(
        "INSERT INTO activity (tag, message) VALUES (?, ?)"
    );
    $stmt->execute([strtoupper($tag), $message]);
}

// call it from the places that already do things:
//   when a chapter is marked done:  log_activity('php', "Finished $sub");
//   when a task is completed:       log_activity('work', "Closed: $label");
//   when practice is logged:        log_activity('music', "$min min practice");

Now the activity feed on the Home tab is just SELECT * FROM activity ORDER BY happened_at DESC LIMIT 7 — real, in order, automatically current. The fake array is gone, replaced by a query over things that genuinely happened.

Computing the headline stats

Each Home number becomes a small query. A few examples:

// chapters finished this week (a real "progress" metric)
$thisWeek = (int) db()->query(
    "SELECT COUNT(*) FROM progress WHERE YEARWEEK(done_at, 1) = YEARWEEK(NOW(), 1)"
)->fetchColumn();

// total practice minutes in the last 7 days
$practice = (int) db()->query(
    "SELECT COALESCE(SUM(minutes),0) FROM practice_log
     WHERE on_date >= CURDATE() - INTERVAL 6 DAY"
)->fetchColumn();

The streak is the fun one because it's a tiny algorithm, not just an aggregate. Pull the distinct dates on which anything happened, then walk backward from today counting consecutive days until you hit a gap:

function current_streak(): int {
    $rows = db()->query(
        "SELECT DISTINCT DATE(happened_at) AS d FROM activity ORDER BY d DESC"
    )->fetchAll();
    $streak = 0;
    $expect = new DateTime('today');
    foreach ($rows as $r) {
        if ($r['d'] === $expect->format('Y-m-d')) {
            $streak++;
            $expect->modify('-1 day');
        } else {
            break;                 // gap -> streak ends
        }
    }
    return $streak;
}

That's a genuinely useful pattern: combine SQL (for the heavy filtering/sorting) with a little PHP (for logic SQL is awkward at). The database gets you the sorted distinct dates fast; PHP does the consecutive-day walk. Right tool for each half.

The weekly metric bars

Those PHP/LANG/MUSIC/WORK bars become a single grouped query — count this week's activity rows per tag, turn each count into a percentage of the busiest tag so the bars stay nicely scaled:

SELECT tag, COUNT(*) AS n
FROM activity
WHERE YEARWEEK(happened_at, 1) = YEARWEEK(NOW(), 1)
GROUP BY tag;

Then in PHP, divide each count by the max to get a 0–100% width. The bars now breathe with your actual week — a heavy PHP-studying week makes the PHP bar tall, all on its own.