How to Create a Workout Tracker Web App with AI

Imagine being able to create a fully functional web app without any prior coding experience.

With the help of AI tools, I’ve managed to develop a bunch of “mini” web applications using minimal hand-coded lines. AI offers smarter, faster, and more efficient solutions than traditional coding, especially as a solo developer who likes to tinker with new projects. It allows you to generate hundreds of lines of functional code.

You can then review the AI code output line by line to understand how it works and ask follow-up questions, significantly condensing your learning time.

This capability will undoubtedly empower non-developers to become tech entrepreneurs in the future.

In this article, I’ll walk you through the process of transforming a workout tracking spreadsheet into a functional web application using PHP, MySQL, jQuery, and JavaScript. We’ll cover the essential components of the app, the challenges encountered during development, and the solutions implemented along the way.

The Project Background

I started tracking my workouts back in November 2016, creating a comprehensive spreadsheet system with multiple components.

The setup included:

• A master list of exercises with maximum rep counts by day and month
• A detailed daily workout log (now containing over 4,444 workout entries)
• A monthly performance tracker with interactive exercise selection
• Statistical summaries showing workout frequency by month

This robust dataset made the perfect candidate for conversion to a database-driven web application, where I could maintain the functionality while gaining the benefits of web accessibility.

Leveraging AI for Rapid Development

For this project, I utilized Claude, a relatively new AI tool with advantages over alternatives like ChatGPT, particularly in coding capabilities and the ability to process screenshots.

In my initial prompt to Claude, I provided three screenshots of my spreadsheet architecture and requested skeleton code using specific technologies:

I have a spreadsheet to log my workouts. I’d like to turn this into a web app. I will provide three screenshots: one is a list of all the exercises with the max number of reps for the set day and month; one is a sheet where I log the daily workouts with the date, including a dropdown to select a specific exercise; and the last is a monthly performance chart where I can select the exercise and a live update to show my monthly reps for that specific exercise and update the chart. Can you create skeleton code to turn this into a web app using PHP, jQuery, JavaScript, and MySQL?

I specifically chose these languages due to my background in WordPress development, where most of my customizations have involved this technology stack. By specifying my preferred languages, I ensured that the AI generated code compatible with my experience and existing skill set.

Database Structure

At the heart of any data-driven application is a well-designed database. For our workout tracker, we need two primary tables:

CREATE TABLE exercises (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(50) NOT NULL,
    max_set INT,
    max_day INT,
    max_month INT
);

CREATE TABLE workouts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    exercise_id INT,
    total_reps INT,
    set1 INT,
    set2 INT,
    set3 INT,
    FOREIGN KEY (exercise_id) REFERENCES exercises(id)
);

The exercises table stores information about each exercise, including its name, category, and maximum repetition records. The workouts table records individual workout sessions, linking to specific exercises and tracking the number of repetitions per set.

Adding User Authentication

A key enhancement to our workout tracker is implementing a secure user authentication system. This allows multiple users to track their workouts independently.

Creating the Users Table

First, we need to add a new table to our database schema:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);

Modifying Existing Tables

We need to update our existing tables to associate data with specific users:

ALTER TABLE exercises ADD COLUMN user_id INT;
ALTER TABLE workouts ADD COLUMN user_id INT;

ALTER TABLE exercises ADD FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE workouts ADD FOREIGN KEY (user_id) REFERENCES users(id);

Authentication Functions

Add these functions to your functions.php file:

function registerUser($username, $email, $password) {
    global $conn;
    $password_hash = password_hash($password, PASSWORD_DEFAULT);
    
    $sql = "INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("sss", $username, $email, $password_hash);
    
    if ($stmt->execute()) {
        return $conn->insert_id;
    } else {
        return false;
    }
}

function loginUser($username, $password) {
    global $conn;
    $sql = "SELECT id, password_hash FROM users WHERE username = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("s", $username);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($user = $result->fetch_assoc()) {
        if (password_verify($password, $user['password_hash'])) {
            $_SESSION['user_id'] = $user['id'];
            updateLastLogin($user['id']);
            return true;
        }
    }
    return false;
}

