Simple Grocery List With PHP MySQL (Free Download)

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

 

 

EXAMPLE CODE DOWNLOAD

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.

 

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

 

 

 

 

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.

1. An “add item” <form> with 2 fields – The item name and quantity.
2. 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.

 

 

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