Simple Notice Board With PHP MySQL (Free Download)

Welcome to a tutorial on how to create a simple notice board with PHP and MySQL. Need a “digital announcement board”? Well, it is actually not that difficult to create one – Read on!

 

 

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

CREATE TABLE `notes` (
  `note_id` bigint(20) NOT NULL,
  `note_sort` bigint(20) NOT NULL DEFAULT 0,
  `note_txt` text CHARACTER SET utf8mb4 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `notes`
  ADD PRIMARY KEY (`note_id`);
 
ALTER TABLE `notes`
  MODIFY `note_id` bigint(20) NOT NULL AUTO_INCREMENT;

First, we start with creating a database table to store the notes. This should be self-explanatory.

• note_id Primary key and auto-increment.
• note_sort Sort order of the notes, in ascending order. I.E. Sort order 0 will be on top, and 9 will be at the bottom.
• note_txt The text itself.

 

PART 2) PHP NOTES LIBRARY

<?php
class Notes {
  // (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 NOTES
  function getAll () {
    $this->query("SELECT * FROM `notes` ORDER BY `note_sort` ASC");
    return $this->stmt->fetchAll();
  }

  // (E) SAVE NOTE
  function save ($txt, $id=null) {
    // (E1) ADD NEW
    if ($id==null) {
      $this->query("UPDATE `notes` SET `note_sort`=`note_sort`+1");
      $this->query("INSERT INTO `notes` (`note_sort`, `note_txt`) VALUES (?,?)", [0, $txt]);
    }

    // (E2) UPDATE
    else {
      $this->query("UPDATE `notes` SET `note_txt`=? WHERE `note_id`=?", [$txt, $id]);
    }

    // (E3) DONE
    return true;
  }

  // (F) DELETE NOTE
  function del ($id) {
    $this->query("DELETE FROM `notes` WHERE `note_id`=?", [$id]);
    return true;
  }

  // (G) SAVE SORT ORDER
  function order ($order) {
    foreach (json_decode($order) as $sort=>$nid) {
      $this->query("UPDATE `notes` SET `note_sort`=? WHERE `note_id`=?", [$sort, $nid]);
    }
    return true;
  }
}

// (H) 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", "");

// (I) NEW NOTES OBJECT
$_NOTE = new Notes();

With the database in place, the next step is to create a library to work with it. Looks complicated, but keep calm and study closely.

• (A & B) When is $_NOTE = new Notes() created, the constructor connects to the database. The destructor closes the connection.
• (C) query() Just a support function to run an SQL query.
• (D to G) The actual “notice board” functions.
  • getAll() Get all notes.
  • save() Add or update a note.
  • del() Deletes the specified note.
  • order() Update the order of the notes.
• (H) Database settings, remember to change this to your own.
• (I) Self-explanatory.

 

 

PART 3) NOTICE BOARD PAGE

3A) THE HTML

<!-- (A) ADD/UPDATE NOTE -->
<form id="noteform">
  <input type="text" id="notetxt" required disabled>
  <input type="submit" id="notego" value="Add" disabled>
</form>
 
<!-- (B) NOTICE BOARD -->
<div id="board"></div>

Yep, the HTML page is a simple “2 sections”:

1. An HTML form to add or update a note.
2. The notes will be loaded into this container via AJAX.

 

3B) THE JAVASCRIPT

