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!
TABLE OF CONTENTS
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
EXAMPLE CODE DOWNLOAD
Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
SORRY FOR THE ADS...
But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.
Buy Me A Coffee Code Boxx eBooks
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
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
<?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) : void {
$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
<!-- (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:
- Just a simple HTML form to add a game score update. The current home and away score, also some comments.
- 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
<!-- (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
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.
- We need to send 2 parameters to the server –
- The last updated Unix timestamp
last
, which defaults to0
. The server will check against this timestamp, and only respond when there are updates. - The game id
gid
, which is fixed to999
for this demo.
- The last updated Unix timestamp
- 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
<?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.
EXTRAS
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
- Websockets on MDN
- AJAX Long Polling – Code Boxx
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!
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. 😉
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