Live Scoreboard With PHP MySQL (Free Source Code)

Welcome to a tutorial on how to create a Live Scoreboard with PHP and MySQL. So you want to go big, and display the scores of a game live on a website?

A simple live scoreboard consists of 4 essential components:

  • A database table to store the game scores.
  • Core PHP library to update and fetch the latest scores.
  • A simple admin page to update the game score.
  • Lastly, the scoreboard itself.

Let us walk through an example in this guide – Read on!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Create a database and import 1-database.sql.
  • Change the database settings in 2-lib.php to your own.
  • Access 4a-board.php in the browser.
  • Open 3-admin.php in another tab, add some dummy scores and watch the scoreboard update.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

LIVE SCORE WITH PHP MYSQL

All right, let us now get into the steps of creating a live score system with PHP and MYSQL.

 

STEP 1) GAME SCORE DATABASE TABLE

1-database.sql
CREATE TABLE `score` (
  `id` bigint(20) NOT NULL,
  `time` datetime NOT NULL DEFAULT current_timestamp(),
  `home` bigint(20) NOT NULL,
  `away` bigint(20) NOT NULL,
  `comment` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `score`
  ADD PRIMARY KEY (`id`,`time`);
 
Field Description
id The game ID, primary key.
time Time when the score is recorded. Defaults to the current time.
home Current home team score.
away Current away team score.
comment Score comments, if any.

This should be pretty easy to understand, just a table to hold the scores as the game progresses.

 

 

STEP 2) PHP SCORE LIBRARY

2-lib.php
<?php
class Score {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo;
  private $stmt;
  public $error;
  function __construct () {
    $this->pdo = new PDO(
      "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=" . DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  }

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    $this->pdo = null;
    $this->stmt = null;
  }
 
  // (C) EXECUTE SQL QUERY
  function query ($sql, $data=null) {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }
 
  // (D) ADD SCORE
  function add ($id, $home, $away, $comment=null) {
    $this->query(
      "INSERT INTO `score` (`id`, `home`, `away`, `comment`) VALUES (?,?,?,?)",
      [$id, $home, $away, $comment]
    );
    return true;
  }
  
  // (E) GET SCORES
  function get ($id) {
    $this->query("SELECT * FROM `score` WHERE `id`=? ORDER BY `time` DESC", [$id]);
    return $this->stmt->fetchAll();
  }
  
  // (F) CHECK LATEST SCORE UPDATE
  function check ($id) {
    $this->query(
      "SELECT UNIX_TIMESTAMP(`time`) FROM `score` WHERE `id`=? ORDER BY `time` DESC LIMIT 1",
      [$id]
    );
    $last = $this->stmt->fetchColumn();
    return $last==false ? 0 : $last ;
  }
}

// (G) DATABASE SETTINGS - CHANGE THESE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (H) CREATE NEW SCORE OBJECT
$_SCORE = new Score();

This is seemingly complicated at first, but keep calm and look carefully.

  • (A, B, H) On creating $_SCORE = new Score(), the constructor will automatically connect to the database. The destructor closes the connection.
  • (C) query() is a simple helper function to run an SQL query.
  • (D to F) There are only 3 functions here!
    • add() Adds a new score update to a game.
    • get() Gets all the scores for a specified game.
    • check() Is a “lightweight” function that returns the Unix timestamp of when the game is last updated. Will explain this later.
  • (G) Remember to change the database settings to your own.

 

 

STEP 3) SIMPLE ADMIN PAGE

3-admin.html
<!-- (A) ADD NEW SCORE -->
<form id="sForm" method="post" target="_self">
  <label>Home</label>
  <input type="number" name="home" required>
  <label>Away</label>
  <input type="number" name="away" required>
  <label>Comment</label>
  <input type="text" name="comment">
  <input type="submit" value="Save">
</form>
 
<!-- (B) CURRENT HISTORY -->
<div id="sWrap"><?php
  // (B1) GAME ID FIXED TO 999 (TO KEEP THINGS SIMPLE)
  require "2-lib.php";
  $gameID = 999;
 
  // (B2) ADD SCORE
  if (isset($_POST["home"])) {
    $_SCORE->add($gameID, $_POST["home"], $_POST["away"], $_POST["comment"]);
    echo "<div id='sNote'>SCORE ADDED</div>";
  }
 
  // (B3) GET + DISPLAY SCORES
  $score = $_SCORE->get($gameID);
  if (count($score)>0) { foreach ($score as $s) { ?>
  <div class="sRow">
    <div class="sGrow">
      <div class="sTime"><?=$s["time"]?></div>
      <div class="sComment"><?=$s["comment"]?></div>
    </div>
    <div class="sPoints">
      <span class="sHome">HOME <?=$s["home"]?></span> | 
      <span class="sAway">AWAY <?=$s["away"]?></span>
    </div>
  </div>
  <?php }} ?>
</div>

This “admin page” should be pretty straightforward as well:

  1. Just a simple HTML form to add a game score update. The current home and away score, also some comments.
  2. Uses the PHP library to update the score when the form is submitted, and also to fetch/show all the score entries.

 

 

STEP 4) SCOREBOARD HTML PAGE

4A) THE HTML

