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

 

 

 

 

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.

 

 

 

SUPPORT

600+ free tutorials & projects on Code Boxx and still growing. I insist on not turning Code Boxx into a "paid scripts and courses" business, so every little bit of support helps.

Buy Me A Meal Code Boxx eBooks

 

EXAMPLE CODE DOWNLOAD

Click here for the 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.

 

 

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