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
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
EXAMPLE CODE DOWNLOAD
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
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.
EXTRAS
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!
Thank you for the code, Have installed successful but get this error
Parse error
: syntax error, unexpected identifier “church_db”, expecting “)” in
C:\xampp\htdocs\church_management\2-lib-notes.php
on line 9
Can you advise. Thanks Martin
As it is – Missing a ) or typo error.