Welcome to a tutorial on how to create a grocery list with PHP and MySQL. Yes, the grocery list. A classic school assignment that lecturers have “reused” over many decades, staying true to the “reusability” concept. That aside, it is still a good way to learn full-stack web development – Read on for the example!
TABLE OF CONTENTS
PHP MYSQL GROCERY LIST
All right, let us now get into the details of building a grocery list using PHP and MySQL.
PART 1) GROCERY ITEMS DATABASE
CREATE TABLE `items` (
`id` bigint(20) NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
`qty` int(11) NOT NULL,
`got` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `items`
ADD PRIMARY KEY (`id`);
ALTER TABLE `items`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
The first step of every project, start with the database and establish the foundations. This is a simple table to store the grocery items.
id
Primary key and auto-increment.name
Item name.qty
Item quantity.got
Item “status” – 0 for “not yet” and 1 for “accquired”.
PART 2) GROCERY ITEMS LIBRARY
<?php
class Items {
// (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 ITEMS
function get () {
$this->query("SELECT * FROM `items`");
return $this->stmt->fetchAll();
}
// (E) ADD NEW ITEM
function add ($name, $qty) {
$this->query("INSERT INTO `items` (`name`, `qty`) VALUES (?,?)", [$name, $qty]);
return true;
}
// (F) UPDATE ITEM STATUS
function update ($got, $id) {
$this->query("UPDATE `items` SET `got`=? WHERE `id`=?", [$got, $id]);
return true;
}
// (G) DELETE ITEM
function delete ($id) {
$this->query("DELETE FROM `items` WHERE `id`=?", [$id]);
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 ITEMS OBJECT
$_ITEMS = new Items();
Next, we create a PHP library to work with the database. This may look complicated at first, but keep calm and look closely.
- (A, B, I) When
$_ITEMS = new Items()
is created, the constructor will automatically connect to the database. The destructor closes the connection. - (C)
query()
is a simple helper function to run an SQL query. - (D To G) The actual functions to deal with the grocery items.
get()
Get all items.add()
Add a new item.update()
Update the “got it/not yet” status of the item.delete()
Remove an item.
- (H) Self-explanatory. Change these to your own.
Yep, the entire library is pretty much just a collection of SQL statements.
PART 3) AJAX HANDLER
<?php
if ($_POST["req"]) {
require "2-lib-items.php";
switch ($_POST["req"]) {
// (A) GET ALL ITEMS
case "get":
echo json_encode($_ITEMS->get());
break;
// (B) ADD ITEM
case "add":
$_ITEMS->add($_POST["name"], $_POST["qty"]);
echo "OK";
break;
// (C) UPDATE ITEM STATUS
case "update":
$_ITEMS->update($_POST["got"], $_POST["id"]);
echo "OK";
break;
// (D) DELETE ITEM
case "delete":
$_ITEMS->delete($_POST["id"]);
echo "OK";
break;
}}
Of course, the library is not going to do anything by itself. So we need to create an “endpoint” to accept requests from the client side. How this works is very simple – Just send $_POST["req"] = "REQUEST"
to this script, followed by the required parameters. For example, send $_POST["req"] = "delete"
and $_POST["id"] = 999
to delete item 999.
PART 4) USER INTERFACE
4A) THE HTML
<div id="gro-wrap">
<!-- (A) ADD ITEM -->
<form id="gro-form">
<input type="number" id="gro-qty" min="1" value="1" placeholder="Quantity" required disabled>
<input type="text" id="gro-item" placeholder="Item Name" required disabled>
<button id="gro-add" class="material-icons" disabled>add</button>
</form>
<!-- (B) ITEM LIST -->
<div id="gro-list"></div>
</div>
Well, the server-side foundations are now complete. All that’s left is to deal with the client-side HTML/CSS/Javascript. This is a simple page with only 2 sections.
- An “add item”
<form>
with 2 fields – The item name and quantity. - An empty
<div>
to generate the grocery list into.
4B) AJAX HELPER
var glist = {
// (A) SUPPORT FUNCTION - AJAX FETCH
ajax : (data, onload) => {
// (A1) FORM DATA
var form = new FormData;
for (let [k,v] of Object.entries(data)) { form.append(k, v); }
// (A2) AJAX FETCH
fetch("3-ajax-items.php", { method:"post", body:form })
.then(res => res.text())
.then(txt => {
if (txt == "OK") { onload(); }
else {
try {
var json = JSON.parse(txt);
onload(json);
} catch (err) {
alert("AJAX fetch error");
console.error(err, txt);
}
}
})
.catch(err => {
alert("AJAX fetch error");
console.error(err);
});
}
}
With that, we can now move into the Javascript. Since this app is pretty much AJAX-driven, we will start with creating a glist.ajax()
helper function to fire an AJAX call to 3-ajax-items.php
.
4C) INITIALIZE
// (B) INITIALIZE GROCERY LIST
items : [], // current grocery list
hqty : null, // html add quantity field
hitem : null, // html add item field
hlist : null, // html <div> grocery list
init : () => {
// (B1) GET HTML ELEMENTS
glist.hqty = document.getElementById("gro-qty");
glist.hitem = document.getElementById("gro-item");
glist.hlist = document.getElementById("gro-list");
// (B2) ENABLE FORM
glist.hqty.disabled = false;
glist.hitem.disabled = false;
document.getElementById("gro-add").disabled = false;
document.getElementById("gro-form").onsubmit = glist.add;
// (B3) DRAW HTML GROCERY LIST
glist.draw();
},
// ...
window.addEventListener("load", glist.init);
Next, we initialize the “app”. Pretty much just getting the related HTML elements, enabling the HTML “add item” form, and drawing the grocery list.
4D) DRAW GROCERY ITEMS
// (C) DRAW GROCERY LIST
draw : () => glist.ajax({ req : "get" }, (items) => {
// (C1) NO ITEMS
if (items.length == 0) {
glist.hlist.innerHTML = "<div class='item-row item-name'>No items found.</div>";
}
// (C2) DRAW ITEMS
else {
glist.hlist.innerHTML = "";
for (let i of items) {
// (C2-1) ITEM ROW
let row = document.createElement("div");
row.className = i.got=="1" ? "item-row yes" : "item-row no";
glist.hlist.appendChild(row);
// (C2-2) DELETE BUTTON
let del = document.createElement("div");
del.className = "item-del material-icons";
del.innerHTML = "delete";
del.onclick = () => glist.delete(i.id);
row.appendChild(del);
// (C2-3) ITEM QUANTITY & NAME
let name = document.createElement("div");
name.innerHTML = `${i.qty} X ${i.name}`;
name.className = "item-name";
if (i.got=="1") { name.classList.add("item-got"); }
row.appendChild(name);
// (C2-4) ITEM ACCQUIRED
let ok = document.createElement("button");
ok.className = "item-ok material-icons";
ok.innerHTML = i.got=="1" ? "done" : "clear";
ok.onclick = () => glist.toggle(i.got=="1" ? 0 : 1, i.id);
row.appendChild(ok);
}
}
}),
Once again, no need to panic. This function pretty much fetches the grocery items from 3-ajax-items.php
, and generates the HTML list. That’s all.
4E) ADD, UPDATE, DELETE GROCERY ITEMS
// (C) ADD NEW ITEM TO THE LIST
add : () => {
glist.ajax({
req : "add",
name : glist.hitem.value,
qty : glist.hqty.value
}, () => {
glist.hqty.value = 1;
glist.hitem.value = "";
glist.draw();
});
return false;
},
// (D) TOGGLE ITEM ACCQUIRED STATUS
toggle : (got, id) => glist.ajax({
req : "update",
got: got,
id : id
}, glist.draw),
// (E) DELETE SELECTED ITEM
delete : id => { if (confirm("Remove this item?")) {
glist.ajax({
req : "delete",
id : id
}, glist.draw);
}}
Lastly, to add/update/delete grocery items – It’s all just doing AJAX calls to 3-ajax-items.php
.
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.
COMPATIBILITY CHECKS
- Arrow Functions – CanIUse
- JSON – CanIUse
This example will work on all modern “Grade A” browsers.
LINKS & REFERENCES
- Offline Javascript Grocery List – 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!