code-boxx/0-PHP-LIVE-SCORE.MD

## 0-PHP-LIVE-SCORE.MD

      
    Raw
  

              0-PHP-LIVE-SCORE.MD
            
          
    PHP LIVE SCOREBOARD

https://code-boxx.com/php-live-score/
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.

LICENSE

Copyright by Code Boxx
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

  
## 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`);

## 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) : 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();

## 3-admin.php
<!DOCTYPE html>
<html>
  <head>
    <title>GAME SCORE ADMIN DEMO</title>
    <meta charset="utf-8">
    <link rel="stylesheet" href="x-cosmetics.css">
  </head>
  <body>
    <!-- (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>
  </body>
</html>

## 4a-board.php
<!DOCTYPE html>
<html>
  <head>
    <title>LIVE SCORE BOARD DEMO</title>
    <meta charset="utf-8">
    <link rel="stylesheet" href="x-cosmetics.css">
    <script src="4b-board.js"></script>
  </head>
  <body id="bWrap">
    <!-- (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>
  </body>
</html>

## 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;

## 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);
}

## x-cosmetics.css
/* (A) WHOLE PAGE */
* {
  font-family: Arial, Helvetica, sans-serif;
  box-sizing: border-box;
}
body { padding: 20px; }

/* (B) ADMIN - SCORE FORM */
#sForm {
  background: #f7f7f7;
  border: 1px solid #dfdfdf;
  padding: 20px;
}
#sForm label, #sForm input {
  display: block;
  width: 100%;
  padding: 10px;
}
#sForm label { padding: 10px 0; }
#sForm input {
  padding: 10px;
  border: 1px solid #d7d7d7;
}
#sForm input[type=submit] {
  margin-top: 20px;
  border: 0;
  color: #fff;
  background: #647dd8;
  cursor: pointer;
}
#sNote {
  margin: 10px 0;
  padding: 10px;
  border: 1px solid #ffc7c7;
  background: #ffebeb;
}

/* (C) ADMIN - SCORE HISTORY */
#sWrap { margin-top: 20px; }
.sRow {
  display: flex;
  align-items: center;
  margin-top: 10px;
  padding: 10px;
  background: #f7f7f7;
  border: 1px solid #dfdfdf;
}
.sGrow { flex-grow: 1; }
.sTime { font-weight: 700; }
.sComment {
  padding-top: 5px;
  color: #686868;
}
.sPoints {
  font-weight: 700;
}
.sHome { color: red; }
.sAway { color: blue; }

/* (D) BIG SCOREBOARD */
#bWrap {
  background: #000;
  display: flex;
  flex-direction: column;
  align-items: center;
}
#bTime {
  padding: 10px 0;
  font-size: 20px;
  background: #323232;
}
#bTime, #bBoard {
  width: 300px;
  text-align: center;
  color: #fff;
}
#bBoard {
  display: grid;
  grid-template-columns: 1fr 1fr;
}
#bHome, #bAway {
  font-size: 60px;
  padding: 20px 0;
}
#bHome, #bHomeT, #bAway, #bAwayT {
  padding: 10px 0;
}
#bHome { background: #0b0558; }
#bHomeT { background: #221d64; }
#bAway { background: #580505; }
#bAwayT { background: #932828; }
#bHistory {
  margin-top: 20px;
  color: #fff;
  font-size: 14px;
}
	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`);
	<?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();
	<!DOCTYPE html>
	<html>
	<head>
	<title>GAME SCORE ADMIN DEMO</title>
	<meta charset="utf-8">
	<link rel="stylesheet" href="x-cosmetics.css">
	</head>
	<body>
	<!-- (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>
	</body>
	</html>
	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;
	<?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);
	}
	/* (A) WHOLE PAGE */
	* {
	font-family: Arial, Helvetica, sans-serif;
	box-sizing: border-box;
	}
	body { padding: 20px; }

	/* (B) ADMIN - SCORE FORM */
	#sForm {
	background: #f7f7f7;
	border: 1px solid #dfdfdf;
	padding: 20px;
	}
	#sForm label, #sForm input {
	display: block;
	width: 100%;
	padding: 10px;
	}
	#sForm label { padding: 10px 0; }
	#sForm input {
	padding: 10px;
	border: 1px solid #d7d7d7;
	}
	#sForm input[type=submit] {
	margin-top: 20px;
	border: 0;
	color: #fff;
	background: #647dd8;
	cursor: pointer;
	}
	#sNote {
	margin: 10px 0;
	padding: 10px;
	border: 1px solid #ffc7c7;
	background: #ffebeb;
	}

	/* (C) ADMIN - SCORE HISTORY */
	#sWrap { margin-top: 20px; }
	.sRow {
	display: flex;
	align-items: center;
	margin-top: 10px;
	padding: 10px;
	background: #f7f7f7;
	border: 1px solid #dfdfdf;
	}
	.sGrow { flex-grow: 1; }
	.sTime { font-weight: 700; }
	.sComment {
	padding-top: 5px;
	color: #686868;
	}
	.sPoints {
	font-weight: 700;
	}
	.sHome { color: red; }
	.sAway { color: blue; }

	/* (D) BIG SCOREBOARD */
	#bWrap {
	background: #000;
	display: flex;
	flex-direction: column;
	align-items: center;
	}
	#bTime {
	padding: 10px 0;
	font-size: 20px;
	background: #323232;
	}
	#bTime, #bBoard {
	width: 300px;
	text-align: center;
	color: #fff;
	}
	#bBoard {
	display: grid;
	grid-template-columns: 1fr 1fr;
	}
	#bHome, #bAway {
	font-size: 60px;
	padding: 20px 0;
	}
	#bHome, #bHomeT, #bAway, #bAwayT {
	padding: 10px 0;
	}
	#bHome { background: #0b0558; }
	#bHomeT { background: #221d64; }
	#bAway { background: #580505; }
	#bAwayT { background: #932828; }
	#bHistory {
	margin-top: 20px;
	color: #fff;
	font-size: 14px;
	}