Welcome to a tutorial on how to create a digital signage using PHP and MySQL. Thinking of creating a digital signage system using web technologies? Well, it is possible, and it is somewhat just like building a content management system. Read on for an example!
ⓘ 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
- Download and unzip into your HTTP folder.
- Create a test database and import
1-database.sql
. - Change the database settings in
2-lib-pg.php
to your own. - Access
3a-admin.html
to update the pages,4a-signage.html
to show them.
EXAMPLE CODE DOWNLOAD
Click here to download all the example 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.
PHP MYSQL SIGNAGE
All right, let us now get into the details of building a simple digital signage system using PHP and MySQL.
PART 1) THE DATABASE
CREATE TABLE `pages` (
`pg_id` bigint(20) NOT NULL,
`pg_title` varchar(255) NOT NULL,
`pg_txt` text NOT NULL,
`pg_sort` bigint(20) NOT NULL DEFAULT 0,
`pg_date` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `pages` (`pg_id`, `pg_title`, `pg_txt`, `pg_sort`, `pg_date`) VALUES
(1, 'First Page', '<div class=\"center\">\r\n<div><img src=\"images/burger.png\" alt=\"\"></div>\r\n<div style=\"font-size: 48px;\"><span style=\"color: rgb(224, 62, 45);\"><strong>HENBORGER $ 999.99</strong></span></div>\r\n</div>', 0, '2022-05-28 13:32:10'),
(2, 'Second Page', '<div class=\"center\">\r\n<div style=\"font-size: 48px;\"><span style=\"color: rgb(224, 62, 45);\"><strong>POTAT $ 888.88</strong></span></div>\r\n<div><img src=\"images/potato.png\" alt=\"\" width=\"300\" height=\"190\"></div>\r\n</div>', 1, '2022-05-28 13:49:08');
ALTER TABLE `pages`
ADD PRIMARY KEY (`pg_id`),
ADD KEY `pg_sort` (`pg_sort`);
ALTER TABLE `pages`
MODIFY `pg_id` bigint(20) NOT NULL AUTO_INCREMENT;
First, let us start with creating a database to store all the pages. This is a simple one with 2 dummy pages to get you started.
pg_id
Primary key, auto-increment.pg_title
Title of the page.pg_txt
Actual contents (HTML) of the page.pg_sort
The sort order.pg_date
Last modified date and time.
PART 2) PHP LIBRARY
<?php
class Pages {
// (A) CONSTRUCTOR - CONNECT TO DATABASE
private $pdo = null;
private $stmt = null;
public $error = null;
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()); }
}
// (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
function __destruct () {
if ($this->stmt !== null) { $this->stmt = null; }
if ($this->pdo !== null) { $this->pdo = null; }
}
// (C) HELPER FUNCTION - RUN SQL QUERY
function query ($sql, $data=null) {
$this->stmt = $this->pdo->prepare($sql);
$this->stmt->execute($data);
}
// (D) GET ALL PAGES (MINUS TEXT)
function getAll () {
$this->query(
"SELECT `pg_id`, `pg_title`, `pg_date`, UNIX_TIMESTAMP(`pg_date`) `unix`
FROM `pages`
ORDER BY `pg_sort` ASC"
);
return $this->stmt->fetchAll();
}
// (E) GET PAGE
function get ($id) {
$this->query("SELECT * FROM `pages` WHERE `pg_id`=?", [$id]);
return $this->stmt->fetch();
}
// (F) SAVE PAGE
function save ($title, $txt, $id=null) {
// (F1) NEW PAGE
if ($id==null) {
$this->query("UPDATE `pages` SET `pg_sort`=`pg_sort`+1");
$this->query(
"INSERT INTO `pages` (`pg_title`, `pg_txt`) VALUES (?,?)",
[$title, $txt]
);
}
// (F2) UPDATE DATE
else {
$this->query(
"UPDATE `pages` SET `pg_title`=?, `pg_txt`=? WHERE `pg_id`=?",
[$title, $txt, $id]
);
}
// (F3) DONE
return true;
}
// (G) DELETE PAGE
function del ($id) {
$this->query("DELETE FROM `pages` WHERE `pg_id`=?", [$id]);
return true;
}
// (H) SAVE SORT ORDER
function order ($order) {
foreach (json_decode($order) as $sort=>$id) {
$this->query(
"UPDATE `pages` SET `pg_sort`=? WHERE `pg_id`=?",
[$sort, $id]
);
}
return true;
}
}
// (I) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");
// (J) NEW PAGES OBJECT
$_PG = new Pages();
With the database in place, the next step is to create a library to work with it. This looks confusing, but keep calm and study closely… It’s quite literally a collection of “SQL functions”.
- (A, B, J) When
$_PG = new Pages()
is created, the constructor automatically connects to the database. The destructor closes the connection. - (C)
query()
A simple helper function to run SQL queries. - (D To H) The “actual library functions”.
getAll()
Get all pages.get()
Get the specified page.save()
Add or update a page.del()
Delete a specified page.order()
Save the new page order.
- (I) Self-explanatory. Change the settings to your own.
PART 3) ADMIN PAGES
3A) THE HTML
<!-- (A) LIST OF PAGES -->
<div id="pgA">
<input type="button" value="Add Page" onclick="adm.show()"/>
<div id="list"></div>
</div>
<!-- (B) ADD/EDIT PAGE -->
<div id="pgB" class="ninja"></div>
With the foundations in place, we can now create a simple “admin page”. There are only 2 sections in this one.
<div id="pgA">
List of existing pages.<div id="pgB">
To add/edit a selected page.
The rest will be driven by Javascript and AJAX.
3B) THE JAVASCRIPT
var adm = {
// (A) INIT
pgA : null, // html page a
pgB : null, // html page b
pgL : null, // html pages list
pgS : [], // html list of pages
pSel : null, // current page being dragged
init : () => {
// (A1) GET HTML ELEMENTS
adm.pgA = document.getElementById("pgA");
adm.pgB = document.getElementById("pgB");
adm.pgL = document.getElementById("list");
// (A2) INIT LOAD PAGES LIST
adm.list();
},
// (B) SUPPORT FUNCTION - AJAX FETCH
fetch : (data, load) => {
// (B1) FORM DATA
let form;
if (data instanceof FormData) { form = data; }
else {
form = new FormData();
for (let [k,v] of Object.entries(data)) { form.append(k,v); }
}
// (B2) FETCH
fetch("3c-admin-ajax.php", { method: "POST", body: form })
.then(res => res.text())
.then(txt => { load(txt); });
},
// (C) SUPPORT FUNCTION - TOGGLE PAGE
toggle : (pg) => {
adm.pgA.classList.add("ninja");
adm.pgB.classList.add("ninja");
document.getElementById("pg"+pg).classList.remove("ninja");
},
// (D) LIST ALL PAGES
list : () => {
// (D1) AJAX FETCH
adm.fetch({ "req" : "list" },
(txt) => {
// (D2) PUT INTO HTML CONTAINER
adm.pgL.innerHTML = txt;
adm.toggle("A");
// (D3) SORTABLE
adm.pgS = document.querySelectorAll("#list .row");
if (adm.pgS.length>0) { for (let div of adm.pgS) {
// (D3-1) ON DRAG START - ADD DROPPABLE HINTS
div.ondragstart = (e) => {
adm.pSel = e.target;
for (let p of adm.pgS) {
p.classList.add("drag");
if (p != adm.pSel) { p.classList.add("hint"); }
}
};
// (D3-2) ON DRAG ENTER - HIGHLIGHT DROPZONE
div.ondragenter = (e) => {
if (div != adm.pSel) { div.classList.add("active"); }
};
// (D3-3) DRAG LEAVE - REMOVE HIGHLIGHT DROPZONE
div.ondragleave = (e) => {
div.classList.remove("active");
};
// (D3-4) DRAG END - REMOVE ALL HIGHLIGHTS
div.ondragend = (e) => { for (let p of adm.pgS) {
p.classList.remove("drag");
p.classList.remove("hint");
p.classList.remove("active");
}};
// (D3-5) DRAG OVER - PREVENT DEFAULT "DROP", SO WE CAN DO OUR OWN
div.ondragover = (e) => { e.preventDefault(); };
// (D3-6) ON DROP - REORDER NOTES & SAVE
div.ondrop = (e) => {
// (D3-6-1) PREVENT DEFAULT BROWSER DROP ACTION
e.preventDefault();
if (e.target != adm.pSel) {
// (D3-6-2) GET CURRENT & DROPPED POSITIONS
let idrag = 0, // index of currently dragged
idrop = 0; // index of dropped location
for (let i=0; i<adm.pgS.length; i++) { if (adm.pSel == adm.pgS[i]) { idrag = i; } if (e.target == adm.pgS[i]) { idrop = i; } } // (D3-6-3) REORDER HTML NOTES if (idrag > idrop) {
document.getElementById("list")
adm.pgL.insertBefore(adm.pSel, e.target);
} else {
adm.pgL.insertBefore(adm.pSel, e.target.nextSibling);
}
// (D3-6-4) GET NEW ORDER
adm.pgS = adm.pgL.querySelectorAll(".row");
let order = [];
for (let n of adm.pgS) { order.push(n.dataset.id); }
// (D3-6-5) AJAX SAVE ORDER
adm.fetch({
"req" : "order",
"order" : JSON.stringify(order)
}, (txt) => {
if (txt == "OK") { adm.show(); }
else { alert(txt); }
});
}
};
}}
});
},
// (E) SHOW PAGE - ADD OR EDIT
show : (id) => {
// (E1) AJAX FETCH FORM
adm.fetch({
"req" : "show",
"id" : (id==undefined ? "" : id)
}, (txt) => {
// (E2) CONTENTS INTO <DIV ID="PGB">
document.getElementById("pgB").innerHTML = txt;
adm.toggle("B");
// (E3) TINYMCE
tinymce.remove();
tinymce.init({
selector : "#pg_txt",
menubar : false,
// https://www.tiny.cloud/docs/advanced/available-menu-items/
plugins : "image code",
toolbar: "bold italic underline | backcolor forecolor | alignleft aligncenter alignright alignjustify | image | code"
});
});
},
// (F) SAVE PAGE
save : () => {
// (F1) GET FORM
var data = {
"req" : "save",
"title" : document.getElementById("pg_title").value,
"txt" : tinymce.get("pg_txt").getContent(),
"id" : document.getElementById("pg_id").value
};
// (F2) CHECKS
if (data.title=="") {
alert("Please enter the title");
return false;
}
if (data.txt=="") {
alert("Please fill in the page content.");
return false;
}
// (F3) AJAX SAVE
adm.fetch(data, (txt) => {
if (txt=="OK") {
adm.list();
alert("Page saved");
} else { alert(txt); }
});
return false;
},
// (G) DELETE PAGE
del : (id) => { if (confirm("Delete page?")) {
adm.fetch({
"req" : "del",
"id" : id
}, (txt) => {
if (txt=="OK") {
adm.list();
alert("Page deleted");
} else { alert(txt); }
});
}}
};
window.onload = adm.init;
Yikes, that’s a whole load of Javascript. I will just summarize it, these basically deal with the HTML interface.
- (A)
init()
Runs on page load. Gets all the related HTML elements, and loads the list of pages. - (B & C) Support functions.
fetch()
Does an AJAX fetch to3c-admin-ajax.php
.toggle()
Remember the two HTML sections from earlier? This toggles between showing the list<div id="pgA">
and the add/edit page<div id="pgB">
.
- (D To G) “Interface functions”.
list()
Loads the list of pages via AJAX.show()
Add or edit a selected page.save()
Save (add or update) a page.del()
Delete a page.
3C) AJAX HANDLER
<?php
// (A) LOAD LIBRARY
require "2-lib-pg.php";
if (isset($_POST["req"])) { switch ($_POST["req"]) {
// (B) INVALID REQUEST
default: echo "Invalid request"; break;
// (C) LIST ALL PAGES
case "list":
$pages = $_PG->getAll();
foreach ($pages as $p) { printf(
"<div class='row' draggable='true' data-id='%u'>
<div class='left'>%s %s</div>
<div class='right'>
<input type='button' value='Delete' onclick='adm.del(%u)'/>
<input type='button' value='Edit' onclick='adm.show(%u)'/>
</div>
</div>",
$p["pg_id"], $p["pg_title"], $p["pg_date"],
$p["pg_id"], $p["pg_id"]
);
}
break;
// (D) SHOW PAGE - ADD/EDIT
case "show":
// (D1) GET PAGE
if (is_numeric($_POST["id"])) {
$pg = $_PG->get($_POST["id"]);
}
// (D2) PAGE FORM ?>
<form class="form" onsubmit="return adm.save()">
<label>Page Title</label>
<input type="text" id="pg_title" name="pg_title" value="<?=isset($pg)?$pg["pg_title"]:""?>"/>
<label>Page Content</label>
<textarea id="pg_txt"><?=isset($pg)?$pg["pg_txt"]:""?></textarea>
<label>Last Modified</label>
<input type="text" id="pg_date" disabled value="<?=isset($pg)?$pg["pg_date"]:"NA - NEW PAGE"?>"/>
<input type="hidden" id="pg_id" name="pg_id" value="<?=isset($pg)?$pg["pg_id"]:""?>" required/>
<input type="button" value="Back" onclick="adm.toggle('A')"/>
<input type="button" value="Save" onclick="adm.save()"/>
</form>
<?php break;
// (E) SAVE PAGE
case "save":
$_PG->save($_POST["title"], $_POST["txt"], $_POST["id"]);
echo "OK";
break;
// (F) DELETE PAGE
case "del":
$_PG->del($_POST["id"]);
echo "OK";
break;
// (G) SORT PAGES
case "order":
$_PG->order($_POST["order"]);
echo "OK";
break;
}}
All AJAX calls from the Javascript will be handled by this one.
- (C)
list
Get all pages from the database and put them into a nice HTML form. - (D)
show
Add/edit a page. - (E)
save
Add a new page, or update one. - (F)
del
Delete a page. - (G)
order
Save the sort order of pages.
Yep, we are pretty much just “mapping” the Javascript AJAX requests to the library functions.
PART 4) SIGNAGE PAGES
4A) THE HTML
<div id="pages"></div>
The last piece of the system – Load all the pages from the database, and show them as “slides”.
4B) THE JAVASCRIPT
var sign = {
// (A) SUPPORT FUNCTION - AJAX FETCH
fetch : (data, load) => {
// (A1) QUERY STRING
let query = new URLSearchParams();
for (let [k,v] of Object.entries(data)) { query.append(k,v); }
// (A2) FETCH
fetch("4c-signage-ajax.php?" + query.toString())
.then(res => res.text())
.then(txt => { load(txt); });
},
// (B) INIT
hwrap : null, // html wrapper
hpages : [], // html pages
pages : [], // pages data
loaded : 0, // number of pages loaded
delay : 5000, // delay between each page (ms)
init : () => {
// (B1) GET HTML WRAPPER
sign.href = document.getElementById("pages");
// (B2) LOAD PAGES
sign.fetch({ req : "check" }, (res) => {
// (B2-1) PUT PAGES "DATA" INTO ARRAY
for (let p of JSON.parse(res)) {
sign.pages.push({
i : p["pg_id"],
u : p["unix"]
});
}
// (B2-2) LOAD INDIVIDUAL PAGES
if (sign.pages.length > 0) { for (let p of sign.pages) {
sign.fetch({
req : "load",
id : p["i"],
u : p["u"]
}, (res) => {
let div = document.createElement("div");
div.className = "page";
div.innerHTML = res;
sign.href.appendChild(div);
sign.hpages.push(div);
sign.loaded++;
if (sign.loaded >= sign.pages.length) {
sign.run();
sign.cache();
setInterval(sign.run, sign.delay);
}
});
}}
});
},
// (C)) ROTATE PAGES
current : -1, // current page
run : () => {
// (C1) HIDE ALL
for (let p of sign.hpages) { p.classList.remove("show"); }
// (C2) SHOW NEXT PAGE
sign.current++;
if (sign.current >= sign.pages.length) { sign.current = 0; }
sign.hpages[sign.current].classList.add("show");
}
};
window.onload = sign.init;
“Scary code” once again, but a quick walkthrough:
- (B2)
sign.init()
will run on page load, and the first thing it does is to do acheck
AJAX call to the server. - (B2-1) The server will return the list of pages to show, and we store them into
sign.pages
. - (B2-2) We loop through
sign.pages
, load them one by one, and append the pages into<div id="page">
. - (C) When all the pages are loaded, we set an interval timer to rotate between them. The end.
4C) AJAX HANDLER
<?php
// (A) LOAD LIBRARY
require "2-lib-pg.php";
if (isset($_GET["req"])) { switch ($_GET["req"]) {
// (B) INVALID REQUEST
default: echo "Invalid request"; break;
// (C) PAGES TO LOAD
case "check":
echo json_encode($_PG->getAll());
break;
// (D) LOAD PAGE
case "load":
$pg = $_PG->get($_GET["id"]);
echo $pg["pg_txt"];
break;
}}
check
Returns the list of pages to load.load
Loads the given page.
Yep, some of you sharp code ninjas should have noticed. Why so dumb? Why don’t we just get all page contents at once and output into 4a-signage.html
? That will be revealed in the next step.
PART 5) PROGRESSIVE WEB APP & OFFLINE SUPPORT
5A) SERVICE WORKER
// (A) CREATE/INSTALL CACHE
self.addEventListener("install", (evt) => {
self.skipWaiting();
// (A2) "BASE" STORAGE CACHE
evt.waitUntil(
caches.open("BASE")
.then((cache) => { return cache.addAll([
"4a-signage.html",
"4b-signage.css",
"4b-signage.js"
]); })
.catch((err) => { console.error(err) })
);
});
// (B) UPDATE CACHE "SIGNAL"
addEventListener("message", (evt) => {
// (B1) DELETE OLD CACHED PAGES
evt.waitUntil(caches.delete("PAGES"));
// (B2) CACHE NEW PAGES
evt.waitUntil(
caches.open("PAGES")
.then((cache) => {
// (B2-1) LIST OF PAGES TO CACHE
let pages = [];
for (let p of evt.data) {
let query = new URLSearchParams();
query.append("req", "load");
query.append("id", p["i"]);
query.append("u", p["u"]);
pages.push("4c-signage-ajax.php?" + query.toString());
}
// (B2-2) CACHE ALL
return cache.addAll(pages);
})
.catch((err) => { console.error(err) })
);
});
// (C) SERVE CACHED PAGES IF FOUND, FALLBACK TO NETWORK
self.addEventListener("fetch", (evt) => {
evt.respondWith(
caches.match(evt.request)
.then((res) => { return res || fetch(evt.request); })
);
});
At this stage, we already have a fully functioning system. This part is optional, but it extends the usability as an offline web app. That is, the signage will continue to work even without a connection to the server.
For you guys who have never heard of a “service-worker”, it is basically a piece of Javascript that is “installed” to run in the background; It is persistent and will run even when the user is offline. What this worker does:
- (C) “Hijack” the fetch requests and load them from the local storage cache. If the file is not in the local cache, fall back to loading from the network. That is, effectively supporting “offline mode”.
- (B) The answer to why we load a list of pages, instead of directly embedding them directly into a single page… So that we can cache them one by one. No, this is not the “usual browser cache”, but persistent cache storage.
5B) PAGE CACHING MECHANISM
// (D) WORKER & OFFLINE SUPPORT
cache : () => { if ("serviceWorker" in navigator) {
// (D1) FLAGS & VARS
let update = false, // cache update required?
old = localStorage.getItem("pages"); // previous list of pages
// (D2) THIS IS A NEW "INSTALLATION"
if (old == null) {
localStorage.setItem("pages", JSON.stringify(sign.pages));
update = true;
}
// (D3) CHECK IF AN "UPDATE" IS REQUIRED
else {
old = JSON.parse(old);
if (old.length != sign.pages.length) { update = true; }
else { for (let i in old) {
if (old[i]["i"]!=sign.pages[i]["i"] || old[i]["u"]!=sign.pages[i]["u"]) {
update = true;
break;
}
}}
}
// (D4) REGISTER SERVICE WORKER
navigator.serviceWorker.register("5-worker.js", {scope: "./"});
if (update) {
navigator.serviceWorker.ready.then(reg => {
reg.active.postMessage(sign.pages);
});
}
}}
In the last part of 4-signage.js
, cache()
will do the offline caching magic.
- Let’s roll back a little bit to (B2-1) of
4-signage.js
first. We get a list of pages to load from the server and put it intosign.pages
. - (D1 & D2) The next thing we do is store
sign.pages
intolocalStorage
. - (D3) On subsequent page loads, if the list of pages in
localStorage
is different from the server’s latestsign.pages
– We know that there have been page updates, and need to refresh the storage cache. - (D4) Register the service worker. If an update is required, we simply pass the entire list of new pages to the worker to update the cache.
5C) INSTALLABLE WEB APP
<!-- WEB APP MANIFEST -->
<!-- https://web.dev/add-manifest/ -->
<link rel="manifest" href="5-manifest.json">
Once again, this is optional. The offline caching mechanics are complete, this step will bring this web app another step forward – Make it installable. Some people think it is very difficult, but no. We only need to implement offline caching (already done), and define a manifest file.
5D) MANIFEST FILE
{
"short_name": "Signage",
"name": "Signage",
"icons": [{
"src": "images/favicon.png",
"sizes": "64x64",
"type": "image/png"
}, {
"src": "images/icon-512.png",
"sizes": "512x512",
"type": "image/png"
}],
"start_url": "4a-signage.html",
"scope": "/",
"background_color": "white",
"theme_color": "white",
"display": "standalone"
}
What is a “manifest file”? It’s only a simple file that contains information about your web app – The app name, icons, starting page, etc… Once installed, this will create an icon on the home screen or desktop; It launches in fullscreen mode and acts like an offline native app.
USEFUL BITS & LINKS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
MORE IMPROVEMENTS
Before the dumb trolls start to scream “this is an incomplete system” – Yes, this is a tutorial only. There are a lot of customizations that can be done before it is “production worthy”:
- User login, secure the admin page.
- Interface to manage file uploads.
- Add more “nice page templates”, or even create a “drag-and-drop” page builder.
- Better offline page caching – Not just the pages, but also the assets (images, audio, videos).
- If you want, you can explore “push updates” on your own. I.E. Instead of manually reloading the page, the server pushes an update to all online signages.
- If you want to support “multiple boards” – Add another
board
table, andboard_id
to thepages
table above.
The possibilities are endless – It’s up to you to build your own system.
COMPATIBILITY CHECKS
- Arrow Functions – CanIUse
- Fetch – CanIUse
- Cache Storage – CanIUse
- Service Worker – CanIUse
A modern “Grade A” browser is required for this to work properly.
LINKS & REFERENCES
- TinyMCE
- Cache Storage – MDN
- Service Worker – MDN
- Save & Load TinyMCE Content With PHP MySQL – Code Boxx
- HTML Javascript Digital Signage – Code Boxx
- Simple User Login System With PHP MySQL – Code Boxx
- PHP Admin Panel – Code Boxx
THE END
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!