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
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
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”:
- An HTML form to add or update a note.
- 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.
board.fetch()
A helper function to run an AJAX call to the PHP AJAX handler4-ajax.php
.- On page load,
board.init()
will run. Basically, get all the HTML elements, enable the add/update note form, and load the notes. board.show()
AJAX load the notes from the server and put them into<div id="board">
.board.edit()
When the user double clicks on an existing note to edit it – Puts the note text into the add/update note form.board.save()
Save a new note, or update one.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.
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
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.
EXTRA 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
oruser_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()
runsUPDATE
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
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!