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

 

 

 

 

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.

 

 

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.

 

 

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