Building a Live Scoreboard Web App with LAMP Stack and TiDB

🚀 Introduction

Hi, I’m Mugeha Jackline, and this is a walkthrough of a project I built as part of my journey to learn the LAMP stack (Linux, Apache, MySQL, PHP). My goal was to create a real-world web application to test my skills end-to-end, including frontend design, backend logic, database integration, and deployment.

The result? A live scoreboard app where predefined judges can score participants, with everything reflected on a public scoreboard. Let me walk you through how I built it!

---

📊 Project Overview

This web application allows:

• Admins to add judges and participants.

• Judges to submit scores for participants.

• Public users to view the scoreboard.

⚡ Tech Stack:

• Frontend: HTML + Bootstrap

• Backend: PHP

• Database: MySQL (hosted on TiDB Cloud)

• Hosting: Render (via Docker deployment)

---

📁 Project Structure

project-root/
│
├── .env                  # Environment variables
├── db.php                # Database connection handler
├── Dockerfile            # Docker image instructions
├── .render.yaml          # Render deployment configuration
├── index.php             # Landing page
├── add_participant.php   # Admin adds participants
├── admin_panel.php       # Admin dashboard for adding judges
├── judge_portal.php      # Judges login and submit scores
├── scoreboard.php        # Public view of live scores
└── assets/               # (Optional) Images, CSS

---

📄 Database Schema

I designed three tables:

CREATE TABLE judges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    display_name VARCHAR(100) NOT NULL
);

CREATE TABLE participants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    judge_id INT NOT NULL,
    participant_id INT NOT NULL,
    score INT NOT NULL,
    FOREIGN KEY (judge_id) REFERENCES judges(id),
    FOREIGN KEY (participant_id) REFERENCES participants(id)
);

---

🔧 Key Features

• Add Participants and Judges from the admin panel.

• Secure Judge Portal for score submissions.

• Live Scoreboard updates scores immediately.

• Environment Variables managed using .env for credentials.

• SSL Secure MySQL Access required by TiDB.

---

🚧 Challenges & Solutions

❌ Remote MySQL Hosting Hurdles

Many free MySQL providers block remote access or require credit card verification. I tried several like Railway, PlanetScale, 000webhost, and freemysqlhosting.net.

✅ Solution: TiDB Cloud

I finally chose TiDB Serverless — it supports remote access and SSL connections out-of-the-box. But I had to:

• Configure SSL with mysqli->ssl_set(...)

• Replace all hardcoded credentials with getenv() from .env

🛠️ Docker & Deployment

I containerized the app using a Dockerfile and deployed it on Render using .render.yaml. This allowed one-click continuous deployment from my GitHub repo.

---

📸 Screenshots / Demo

Loom video demo

---

📍 Deployment Notes

• .env file holds DB credentials like DB_HOST, DB_USER, DB_PASS, DB_PORT.

• db.php establishes secure connection using those variables.

• Render auto-builds from Docker and sets env vars in the dashboard.

---

😍 What I Learned

• How to build a complete CRUD app in pure PHP

• How to structure a MySQL schema with relations

• How to containerize with Docker

• How to connect securely to a remote DB using SSL

• How to deploy a LAMP app using Render + TiDB

---

🚀 What's Next?

• Add authentication for judges.

• Make the scoreboard auto-refresh every few seconds.

• Add sorting/filtering for better usability.

• Use AJAX or WebSockets for real-time updates.

---

📄 Links

• 💻 Source Code on GitHub

• ✨ Live Demo on Render

Thanks for reading! I'd love your feedback or ideas for improvements 😊

---

If you're learning the LAMP stack too, feel free to fork my repo and experiment!