function updateLastLogin($user_id) {
    global $conn;
    $sql = "UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
}

function logoutUser() {
    session_unset();
    session_destroy();
}

function isLoggedIn() {
    return isset($_SESSION['user_id']);
}

function getCurrentUserId() {
    return $_SESSION['user_id'] ?? null;
}

Registration and Login Pages

Create register.php:

<?php
include 'db_connect.php';
include 'functions.php';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $username = $_POST['username'];
    $email = $_POST['email'];
    $password = $_POST['password'];
    
    if (registerUser($username, $email, $password)) {
        echo "Registration successful. Please log in.";
    } else {
        echo "Registration failed. Please try again.";
    }
}
?>

<form method="post">
    <input type="text" name="username" required placeholder="Username">
    <input type="email" name="email" required placeholder="Email">
    <input type="password" name="password" required placeholder="Password">
    <input type="submit" value="Register">
</form>

Create login.php:

<?php
session_start();
include 'db_connect.php';
include 'functions.php';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $username = $_POST['username'];
    $password = $_POST['password'];
    
    if (loginUser($username, $password)) {
        header("Location: index.php");
        exit();
    } else {
        echo "Invalid username or password.";
    }
}
?>

<form method="post">
    <input type="text" name="username" required placeholder="Username">
    <input type="password" name="password" required placeholder="Password">
    <input type="submit" value="Log In">
</form>

Create a simple logout.php:

<?php
session_start();
include 'functions.php';

logoutUser();
header("Location: login.php");
exit();
?>

Updating Existing Functions for User Context

Modify your existing database functions to include the user_id parameter:

function getExercises() {
    global $conn;
    $user_id = getCurrentUserId();
    $sql = "SELECT * FROM exercises WHERE user_id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
    return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}

function logWorkout($date, $exercise_id, $total_reps, $set1, $set2, $set3) {
    global $conn;
    $user_id = getCurrentUserId();
    $sql = "INSERT INTO workouts (date, exercise_id, total_reps, set1, set2, set3, user_id) VALUES (?, ?, ?, ?, ?, ?, ?)";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("siiiiii", $date, $exercise_id, $total_reps, $set1, $set2, $set3, $user_id);
    return $stmt->execute();
}

Authentication Middleware

Update your main pages to include authentication checks:

<?php
session_start();
include 'db_connect.php';
include 'functions.php';

if (!isLoggedIn()) {
    header("Location: login.php");
    exit();
}

// Rest of your page code
?>

This ensures only authenticated users can access your application.

Core Architecture

Our application now follows an enhanced web architecture:

• Authentication Layer: Manages user registration, login, and session management
• Backend (PHP): Handles data processing and database interactions within user context
• Frontend (HTML/CSS/JavaScript): Provides the user interface and client-side functionality
• Database (MySQL): Stores and retrieves workout data linked to specific users

By adding user authentication, we’ve transformed our single-user app into a multi-user platform where each person’s workout data remains private and separate.

Key Features Implementation

1. Navigation System

The app features a clean, tab-based navigation system that allows users to switch between different sections:

<nav>
    <ul>
        <li><a href="#" id="show-exercises">Exercises</a></li>
        <li><a href="#" id="show-log">Log Workout</a></li>
        <li><a href="#" id="show-chart">Performance Chart</a></li>
    </ul>
</nav>

This is powered by jQuery to dynamically load content without full page refreshes:

$('#show-exercises').click(function(e) {
    e.preventDefault();
    $.get('get_exercises.php', function(data) {
        $('#content').html(data);
    });
});

2. Exercise Management

One significant feature is the ability to view and add exercises. We implemented this with a table display and an add form:

