Digital Signage With PHP MySQL (Free Download)

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!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

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

 

 

 

PART 1) THE DATABASE

1A) “VERSION”

-- (A) PAGES "VERSION"
CREATE TABLE `page_ver` (
  `pg_date` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `page_ver`
  ADD PRIMARY KEY (`pg_date`);

First, we have a very simple “version” table. Yep, this only holds a single page_ver “last updated timestamp”. Every time we add/edit/delete/sort the pages, this timestamp will be updated.

 

 

1B) PAGES (OR SLIDES)

-- (B) PAGES
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,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
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;

Next, a table to store all the pages.

• 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.

 

 

PART 2) PHP LIBRARY

<?php
class Pages {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = null;
  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 () {
    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) : void {
    $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` 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) CHECK PAGES (GET PAGE ORDER FOR SIGNAGE)
  function check () {
    // (F1) "VERSION" TIMESTAMP
    $this->query("SELECT UNIX_TIMESTAMP(`pg_date`) FROM `page_ver`");
    $check = ["last"=>$this->stmt->fetchColumn(), "pages"=>[]];
 
    // (F2) PAGES ORDER
    $this->query("SELECT `pg_id` FROM `pages` ORDER BY `pg_sort` ASC");
    while ($r = $this->stmt->fetch()) { $check["pages"][] = $r["pg_id"]; }
 
    // (F3) DONE
    return $check;
  }
 
  // (G) UPDATE TIMESTAMP
  function touch () {
    $this->query("UPDATE `page_ver` SET `pg_date`=?", [date("Y-m-d H:i:s")]);
  }

  // (H) SAVE PAGE
  function save ($title, $txt, $id=null) {
    // (H1) 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]);
    }

    // (H2) UPDATE PAGE
    else {
      $this->query("UPDATE `pages` SET `pg_title`=?, `pg_txt`=? WHERE `pg_id`=?", [$title, $txt, $id]);
    }

    // (H3) TOUCH & DONE
    $this->touch();
    return true;
  }

  // (I) DELETE PAGE
  function del ($id) {
    $this->query("DELETE FROM `pages` WHERE `pg_id`=?", [$id]);
    this->touch();
    return true;
  }

  // (J) 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]);
    }
    $this->touch();
    return true;
  }
}

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

// (L) 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, L) 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 F) Functions to “get pages”.
  • getAll() Get all pages.
  • get() Get the specified page.
  • check() Get the last updated timestamp and page sort order.
• (G to J) Function to “modify pages”.
  • touch() Update the “last updated” timestamp to now.
  • save() Add or update a page.
  • del() Delete a specified page.
  • order() Save the new page order.
• (K) Self-explanatory. Change the settings to your own.

 

 

PART 3) ADMIN PAGE

 

3A) THE HTML

<!-- (A) LIST OF PAGES -->
<div id="pgA">
  <div id="pgAdd" class="flex" onclick="adm.show()">
    &#43; ADD PAGE
  </div>
  <div id="pgList"></div>
</div>
 
<!-- (B) ADD/EDIT PAGE -->
<form id="pgB" class="hide" onsubmit="return adm.save()"></form>

With the foundations in place, we can now create a simple “admin page”. There are only 2 sections in this one.

1. <div id="pgA"> List of existing pages.
2. <form 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
  pgD : null, // current page being dragged
  init : () => {
    // (A1) GET HTML ELEMENTS
    adm.pgA = document.getElementById("pgA");
    adm.pgB = document.getElementById("pgB");
    adm.pgL = document.getElementById("pgList");

    // (A2) INIT LOAD PAGES LIST
    adm.list();
  },

  // (B) SUPPORT FUNCTION - AJAX FETCH
  fetch : (data, load) => {
    // (B1) FORM DATA
    let 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))
    .catch(err => console.error(err));
  },

  // (C) SUPPORT FUNCTION - TOGGLE PAGE
  toggle : pg => {
    adm.pgA.classList.add("hide");
    adm.pgB.classList.add("hide");
    document.getElementById("pg"+pg).classList.remove("hide");
  },

  // (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("#pgList.row");
      if (adm.pgS.length>0) { for (let div of adm.pgS) {
        // (D3-1) ON DRAG START - ADD DROPPABLE HINTS
        div.ondragstart = e => {
          adm.pgD = e.target;
          for (let p of adm.pgS) {
            p.classList.add("drag");
            if (p != adm.pgD) { p.classList.add("hint"); }
          }
        };

        // (D3-2) ON DRAG ENTER - HIGHLIGHT DROPZONE
        div.counter = 0;
        div.ondragenter = e => {
          div.counter++;
          if (div != adm.pgD ) { div.classList.add("active"); }
        };

        div.ondragleave = e => {
          div.counter--;
          if (div.counter==0) { div.classList.remove("active"); }
        };

        // (D3-4) DRAG END - REMOVE ALL HIGHLIGHTS
        div.ondragend = e => { for (let p of adm.pgS) {
          p.counter = 0;
          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 (div != adm.pgD) {
            // (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.pgD == adm.pgS[i]) { idrag = i; }
              if (div == adm.pgS[i]) { idrop = i; }
            }
 
            // (D3-6-3) REORDER HTML NOTES
            if (idrag > idrop) {
              adm.pgL.insertBefore(adm.pgD, div);
            } else {
              adm.pgL.insertBefore(adm.pgD, div.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") { 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
      // https://www.tiny.cloud/docs/advanced/available-menu-items/
      tinymce.remove();
      tinymce.init({
        selector : "#pgTxt",
        menubar : false,
        plugins : "image textcolor lists code",
        toolbar: "fontfamily fontsize | 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("pgTitle").value,
      txt : tinymce.get("pgTxt").getContent(),
      id : document.getElementById("pgId").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 to 3c-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 <form 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
if (isset($_POST["req"])) {
  require "2-lib-pg.php";
  switch ($_POST["req"]) {
  // (A) LIST ALL PAGES
  case "list":
    $pages = $_PG->getAll();
    foreach ($pages as $p) { printf(
      "<div class='row flex' draggable='true' data-id='%u'>
        <div class='ico' onclick='adm.del(%u)'>&#10006;</div>
        <strong class='flexGrow'>%s</strong>
        <div class='ico' onclick='adm.show(%u)'>&#9998;</div>
      </div>",
        $p["pg_id"], $p["pg_id"], $p["pg_title"], $p["pg_id"]
      );
    }
    break;
 
  // (B) SHOW PAGE - ADD/EDIT
  case "show":
    // (B1) GET PAGE
    if (is_numeric($_POST["id"])) { $pg = $_PG->get($_POST["id"]); }
 
    // (B2) PAGE FORM ?>
    <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>
 
    <input type="hidden" id="pgId" value="<?=isset($pg)?$pg["pg_id"]:""?>" required>
    <input type="button" value="Back" onclick="adm.toggle('A')">
    <input type="button" value="Save" onclick="adm.save()">
    <?php break;
 
  // (C) SAVE PAGE
  case "save":
    $_PG->save($_POST["title"], $_POST["txt"], $_POST["id"]);
    echo "OK";
    break;
 
  // (D) DELETE PAGE
  case "del":
    $_PG->del($_POST["id"]);
    echo "OK";
    break;
 
  // (E) SORT PAGES
  case "order":
    $_PG->order($_POST["order"]);
    echo "OK";
    break;
}}

All AJAX calls from the Javascript will be handled by this one.

1. list Get all pages from the database and put them into a nice HTML form.
2. show Add/edit a page.
3. save Add a new page, or update one.
4. del Delete a page.
5. 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 PAGE

 

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) DATA
    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))
    .catch(err => console.error(err));
  },

  // (B) PAGE INIT
  hWrap : null, // html wrapper
  version : null, // last updated pages data
  pages : [], // loaded pages
  delay : 5000, // delay between each page (ms)
  init : () => {
    // (B1) GET HTML WRAPPER
    sign.hWrap = document.getElementById("pages");

    // (B2) RESTORE PAGES DATA
    sign.version = localStorage.getItem("version");
    if (sign.version != null) { sign.version = JSON.parse(sign.version); }

    // (B3) REGISTER SERVICE WORKER
    if ("serviceWorker" in navigator) {
      navigator.serviceWorker.register("5-worker.js", {scope: "/"});
    }

    // (B4) IF ONLINE - CHECK FOR POSSIBLE UPDATES
    // (B4) ELSE - JUST LOAD FROM CACHE
    if (navigator.onLine) {
      sign.fetch({ req : "check" }, res => {
        res = JSON.parse(res);
        if (sign.version==null || sign.version.last < res.last) { sign.version = res; localStorage.setItem("version", JSON.stringify(sign.version)); sign.cache(); } sign.load(); }); } else { sign.load(); } }, // (C) LOAD PAGES load : () => { if (sign.version != null) {
    for (let id of sign.version.pages) {
      sign.fetch({ req : "get", id : id }, res => {
        let div = document.createElement("div");
        div.className = "page";
        div.innerHTML = res;
        sign.hWrap.appendChild(div);
        sign.pages.push(div);
        if (sign.pages.length >= sign.version.pages.length) {
          sign.run();
          setInterval(sign.run, sign.delay);
        }
      });
    }
  }},

  // (D) UPDATE CACHE
  cache : async () => {
    // (D1) REMOVE OLD CACHE
    await caches.delete("PAGES");

    // (D2) LIST OF PAGES TO CACHE
    let updater = [];
    for (let id of sign.version.pages) {
      updater.push("4c-signage-ajax.php?req=get&id=" + id);
    }

    // (D3) GO!
    caches.open("PAGES")
    .then(cache => cache.addAll(updater))
    .catch(err => console.error(err));
  },

  // (E) ROTATE PAGES
  current : -1, // current page
  run : () => {
    // (E1) HIDE ALL
    for (let p of sign.pages) { p.classList.remove("show"); }
    
    // (E2) SHOW NEXT PAGE
    sign.current++;
    if (sign.current >= sign.pages.length) { sign.current = 0; }
    sign.pages[sign.current].classList.add("show");
  }
};
window.onload = sign.init;

“Scary code” once again, but a quick walkthrough:

1. sign.fetch() A helper function to do AJAX calls.
2. sign.init() will run on page load, it does a couple of things.
   • (B1) Get <div id="pages">
   • (B2) Remember function check() of the PHP library? We basically store the timestamp in localStorage.version, so this page only needs to update when necessary.
   • (B3) Register a service worker. More on that later.
   • (B4) If the user is online, we do a check against the server for page updates. If not, just load whatever is in the local cache.
3. Load the pages.
4. Cache the pages. This will only run if there are updates.
5. When all the pages are loaded, we set an interval timer to rotate between them. The end.

 

 

4C) AJAX HANDLER

<?php
if (isset($_GET["req"])) {
  require "2-lib-pg.php";
  switch ($_GET["req"]) {
  // (A) CHECK PAGE "VERSION"
  case "check":
    echo json_encode($_PG->check());
    break;
 
  // (B) GET PAGE
  case "get":
    $pg = $_PG->get($_GET["id"]);
    if (isset($pg["pg_txt"])) { echo $pg["pg_txt"]; }
    else { echo "Invalid page"; }
    break;
}}

• check Returns the “last updated” timestamp, and list of pages to load.
• get Loads the given page.

 

PART 5) PROGRESSIVE WEB APP & OFFLINE SUPPORT

5A) SERVICE WORKER

// (A) CREATE/INSTALL CACHE
self.addEventListener("install", evt => {
  self.skipWaiting();
  evt.waitUntil(
    caches.open("BASE")
    .then(cache => cache.addAll([
      "4a-signage.html",
      "4b-signage.css",
      "4b-signage.js", 
      "5-manifest.json"
    ]))
    .catch(err => console.error(err))
  );
});
 
// (B) CLAIM CONTROL INSTANTLY
self.addEventListener("activate", evt => self.clients.claim());
 
// (C) LOAD FROM CACHE FIRST, FALLBACK TO NETWORK IF NOT FOUND
self.addEventListener("fetch", evt => evt.respondWith(
  caches.match(evt.request).then(res => res || fetch(evt.request))
));

• (A) Caches the project files when the service worker is first installed.
• (C) “Hijack” the fetch requests and load them from the local storage cache. If the file is not in the local cache, falls back to loading from the network.

Remember from earlier that we cached the pages? Yes, this worker does the “offline mode” magic by serving the cached pages instead of loading them from the network.

 

5B) 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.

 

5C) 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.

 

 

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, and board_id to the pages 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