var board = {
  // (A) HELPER - AJAX FETCH
  fetch : (data, load) => {
    // (A1) FORM DATA
    let form = new FormData();
    for (let [k,v] of Object.entries(data)) { form.append(k,v); }

    // (A2) FETCH
    fetch("4-ajax.php", { method:"POST", body:form })
    .then(res => res.text())
    .then(txt => load(txt))
    .catch(err => console.error(err));
  },

  // (B) INITIALIZE NOTICE BOARD
  notes : [],    // current list of notes
  hwrap : null,  // html notice board wrapper
  hform : null,  // html add/update note form
  hadd : null,   // html add/update note text field
  hgo : null,    // html add/update note button
  hid : "",      // note id currently being edited
  hsel : null,   // current note being dragged or edited
  init : () => {
    // (B1) GET HTML ELEMENTS
    board.hwrap = document.getElementById("board");
    board.hform = document.getElementById("noteform");
    board.hadd = document.getElementById("notetxt");
    board.hgo = document.getElementById("notego");

    // (B2) ENABLE ADD NEW NOTE
    board.hform.onsubmit = () => board.save();
    board.hadd.disabled = false;
    board.hgo.disabled = false;

    // (B3) LOAD & DRAW HTML NOTES
    board.show();
  },

  // (C) SHOW NOTES
  show : () => { board.fetch(
    {"req" : "show"},
    txt => {
      // (C1) SET NEW HTML + GET ALL NOTES
      board.hwrap.innerHTML = txt;
      board.notes = document.querySelectorAll("#board .note");

      // (C2) ATTACH "EDIT", "DELETE", "SORT"
      if (board.notes.length>0) { for (let div of board.notes) {
        // (C2-1) DOUBLE CLICK TO EDIT
        div.ondblclick = () => board.edit(div);

        // (C2-2) CLICK TO DELETE
        div.querySelector(".del").onclick = () => board.del(div);

        // (C2-3) ON DRAG START - ADD DROPPABLE HINTS
        div.ondragstart = e => {
          board.hsel = e.target;
          for (let n of board.notes) {
            n.classList.add("drag");
            if (n != board.hsel) { n.classList.add("hint"); }
          }
        };

        // (C2-4) ON DRAG ENTER - HIGHLIGHT DROPZONE
        div.ondragenter = e => {
          if (div != board.hsel) { div.classList.add("active"); }
        };

        // (C2-5) DRAG LEAVE - REMOVE HIGHLIGHT DROPZONE
        div.ondragleave = e => div.classList.remove("active");

        // (C2-6) DRAG END - REMOVE ALL HIGHLIGHTS
        div.ondragend = e => { for (let n of board.notes) {
          n.classList.remove("drag");
          n.classList.remove("hint");
          n.classList.remove("active");
        }};

        // (C2-7) DRAG OVER - PREVENT DEFAULT "DROP", SO WE CAN DO OUR OWN
        div.ondragover = (e) => e.preventDefault();

        // (C2-8) ON DROP - REORDER NOTES & SAVE
        div.ondrop = e => {
          // (C2-8-1) PREVENT DEFAULT BROWSER DROP ACTION
          e.preventDefault();

          if (e.target != board.hsel) {
            // (C2-8-2) GET CURRENT & DROPPED POSITIONS
            let idrag = 0, // index of currently dragged
                idrop = 0; // index of dropped location
            for (let i=0; i<board.notes.length; i++) { if (board.hsel == board.notes[i]) { idrag = i; } if (e.target == board.notes[i]) { idrop = i; } } // (C2-8-3) REORDER HTML NOTES if (idrag > idrop) {
              board.hwrap.insertBefore(board.hsel, e.target);
            } else {
              board.hwrap.insertBefore(board.hsel, e.target.nextSibling);
            }

            // (C2-8-4) GET NEW ORDER
            board.notes = board.hwrap.querySelectorAll(".note");
            let order = [];
            for (let n of board.notes) { order.push(n.dataset.id); }

            // (C2-8-5) AJAX SAVE ORDER
            board.fetch({
              "req" : "order",
              "order" : JSON.stringify(order)
            }, txt => {
              if (txt == "OK") { board.show(); }
              else { alert(txt); }
            });
          }
        };
      }}
    }
  ); },

  // (D) EDIT NOTE
  edit : note => {
    // (D1) SELECTED NOTE & NOTE ID
    board.hsel = note.querySelector(".txt");
    board.hid = note.dataset.id;

    // (D-2) LOCK - NO DRAG NO DELETE WHILE EDITING
    for (let n of board.notes) { n.classList.add("lock"); }

    // (D-3) UPDATE NOTE FORM
    board.hadd.value = board.hsel.innerHTML;
    board.hgo.value = "Update";
    // board.hadd.focus();
    board.hadd.select();
  },

  // (E) SAVE NOTE
  save : () => {
    // (E1) AJAX POST
    board.fetch({
      "req" : "save",
      "note_id" : board.hid,
      "note_txt" : board.hadd.value
    },

    // (E2) ON SERVER RESPONSE
    txt => {
      // (E2-1) EDIT MODE ONLY - RESTORE NOTE FORM & ENABLE NOTES
      if (board.hid != "") {
        board.hid = "";
        board.hgo.value = "Add";
        board.hsel = board.hadd.value;
        for (let n of board.notes) { n.classList.remove("lock"); }
      }

      // (E2-2) PASS/FAIL
      if (txt == "OK") {
        board.hadd.value = "";
        board.show();
      } else { alert(txt); }
    });
    return false;
  },

  // (F) DELETE NOTE
  del : note => { if (confirm("Delete note?")) {
    board.fetch({
      "req" : "del",
      "note_id" : note.dataset.id
    }, txt => {
      if (txt == "OK") { board.show(); }
      else { alert(txt); }
    });
  }}
};