// get_exercises.php
<table>
    <tr>
        <th>Exercise</th>
        <th>Category</th>
        <th>Max (Set)</th>
        <th>Max (Day)</th>
        <th>Max (Month)</th>
    </tr>
    <?php foreach ($exercises as $exercise): ?>
    <tr>
        <td><?php echo $exercise['name']; ?></td>
        <td><?php echo $exercise['category']; ?></td>
        <td><?php echo $exercise['max_set']; ?></td>
        <td><?php echo $exercise['max_day']; ?></td>
        <td><?php echo $exercise['max_month']; ?></td>
    </tr>
    <?php endforeach; ?>
</table>

<button id="add-exercise-btn">+ Add Exercise</button>

When a user clicks the “Add Exercise” button, a form appears that, upon submission, sends data to add_exercise.php for processing.

3. Workout Logging

The core functionality allows users to log their workouts. A key improvement we made was enabling multiple exercises to be logged for a single day:

$(document).on('click', '#add-exercise', function() {
    exerciseCount++;
    // Remove the "Remove Last Row" button from all existing exercise inputs
    $('.exercise-input .remove-exercise').remove();
    
    const newExercise = $('.exercise-input').first().clone();
    newExercise.find('input').val('');
    newExercise.find('select').val('');
    
    // Append the new exercise input
    $('#exercise-inputs').append(newExercise);
    
    // Add the "Remove Last Row" button only to the last exercise input
    $('#exercise-inputs .exercise-input:last')
        .append('<button type="button" class="remove-exercise">Remove Last Row</button>');
});

This dynamic form allows users to add as many exercises as needed for a single workout session.

Workout Streaks Tracking

One key motivational feature we’ve implemented is the ability to track workout streaks – both the current streak and the longest streak achieved:

function getWorkoutStreaks() {
    global $conn;
    $user_id = getCurrentUserId();
    
    $sql = "SELECT DISTINCT date 
            FROM workouts 
            WHERE user_id = ? 
            AND date < CURDATE()
            ORDER BY date DESC";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $dates = $result->fetch_all(MYSQLI_ASSOC);
    
    $current_streak = 0;
    $longest_streak = 0;
    $streak = 0;
    $yesterday = date('Y-m-d', strtotime('-1 day'));
    $prev_date = null;

    foreach ($dates as $row) {
        $date = $row['date'];
        
        if ($prev_date === null) {
            $streak = 1;
            if ($date == $yesterday) {
                $current_streak = 1;
            }
        } else {
            $diff = (strtotime($prev_date) - strtotime($date)) / 86400;
            
            if ($diff == 1) {
                $streak++;
            } else {
                $longest_streak = max($longest_streak, $streak);
                $streak = 1;
            }

            if ($current_streak > 0 && $diff == 1) {
                $current_streak++;
            }
        }

        $prev_date = $date;
    }

    // Final check
    $longest_streak = max($longest_streak, $streak);

    return [
        'current_streak' => $current_streak,
        'longest_streak' => $longest_streak
    ];
}

This function calculates two key metrics:

• Current streak: Consecutive days with workouts up to yesterday
• Longest streak: The longest sequence of consecutive workout days in history

The function works by:

• Fetching all workout dates for the user (ordered from newest to oldest)
• Iterating through these dates to identify consecutive days
• Tracking the current streak (must include yesterday)
• Finding the longest streak in the entire history

Monthly Progress Calculation

We also added a function to calculate monthly progress – what percentage of days in the current month have recorded workouts:

function getMonthlyProgress() {
    global $conn;
    $user_id = getCurrentUserId();
    
    $current_month = date('Y-m');
    $days_in_month = date('t');
    
    $sql = "SELECT COUNT(DISTINCT date) as workout_days 
            FROM workouts 
            WHERE user_id = ? 
            AND date LIKE ?";
    
    $stmt = $conn->prepare($sql);
    $like_param = $current_month . '%';
    $stmt->bind_param("is", $user_id, $like_param);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    
    $workout_days = $row['workout_days'];
    $monthly_progress = ($workout_days / $days_in_month) * 100;
    
    return number_format($monthly_progress, 1);
}

This function:

• Determines the current month and how many days it has
• Counts how many days in the current month have recorded workouts
• Calculates the percentage of days with workouts
• Returns a formatted percentage string

4. CSV Upload Integration

