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!

ⓘ 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 PHP Board Useful Bits & Links
The End

 

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.
  • Edit 2-lib-notes.php, change the database settings to your own.
  • Launch 3a-notice-board.html in the browser. Captain Obvious, use http:// and not file://.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

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 NOTICE BOARD

All right, let us now get into the details of creating a notice board using PHP and MySQL.

 

PART 1) THE DATABASE

1-database.sql
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=latin1;
 
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

2-lib-notes.php
<?php
class Notes {
  // (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 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", "utf8");
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

3a-notice-board.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

3b-notice-board.js
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); });
  },

  // (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 = () => { return 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

4-ajax.php
<?php
// (A) LOAD LIBRARY
require "2-lib-notes.php";
 
if (isset($_POST["req"])) { switch ($_POST["req"]) {
  // (B) INVALID REQUEST
  default: echo "Invalid request"; break;
 
  // (C) 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;
 
  // (D) SAVE NOTE
  case "save":
    echo $_NOTE->save($_POST["note_txt"], $_POST["note_id"])
      ? "OK" : "ERROR";
    break;
 
  // (E) DELETE NOTE
  case "del":
    echo $_NOTE->del($_POST["note_id"])
      ? "OK" : "ERROR";
    break;
 
  // (F) 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.

 

 

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.

 

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

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

 

LINKS & REFERENCES

 

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!

Leave a Comment

Your email address will not be published.