4a-board.php
<!-- (A) BIG SCORE BOARD -->
<div id="bTime"><?=date("Y-m-d H:i:s")?></div>
<div id="bBoard">
  <div id="bHome">0</div>
  <div id="bAway">0</div>
  <div id="bHomeT">Home</div>
  <div id="bAwayT">Away</div>
</div>
 
<!-- (B) SCORE HISTORY -->
<div id="bHistory"></div>

Very straightforward and simple page – Scoreboard at the top, score history at the bottom.

 

4B) SCOREBOARD JAVASCRIPT

4b-board.js
var board = {
  last : 0,
  poll : () => {
    // (A) DATA
    var data = new FormData();
    data.append("last", board.last); // last game updated timestamp
    data.append("gid", 999); // game id (fixed to 999 for demo)

    // (B) AJAX FETCH LOOP
    fetch("4c-ajax.php", { method:"post", body:data })
    .then(res => res.json())
    .then(data => {
      // (B1) UPDATE SCOREBOARD
      let history = document.getElementById("bHistory"), first = true;
      history.innerHTML = "";
      for (let score of data.score) {
        // (B1-1) BIG SCOREBOARD
        if (first) {
          document.getElementById("bTime").innerHTML = score["time"];
          document.getElementById("bHome").innerHTML = score["home"];
          document.getElementById("bAway").innerHTML = score["away"];
          first = false;
        }

        // (B1-2) GAME HISTORY
        let row = document.createElement("div");
        row.innerHTML = `[${score["time"]}] ${score["home"]}-${score["away"]} | ${score["comment"]}`;
        history.appendChild(row);
      }

      // (B2) NEXT ROUND
      board.last = data.last;
      board.poll();
    })
    .catch(err => board.poll());
  }
};
window.onload = board.poll;

Not going to do a boring line-by-line explanation here, but this is essentially a long-polling AJAX call to the server to fetch game score updates.

  1. We need to send 2 parameters to the server –
    • The last updated Unix timestamp last, which defaults to 0. The server will check against this timestamp, and only respond when there are updates.
    • The game id gid, which is fixed to 999 for this demo.
  2. Nothing really “special” with the rest, just that the AJAX request is non-stop and loops itself.

In a nutshell, this just keeps on connecting to the server to check for updates.

 

 

STEP 4C) GAME SCORE AJAX HANDLER

4c-ajax.php
<?php
// (A) INIT
if (!isset($_POST["last"]) || !isset($_POST["gid"])) { exit("INVALID REQUEST"); }
require "2-lib.php";
set_time_limit(30); // set the appropriate time limit
$sleep = 2; // short pause before next check
 
// (B) LOOP & GET UPDATES
while (true) {
  // (B1) GET LAST GAME UPDATE
  $last = $_SCORE->check($_POST["gid"]);
 
  // (B2) SERVE NEW DATA IF THERE ARE UPDATES
  if ($last > $_POST["last"]) {
    $score = $_SCORE->get($_POST["gid"]);
    echo json_encode([
      "last" => $last,
      "score" => $score
    ]);
    break;
  }
 
  // (B3) WAIT BEFORE CHECKING AGAIN
  sleep($sleep);
}

The last piece of the puzzle, the PHP AJAX handler. Remember that the Javascript will send over the “last updated Unix timestamp”?

  • $last = $_SCORE->check($_POST["gid"]) will fetch the last game timestamp from the database.
  • Check it against the client’s timestamp if ($last > $_POST["last"]).
  • Fetch and output the new scores only if the client has outdated game scores – $score = $_SCORE->get($_POST["gid"]).
  • Javascript then receives the update – Updates its Unix timestamp, HTML scoreboard, and the whole “send timestamp plus check updates” cycle loops again.

 

EXTRA BITS & LINKS

That’s it for all the code, and here are a few small extras that you may find to be useful.

 

LIMITATIONS – OPENING TO THE GENERAL PUBLIC?

If you intend to open the scoreboard to a large group of users, you might want to reconsider using a different technique. AJAX long polling works, but it is not the best idea – Thousands of people hammering refresh on the server will definitely crash it fast. You might want to look into Web Sockets and push technology instead.

 

SKELETON SYSTEM ONLY

Captain Obvious reminder – You wouldn’t want to upload the admin page as-it-is onto the Internet… At least put some password protection on it. Also, complete your own “add edit delete” score functions.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end of this tutorial. Sorry for “dumping” all the files without any folder structure – Everyone has their own existing project, and I really don’t want to add more confusion to the equation. That said, even though quite a bit of work needs to be done with this system, I hope it has given you a good kick-start.

If you have anything to share with this guide, please feel free to comment below. Good luck and happy coding!

2 thoughts on “Live Scoreboard With PHP MySQL (Free Source Code)”

  1. Mohamad del Rosario

    Hi Thanks,

    Thanks for the code, for the scoreboard I believe the better approach is to use a query string in the URL and use $_GET to obtain the $gameID. 😉

    1. Hi Mohamad,

      Do you have an example of how to implement this?
      Was wondering what the best way was to cycle through games and give the public the option of choosing the game they want to follow.

      Regards
      Garth

Leave a Comment

Your email address will not be published. Required fields are marked *