For users who prefer batch uploads or are migrating from spreadsheets, we added CSV upload functionality:

function getExerciseIdByName($name) {
    global $conn;
    $sql = "SELECT id FROM exercises WHERE name = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("s", $name);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        return $row['id'];
    }
    return null;
}

This function helps map exercise names from the CSV to their corresponding database IDs, enabling smooth data imports.

5. Performance Tracking and Visualization

To help users track their progress, we implemented a performance chart using Chart.js:

function updateChart(exerciseId) {
    $.ajax({
        url: 'get_performance_data.php',
        method: 'GET',
        data: { exercise_id: exerciseId },
        dataType: 'json',
        success: function(data) {
            const labels = data.map(item => item.month);
            const reps = data.map(item => parseInt(item.total_reps));

            chart.data.labels = labels;
            chart.data.datasets[0].data = reps;
            chart.update();
        },
        error: function(xhr, status, error) {
            console.error('Error fetching performance data:', error);
        }
    });
}

This function fetches performance data for a selected exercise and updates the chart, providing visual feedback on progress over time.

Recent Workouts Management

To help users track and manage their workout history, we’ve implemented a function to display and allow deletion of recent workouts:

function getRecentWorkouts($user_id, $days = 14) {
    global $conn;
    $sql = "SELECT w.id, w.date, e.name as exercise_name, w.total_reps, w.set1, w.set2, w.set3 
            FROM workouts w 
            JOIN exercises e ON w.exercise_id = e.id 
            WHERE w.user_id = ? 
            AND w.date >= DATE_SUB(CURDATE(), INTERVAL ? DAY) 
            ORDER BY w.date DESC, e.name ASC";
    
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ii", $user_id, $days);
    $stmt->execute();
    return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}

For the corresponding HTML and JavaScript, we created a dynamic table with delete functionality:

<table id="workouts-table">
    <thead>
        <tr>
            <th>Date</th>
            <th>Exercise</th>
            <th>Total Reps</th>
            <th>Set 1</th>
            <th>Set 2</th>
            <th>Set 3</th>
            <th>Delete</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach ($workouts as $workout): ?>
            <tr data-id="<?php echo $workout['id']; ?>">
                <td><?php echo htmlspecialchars($workout['date']); ?></td>
                <td><?php echo htmlspecialchars($workout['exercise_name']); ?></td>
                <td><?php echo htmlspecialchars($workout['total_reps']); ?></td>
                <td><?php echo htmlspecialchars($workout['set1']); ?></td>
                <td><?php echo htmlspecialchars($workout['set2']); ?></td>
                <td><?php echo htmlspecialchars($workout['set3']); ?></td>
                <td><span class="delete-btn">❌</span></td>
            </tr>
        <?php endforeach; ?>
    </tbody>
</table>

The deletion functionality is handled via AJAX to provide a smooth user experience:

$(document).ready(function() {
    $('.delete-btn').on('click', function() {
        var row = $(this).closest('tr');
        var workoutId = row.data('id');

        if (confirm('Are you sure you want to delete this workout?')) {
            $.ajax({
                url: 'recent_workouts.php',
                method: 'POST',
                data: { delete_id: workoutId },
                dataType: 'json',
                success: function(response) {
                    if (response.success) {
                        row.remove();
                    } else {
                        alert('Error deleting workout: ' + response.error);
                    }
                },
                error: function() {
                    alert('An error occurred while trying to delete the workout.');
                }
            });
        }
    });
});

On the server side, we handle the delete request:

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['delete_id'])) {
    $delete_id = $_POST['delete_id'];
    $delete_sql = "DELETE FROM workouts WHERE id = ? AND user_id = ?";
    $delete_stmt = $conn->prepare($delete_sql);
    $delete_stmt->bind_param("ii", $delete_id, $user_id);
    
    if ($delete_stmt->execute()) {
        echo json_encode(['success' => true]);
    } else {
        echo json_encode(['success' => false, 'error' => $conn->error]);
    }
    exit();
}

Workout Days by Month Chart

