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 Live Score Useful Bits
The End

 

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-core.php to your own.
  • Access 4a-score.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 `game_score` (
  `game_id` int(20) NOT NULL,
  `game_time` datetime NOT NULL DEFAULT current_timestamp(),
  `score_home` int(20) NOT NULL,
  `score_away` int(20) NOT NULL,
  `score_comment` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ALTER TABLE `game_score`
  ADD PRIMARY KEY (`game_id`,`game_time`);
 
Field Description
game_id The game ID, primary key.
game_time Time the score is recorded, defaults to the current time.
score_home Current home team score.
score_away Current away team score.
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-core.php
<?php
// (A) SCORE CLASS
class Score {
  // (A1) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo;
  private $stmt;
  public $error;
  function __construct () {
    try {
      $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
        ]
      );
    } catch (Exception $ex) { exit($ex->getMessage()); }
  }

  // (A2) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    $this->pdo = null;
    $this->stmt = null;
  }

  // (A3) ADD SCORE
  function add ($id, $home, $away, $comment=null, $time=null) {
    if ($time==null) { $time = date("Y-m-d H:i:s"); }
    try {
      $this->stmt = $this->pdo->prepare(
        "INSERT INTO `game_score` (`game_id`, `game_time`, `score_home`, `score_away`, `score_comment`) VALUES (?,?,?,?,?)"
      );
      $this->stmt->execute([$id, $time, $home, $away, $comment]);
      return true;
    } catch (Exception $ex) {
      $this->error = $ex->getMessage();
      return false;
    }
  }
  
  // (A4) GET SCORE
  function get ($id) {
    $this->stmt = $this->pdo->prepare(
      "SELECT * FROM `game_score` WHERE `game_id`=? ORDER BY `game_time` DESC"
    );
    $this->stmt->execute([$id]);
    return $this->stmt->fetchAll();
  }
  
  // (A5) CHECK LATEST SCORE UPDATE
  function check ($id) {
    $this->stmt = $this->pdo->prepare(
      "SELECT UNIX_TIMESTAMP(`game_time`) `unix` FROM `game_score` WHERE `game_id`=? ORDER BY `game_time` DESC LIMIT 1"
    );
    $this->stmt->execute([$id]);
    $last = $this->stmt->fetch();
    return is_array($last) ? $last["unix"] : 0 ;
  }
}

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

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

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

  • (A1 & A2) On creating a $_SCORE = new Score() object, the constructor will automatically connect to the database. The destructor will close it when done.
  • (A3 to A5) There are only 3 functions here!
    • function add() adds a new score update to a game.
    • function get() gets all the scores for a specified game.
    • Lastly, function check() is a “lightweight” function that returns the Unix timestamp of when the game is last updated. Will explain this later.
  • (B & C) Self-explanatory. A reminder here to change the database settings to your own again.

 

 

STEP 3) SIMPLE ADMIN PAGE

3-admin.html
<!-- (A) ADD NEW SCORE -->
<form id="scoreForm" method="post" target="_self">
  <label for="home">Home:</label>
  <input type="number" name="home" required/>
  <label for="away">Away:</label>
  <input type="number" name="away" required/>
  <label for="comment">Comment:</label>
  <input type="text" name="comment"/>
  <input type="submit" value="Save"/>
</form>
 
<!-- (B) CURRENT HISTORY -->
<div id="scoreWrap"><?php
// (B1) INIT
require "2-core.php";
// GAME ID IS FIXED TO 1 FOR THIS DEMO (TO KEEP THINGS SIMPLE)
$gameID = 1;
 
// (B2) ADD SCORE
if (isset($_POST["home"])) {
  if ($_SCORE->add($gameID, $_POST["home"], $_POST["away"], $_POST["comment"])) {
    echo "<div>SCORE ADDED</div>";
  } else {
    echo "<div>$_SCORE->error</div>";
  }
}
 
// (B3) GET + DISPLAY SCORES
$score = $_SCORE->get($gameID);
if (count($score)>0) { foreach ($score as $s) { ?>
<div class="scoreRow">
  <div class="scoreTime"><?=$s["game_time"]?></div>
  <div class="scorePoints">
    <span class="scoreHome">HOME: <?=$s["score_home"]?></span> | 
    <span class="scoreAway">AWAY: <?=$s["score_away"]?></span>
  </div>
  <div class="scoreComment">COMMENT: <?=$s["score_comment"]?></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 4A) SCOREBOARD HTML PAGE

4a-score.php
<!-- (A) BIG SCORE BOARD -->
<div id="scoreBoard">
  <div id="scoreTime"><?=date("Y-m-d H:i:s")?></div>
  <div id="scoreHome">0</div>
  <div id="scoreAway">0</div>
  <div id="nameHome">HOME</div>
  <div id="nameAway">AWAY</div>
</div>
 
<!-- (B) SCORE HISTORY -->
<div id="scoreHistory"></div>

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

 

STEP 4B) SCOREBOARD JAVASCRIPT

4b-score.js
var score = {
  last : 0,
  poll : function () {
    // (A) DATA
    var data = new FormData();
    data.append("last", score.last); // Last game updated timestamp
    data.append("gid", 1); // Game ID (fixed to 1 for demo)

    // (B) INIT AJAX
    var xhr = new XMLHttpRequest();
    xhr.open("POST", "4c-ajax-score.php");
    xhr.timeout = 30000; // 1000 ms = 1 sec

    // (C) KEEP FIRING REQUESTS TO GET UPDATES
    xhr.ontimeout = score.poll;

    // (D) UPDATE NEW SCORE DATA
    xhr.onload = function () {
      // (D1) PARSE RESULTS
      let res = JSON.parse(this.response),
          history = document.getElementById("scoreHistory");
          first = true;
      score.last = res.last;
      history.innerHTML = "";

      for (let s of res.score) {
        // (D2) UPDATE HTML SCORE BOARD
        if (first) {
          document.getElementById("scoreTime").innerHTML = s["game_time"];
          document.getElementById("scoreHome").innerHTML = s["score_home"];
          document.getElementById("scoreAway").innerHTML = s["score_away"];
          first = false;
        }
        
        // (D3) UPDATE HISTORY
        let hrow = document.createElement("div");
        hrow.innerHTML = `[${s["game_time"]}] ${s["score_home"]}-${s["score_away"]} | ${s["score_comment"]}`;
        history.appendChild(hrow);
      }
      score.poll();
 
    // (E) GO!
    xhr.send(data);
  }
};
window.addEventListener("DOMContentLoaded", score.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.

  • 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 game updates.
    • The game id gid, which is fixed to 1 for this demo.
  • Nothing really “special” with the rest, just that the AJAX request is non-stop and loops itself.
    • xhr.ontimeout = score.poll will fire another AJAX request when there are no updates.
    • xhr.onload = ... score.poll() will fire another AJAX request after receiving updates.

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

 

 

STEP 4C) GAME SCORE AJAX HANDLER

4c-ajax-score
<?php
// (X) ERROR
if (!isset($_POST["last"]) || !isset($_POST["gid"])) { die("INVALID REQUEST"); }
 
// (A) INIT
require "2-core.php";
set_time_limit(30); // Set the appropriate time limit
ignore_user_abort(false); // Stop when connection breaks
$sleep = 2; // Not to break the server. 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.

 

USEFUL 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, but 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 *