Yep, more “panic code”. Not going to explain line-by-line, but the entire notice board is essentially driven via AJAX.

1. board.fetch() A helper function to run an AJAX call to the PHP AJAX handler 4-ajax.php.
2. On page load, board.init() will run. Basically, get all the HTML elements, enable the add/update note form, and load the notes.
3. board.show() AJAX load the notes from the server and put them into <div id="board">.
4. board.edit() When the user double clicks on an existing note to edit it – Puts the note text into the add/update note form.
5. board.save() Save a new note, or update one.
6. board.del() Delete a selected note.

 

 

PART 4) AJAX HANDLER

<?php
if (isset($_POST["req"])) {
  require "2-lib-notes.php";
  switch ($_POST["req"]) {
  // (A) INVALID REQUEST
  default: echo "Invalid request"; break;
 
  // (B) SHOW ALL NOTES
  case "show":
    $notes = $_NOTE->getAll();
    if (count($notes)>0) { foreach ($notes as $n) {
    printf(
      "<div class='note' draggable='true' data-id='%u'>
         <div class='del'>X</div>
         <div class='txt'>%s</div>
       </div>",
       $n["note_id"], $n["note_txt"]
    );
  }}
  break;
 
  // (C) SAVE NOTE
  case "save":
    echo $_NOTE->save($_POST["note_txt"], $_POST["note_id"])
      ? "OK" : "ERROR";
    break;
 
  // (D) DELETE NOTE
  case "del":
    echo $_NOTE->del($_POST["note_id"])
      ? "OK" : "ERROR";
    break;
 
  // (E) SAVE NEW ORDER
  case "order":
    echo $_NOTE->order($_POST["order"])
      ? "OK" : "ERROR";
    break;
}}

No, no need to panic. The Javascript pretty much sends a $_POST["req"] to this script to request a certain “service”.

• (C) Remember board.show() from earlier? This is the part that loads the notes from the database and generates the HTML.
• (D) Remember board.save()? This part updates the database.
• (E) Remember board.del()? This deletes the selected note in the database.
• (F) Yep… Don’t think this needs an explanation.

 

 

 

A COUPLE OF IMPROVEMENT IDEAS

Yes, this is only a barebones system. Plenty of improvements can be made:

• Just add a board_id or user_id to the database, if you want to support multiple users & boards.
• There’s no login or admin panel to keep things simple here. See the links below if you need one.
• The lazy way for a “view only” notice board – Just remove the add/update note form, and remove the “drag to sort”. That is literally, $notes = $_NOTES->getAll(); foreach ($notes as $n) { DRAW HTML }.
• As you can see, $_NOTE->order() runs UPDATE multiple times to update the sort order. This is fine if you have a dozen or so notes, but it generally becomes more inefficient when there are more notes… I highly doubt there will be hundreds of notes on a single board. But when that point comes, it will be wise to come up with alternate ways to deal with the sort order.

 

COMPATIBILITY CHECKS

• Arrow Functions – CanIUse
• Draggable – CanIUse
• Fetch – CanIUse

This example will work generally well on all modern “Grade A” browsers.

 

 

LINKS & REFERENCES

• Simple PHP Admin Panel – Code Boxx
• PHP User Role Management – Code Boxx
• Javascript Notice Board – Code Boxx