To help visualize workout consistency over time, we added a function to calculate workout days per month:

function getWorkoutDays(){
    global $conn;
    $user_id = getCurrentUserId();
    $sql = "SELECT DATE_FORMAT(date, '%Y-%m') AS month, COUNT(DISTINCT date) AS workout_days 
              FROM workouts 
              WHERE user_id = ?
              GROUP BY month 
              ORDER BY month";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
    $result = $stmt->get_result();
    return $result->fetch_all(MYSQLI_ASSOC);
}

This data powers a Chart.js bar chart showing workout frequency by month:

var workoutDaysCtx = document.getElementById('workoutDaysChart').getContext('2d');
var workoutDaysChart = new Chart(workoutDaysCtx, {
    type: 'bar',
    data: {
        labels: <?php echo json_encode($months); ?>,
        datasets: [{
            label: 'Workout Days per Month',
            data: <?php echo json_encode($workout_days); ?>,
            backgroundColor: 'rgba(75, 192, 192, 0.6)',
            borderColor: 'rgba(75, 192, 192, 1)',
            borderWidth: 1
        }]
    },
    options: {
        scales: {
            y: {
                beginAtZero: true,
                title: {
                    display: true,
                    text: 'Number of Workout Days'
                }
            },
            x: {
                title: {
                    display: true,
                    text: 'Month'
                }
            }
        },
        plugins: {
            title: {
                display: true,
                text: 'Workout Days per Month'
            }
        }
    }
});

This chart visualization provides users with a clear view of their workout consistency over time. The PHP code to prepare the data looks like this:

// Query to get workouts grouped by month
$workout_days_data = getWorkoutDays();
$months = [];
$workout_days = [];

foreach ($workout_days_data as $row) {
    $months[] = $row['month'];
    $workout_days[] = $row['workout_days'];
}

// Convert PHP arrays to JSON for use in JavaScript
$months_json = json_encode($months);
$workout_days_json = json_encode($workout_days);

Dashboard Metrics

To provide users with an at-a-glance view of their progress, we designed a metrics dashboard that displays key performance indicators.

HTML/CSS Structure

Here’s a clean, modern metric dashboard design:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Workout Metrics Dashboard</title>
    <link href="https://fonts.googleapis.com/css2?family=Roboto:wght@300;400;700&display=swap" rel="stylesheet">
    <style>
        body {
            font-family: 'Roboto', sans-serif;
            background-color: #f4f4f8;
            margin: 0;
            padding: 20px;
            color: #333;
        }
        .dashboard {
            display: flex;
            flex-wrap: wrap;
            gap: 20px;
            max-width: 1200px;
            margin: 0 auto;
        }
        .metric {
            background-color: #ffffff;
            border-radius: 10px;
            padding: 20px;
            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
            flex: 1 1 calc(33.333% - 20px);
            min-width: 200px;
            display: flex;
            flex-direction: column;
            align-items: center;
            transition: transform 0.3s ease;
        }
        .metric:hover {
            transform: translateY(-5px);
        }
        .metric-icon {
            font-size: 2.5em;
            margin-bottom: 10px;
        }
        .metric-value {
            font-size: 1.8em;
            font-weight: bold;
            margin-bottom: 5px;
        }
        .metric-label {
            font-size: 1em;
            color: #666;
            text-align: center;
        }
        @media (max-width: 768px) {
            .metric {
                flex: 1 1 calc(50% - 20px);
            }
        }
        @media (max-width: 480px) {
            .metric {
                flex: 1 1 100%;
            }
        }
    </style>
