Logging Real Practice — Music That Remembers

One row per day — the upsert pattern

Practice tracking has a nice natural shape: you might log a few sessions in one day, but the grid shows a single total per day. So instead of inserting a new row every session, we keep one row per date and add to it. That's an upsert — "insert if new, update if it exists" — and MariaDB does it in one statement thanks to the unique key on on_date you created in chapter 4:

function log_practice(int $minutes): void {
    $stmt = db()->prepare(
        "INSERT INTO practice_log (on_date, minutes)
         VALUES (CURDATE(), ?)
         ON DUPLICATE KEY UPDATE minutes = minutes + VALUES(minutes)"
    );
    $stmt->execute([$minutes]);
}

Read that ON DUPLICATE KEY UPDATE clause out loud: "try to insert today's row; if today already exists (the unique key collides), instead add these minutes to what's there." One round trip, no race conditions, no "does today's row exist yet?" check in PHP. This is one of those SQL features that feels like magic the first time it saves you ten lines of code.

🐍 Python brain: upsert is the SQL version of dict.setdefault(key, 0); d[key] += n — "make sure today exists, then add to it" — except the database does it atomically.

Filling the grid from real data

The 14-day grid is currently a literal $mins = [42, 18, 0, 35, ...] array. We swap it for a query of the last 14 days, then map results onto the day cells:

$rows = db()->query(
    "SELECT on_date, minutes FROM practice_log
     WHERE on_date >= CURDATE() - INTERVAL 13 DAY"
)->fetchAll(PDO::FETCH_KEY_PAIR);   // [ '2026-05-29' => 42, ... ]

// then for each of the last 14 dates, look up minutes (0 if absent)

FETCH_KEY_PAIR is a tidy little PDO mode that turns a two-column result into an associative array keyed by the first column — perfect for "date → minutes" lookups. Days with no row simply aren't in the array, so they render as the empty "–" cell. The existing grid markup barely changes; only the data source does.

The playlist comes from the database now

Here's where chapter 3 pays a dividend. Back then, the player read each track's file path from a data-src attribute, and the playlist was a hardcoded PHP array. Now we move that array into a tracks table:

CREATE TABLE tracks (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    title   VARCHAR(200) NOT NULL,
    artist  VARCHAR(200) NOT NULL,
    file    VARCHAR(255) NOT NULL,
    sort    INT NOT NULL DEFAULT 0
);

The Music tab renders rows from SELECT * FROM tracks ORDER BY sort, id instead of the literal array. And here's the beautiful part: app.js doesn't change at all. It still just reads data-src / data-title / data-artist off each row — it neither knows nor cares whether those came from a hardcoded array or a database query. That's the data-attribute bridge from chapter 3 doing exactly what good design is supposed to do: let you swap the back end without touching the front end.

Tying music into the stats you already built

Remember log_activity() from chapter 6? When you log practice, log an activity row too, so the Home tab's MUSIC metric and streak count it:

log_practice($minutes);
log_activity('music', "$minutes min practice");

Now everything connects: practicing fills the music grid and nudges your Home stats and extends your streak. That web of small connections — one action updating several real views — is what makes a dashboard feel alive instead of like a pile of separate widgets.