</head>
<body>
    <div class="dashboard">
        <div class="metric">
            <div class="metric-icon">🔥</div>
            <div class="metric-value"><?php echo $streaks['current_streak']; ?></div>
            <div class="metric-label">Current Streak</div>
        </div>
        <div class="metric">
            <div class="metric-icon">🏆</div>
            <div class="metric-value"><?php echo $streaks['longest_streak']; ?></div>
            <div class="metric-label">Longest Streak</div>
        </div>
        <div class="metric">
            <div class="metric-icon">📈</div>
            <div class="metric-value"><?php echo $monthly_progress; ?>%</div>
            <div class="metric-label">Monthly Progress</div>
        </div>
        <div class="metric">
            <div class="metric-icon">💪</div>
            <div class="metric-value"><?php echo $top_exercise; ?></div>
            <div class="metric-label">Last Month's Top Exercise</div>
        </div>
        <div class="metric">
            <div class="metric-icon">📊</div>
            <div class="metric-value"><?php echo $improvement_score; ?>%</div>
            <div class="metric-label">Improvement Score</div>
        </div>
    </div>
</body>
</html>

Enhanced Progress Visualization

For the monthly progress metric, we replaced the simple percentage text with an SVG pie chart:

<div class="metric">
    <svg width="100" height="100" viewBox="0 0 32 32" xmlns="http://www.w3.org/2000/svg">
        <circle cx="16" cy="16" r="16" fill="#e6e6e6"/>
        <circle cx="16" cy="16" r="16" fill="transparent"
                stroke="#007bff"
                stroke-width="32"
                stroke-dasharray="<?php echo $progress * 0.628; ?> 100"
                transform="rotate(-90 16 16)"/>
    </svg>
    <div class="metric-label">Monthly Progress</div>
</div>

For dynamic PHP integration, you’d replace the static value with a calculation:

<svg width="100" height="100" viewBox="0 0 32 32" xmlns="http://www.w3.org/2000/svg">
    <circle cx="16" cy="16" r="16" fill="#e6e6e6"/>
    <circle cx="16" cy="16" r="16" fill="transparent"
            stroke="#007bff"
            stroke-width="32"
            stroke-dasharray="<?php echo $progress * 0.628; ?> 100"
            transform="rotate(-90 16 16)"/>
</svg>

Where $progress is your percentage value (e.g., 13), and we multiply it by 0.628 (which is approximately 2π/100) to convert the percentage to the correct fraction of the circle’s circumference.

Handling Challenges

At this stage, I reached the limit on prompts for this conversation, as Claude limits usage to five or six prompts at a time. I often had to wait a few hours before continuing. To work around this, I alternated between Claude and other free AI tools like ChatGPT or Google Gemini to maximize response quality and efficiently address minor issues.

The pro version of Claude includes a project feature that enables collaboration on a set of documents or code.

Debugging Workout Logging Issues

When testing the workout logging functionality, we encountered an issue where the operation would fail silently. We implemented better error reporting to diagnose the problem:

function logWorkout($date, $exercise_id, $total_reps, $set1, $set2, $set3) {
    global $conn;
    $sql = "INSERT INTO workouts (date, exercise_id, total_reps, set1, set2, set3) VALUES (?, ?, ?, ?, ?, ?)";
    $stmt = $conn->prepare($sql);
    if ($stmt === false) {
        return "Prepare failed: " . $conn->error;
    }
    $stmt->bind_param("siiiiii", $date, $exercise_id, $total_reps, $set1, $set2, $set3);
    if ($stmt->execute() === false) {
        return "Execute failed: " . $stmt->error;
    }
    return true;
}

The issue was a mismatch in the parameter types specified in the bind_param method. By adding detailed error reporting, we could quickly identify and fix the problem.

Calculating Exercise Maximums

To keep track of personal records, we created a function that calculates the maximum repetitions for each exercise:

function updateExerciseMaxes() {
    global $conn;
    
    // Get all exercises
    $exercises_query = "SELECT id FROM exercises";
    $exercises_result = $conn->query($exercises_query);
    
    while ($exercise = $exercises_result->fetch_assoc()) {
        $exercise_id = $exercise['id'];
        
        // Max reps per set
        $max_set_query = "SELECT MAX(GREATEST(set1, set2, set3)) as max_set 
                          FROM workouts 
                          WHERE exercise_id = $exercise_id";
        $max_set_result = $conn->query($max_set_query);
        $max_set = $max_set_result->fetch_assoc()['max_set'];
        
        // Additional queries for max_day and max_month
        
        // Update the exercise record
        $update_query = "UPDATE exercises 
                         SET max_set = $max_set, max_day = $max_day, max_month = $max_month 
                         WHERE id = $exercise_id";
        $conn->query($update_query);
    }
    
    return true;
}

This function runs complex SQL queries to calculate the maximum values and update them in the database.

Troubleshooting Authentication Issues

During implementation of the user authentication system, we encountered some challenges with the user ID not being properly passed to other functions. Here’s how we resolved it:

Session Management

The most common issue was related to session management. Here’s a fixed version of getCurrentUserId() with improved error handling:

function getCurrentUserId() {
    if (!isset($_SESSION)) {
        error_log("Session not started when calling getCurrentUserId()");
        return null;
    }
    if (!isset($_SESSION['user_id'])) {
        error_log("user_id not set in session when calling getCurrentUserId()");
        return null;
    }
    return $_SESSION['user_id'];
}

To ensure sessions are properly started, we added session_start() at the beginning of all PHP files that use sessions:

<?php
session_start();
include 'db_connect.php';
include 'functions.php';

// Rest of the code
?>

Function Debugging

We also implemented debugging code in key functions to track down issues:

function logWorkout($date, $exercise_id, $total_reps, $set1, $set2, $set3) {
    global $conn;
    $user_id = getCurrentUserId();
    if ($user_id === null) {
        error_log("User ID is null in logWorkout function");
        // You might want to throw an exception here or handle this case
        return false;
    }
    // ... rest of the function
}

This extra error checking helped identify where the authentication chain was breaking down.

Streak Calculation Challenges

The streak calculation logic was particularly tricky to get right. We went through several iterations before landing on a reliable solution:

• First Attempt: Used a recursive common table expression (CTE) in MySQL, which was elegant but proved too resource-intensive and caused timeouts.
• Second Attempt: Tried a complex MySQL query with window functions, which also had performance issues.
• Final Solution: Simplified the approach by fetching the raw data and performing calculations in PHP, which proved much more efficient and maintainable.

The key insight was recognizing that some computations are better handled in application code rather than in database queries, especially when dealing with sequential data analysis like streaks.

Debugging the Streak Logic

We experienced issues where the current streak calculation wasn’t working correctly. The problem was in how we were comparing dates and checking for continuity:

// Problematic code
$diff = abs((strtotime($date) - strtotime($prev_date)) / 86400);

The solution was to ensure we were calculating the difference between consecutive dates correctly:

// Fixed code
$diff = (strtotime($prev_date) - strtotime($date)) / 86400;

This subtle change fixed the issue because we needed to account for the fact that the dates were being processed in descending order.

Another challenge was properly identifying the start of the current streak. We needed to specifically look for yesterday’s date:

$yesterday = date('Y-m-d', strtotime('-1 day'));
if ($date == $yesterday) {
    $current_streak = 1;
}

These detailed adjustments to the logic ensured that the streak calculations worked correctly in all scenarios.

Conclusion

Transforming a spreadsheet into a web application involves careful planning, thoughtful architecture, and attention to detail. By leveraging PHP, MySQL, jQuery, and JavaScript, we created a robust workout tracking system that offers more functionality and a better user experience than the original spreadsheet.

The final product allows users to track their exercises, log workouts with multiple exercises per day, upload data via CSV, and visualize their performance over time with interactive charts and engaging visual elements like pie charts. The modern, responsive design with intuitive navigation elements like the pill selector ensures a pleasant experience across devices.

By incorporating visual progress indicators such as the SVG pie chart, we’ve made the data more meaningful and engaging for users, helping them stay motivated and track their fitness journey more effectively.

This project demonstrates the power of web technologies in creating practical, everyday tools that enhance productivity and help users achieve their fitness goals. The combination of solid backend functionality with thoughtful frontend design creates an application that’s both powerful and a pleasure to use.

I’m also launching this workout tracker app since I’ve been tracking my workouts for nearly a decade, and I believe others may find this useful as well. If you’re interested in using this tool, go check it out here – Habit IQ.