POS System With PHP MySQL (Free Download)

Welcome to a tutorial on how to create a POS system with PHP and MySQL. Yep, there are already several paid PHP POS systems and tutorials all over the Internet. So this one is going to be slightly different. It’s not just PHP MySQL, but also a progressive web app to reduce dependency on the server – Read on!

 

 

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) THE DATABASE

1A) ITEMS TABLE

-- (A) ITEMS
CREATE TABLE `items` (
  `item_id` bigint(20) NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `item_price` decimal(12,2) NOT NULL,
  `item_image` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `items`
  ADD PRIMARY KEY (`item_id`),
  ADD KEY `item_name` (`item_name`);

ALTER TABLE `items`
  MODIFY `item_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

INSERT INTO `items` (`item_id`, `item_name`, `item_price`, `item_image`) VALUES
(1, 'Banana', '1.20', 'banana.png'),
(2, 'Cherry', '2.30', 'cherry.png'),
(3, 'Ice Cream', '3.40', 'icecream.png'),
(4, 'Orange', '4.50', 'orange.png'),
(5, 'Strawberry', '5.60', 'strawberry.png'),
(6, 'Watermelon', '6.70', 'watermelon.png');

First, we have a table to store all the available items for sale.

• item_id Primary key and auto-increment.
• item_name Product name.
• item_price Selling price.
• item_image Product picture, place in the assets folder.

 

 

1B) ORDERS TABLE

-- (B) ORDERS
CREATE TABLE `orders` (
  `order_id` bigint(20) NOT NULL,
  `oder_date` datetime NOT NULL DEFAULT current_timestamp(),
  `order_total` decimal(12,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `orders`
  ADD PRIMARY KEY (`order_id`),
  ADD KEY `oder_date` (`oder_date`);

ALTER TABLE `orders`
  MODIFY `order_id` bigint(20) NOT NULL AUTO_INCREMENT;

This table should be self-explanatory, to save orders when the staff checks out.

• orders_id Primary key and auto-increment.
• order_date Timestamp when the order is checked out.
• order_total Total amount.

 

 

1C) ORDER ITEMS TABLE

-- (C) ORDER ITEMS
CREATE TABLE `order_items` (
  `order_item_id` bigint(20) NOT NULL,
  `order_id` bigint(20) NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `item_price` decimal(12,0) NOT NULL,
  `item_qty` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `order_items`
  ADD PRIMARY KEY (`order_item_id`),
  ADD KEY `order_id` (`order_id`),
  ADD KEY `item_name` (`item_name`);

ALTER TABLE `order_items`
  MODIFY `order_item_id` bigint(20) NOT NULL AUTO_INCREMENT;

The items for each order.

• order_item_id Primary key and auto-increment.
• order_id Foreign key.
• item_name Name of the item.
• item_price Price of the item (each).
• item_qty Item quantity.

Some of you guys should have spotted – Isn’t it easier to just use an item_id foreign key? Well, items can be changed and updated over time. That is why we capture the name and price instead; This is not redundancy but “accuracy at the time of checkout”.

 

PART 2) PHP POS LIBRARY

<?php
class POS {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  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 - EXECUTE SQL QUERY
  function query ($sql, $data=null) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }
 
  // (D) SAVE ITEM
  function saveItem ($name, $price, $img=null, $id=null) {
    // (D1) NEW ITEM
    if ($id==null) {
      $sql = "INSERT INTO `items` (`item_name`, `item_price`, `item_image`) VALUES (?,?,?)";
      $data = [$name, $price, $img];
    }
 
    // (D2) UPDATE ITEM
    else {
      $sql = "UPDATE `items` SET `item_name`=?, `item_price`=?, `item_image`=? WHERE `item_id`=?";
      $data = [$name, $price, $img, $id];
    }
 
    // (D3) RUN SQL + UPDATE TIMESTAMP
    // YOU MIGHT WANT TO KEEP THIS IN THE DATABASE INSTEAD...
    $this->query($sql, $data);
    file_put_contents(
      __DIR__ . DIRECTORY_SEPARATOR . "updated.php",
      "<?php define('POS_UPDATE', ".strtotime("now").");"
    );
    return true;
  }
 
  // (E) CHECKOUT ORDER
  function checkout ($items, $total, $timestamp) {
    // (E1) CHECK TIMESTAMP
    if ($timestamp < POS_UPDATE) {
      $this->error = POS_UPDATE;
      return false;
    }

    // (E2) ORDERS ENTRY
    $this->query(
      "INSERT INTO `orders` (`order_total`) VALUES (?)", [$total]
    );

    // (E3) ORDER ITEMS
    $id = $this->pdo->lastInsertId();
    $sql = "INSERT INTO `order_items` (`order_id`, `item_name`, `item_price`, `item_qty`) VALUES ";
    $data = [];
    $items = json_decode($items, true);
    foreach ($items as $i) {
      $sql .= "(?,?,?,?),";
      $data[] = $id;
      $data[] = $i["n"];
      $data[] = $i["p"];
      $data[] = $i["q"];
    }
    $sql = substr($sql, 0, -1) . ";";
    $this->query($sql, $data);

    // (E4) DONE
    return true;
  }

  // (F) GET ALL ITEMS
  function getAll () {
    $this->query("SELECT * FROM `items`");
    return $this->stmt->fetchAll();
  }
}

// (G) 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", "");

// (H) LAST UPDATED
require "updated.php";

// (I) NEW POS OBJECT
$_POS = new POS();

Next, we have a PHP library to work with the database. This looks “scary” at first, but keep calm and look closely.

• (A, B, I) When $_POS = new POS() is created, the constructor automatically connects to the database. The destructor closes the connection.
• (C) query() A simple helper function to run an SQL statement.
• (D To F) The “actual library functions”.
  • save() Add or update an item.
  • checkout() Create a new order.
  • getAll() Get all items.
• (G) Remember to change the settings to your own.

P.S. Take extra note of D3 and H, a PHP file (POS_UPDATE = UNIX TIMESTAMP) is generated when an item is saved. We will use Javascript to save a copy of the items into a browser indexed database later, this is used to indicate to the browsers to fetch updated products from the server.

 

 

PART 3) POS AJAX HANDLER

<?php
require "2-lib-pos.php";
if (isset($_POST["req"])) { switch ($_POST["req"]) {
  // (A) CHECK LAST UPDATE
  case "check":
    echo POS_UPDATE;
    break;

  // (B) GET ALL ITEMS
  case "getAll":
    echo json_encode($_POS->getAll());
    break;

  // (C) CHECKOUT ORDER
  case "checkout":
    echo $_POS->checkout($_POST["items"], $_POST["total"], $_POST["timestamp"])
      ? "OK" : $_POS->error ;
    break;
}}

Believe it or not, this script is pretty much a “complete system without user interface”.

• How it works is very simple, just send $_POST["req"]="REQUEST" along with the required parameters to this script.
• A checkout example.
  • $_POST["req"] = "checkout"
  • $_POST["items"] = '[{n:"NAME", p:"PRICE", q:"QUANTITY"}, {n:"NAME", p:"PRICE", q:"QUANTITY"}, ...]'
  • $_POST["total"] = "AMOUNT"
  • $_POST["timestamp"] = "UNIX TIMESTAMP"

Yep. Does not matter if it is HTML, iOS, or Android app. Just send $_POST to this endpoint.

 

PART 4) POS PAGE

4A) THE HTML

<!-- (A) ITEMS LIST -->
<div id="left">
  <div id="list"></div>
</div>
 
<!-- (B) CART ITEMS -->
<div id="right">
  <div id="cart"></div>
</div>

For the POS interface, we have a very simple HTML page.

• <div id="list"> Items list.
• <div id="cart"> Cart items.

 

4B) POS JAVASCRIPT

var pos = {
  // (A) PROPERTIES
  iName : "POS", // idb name
  iDB : null, iTX : null, // idb object & transaction
  updated : null, // last updated timestamp
  items : null, // items list
  hList : null, // html items list
  hCart : null, // html cart items

  // (B) HELPER FUNCTION - AJAX FETCH
  fetch : (req, data, after) => {
    // (B1) FORM DATA
    let form = new FormData();
    form.append("req", req);
    if (data != null) { for (let [k,v] of Object.entries(data)) {
      form.append(k, v);
    }}

    // (B2) GO!
    fetch("3-ajax-pos.php", { method: "post", body: form })
    .then(res => res.text())
    .then(txt => after(txt))
    .catch(err => console.error(err));
  },

  // (C) HELPER FUNCTION - UPDATE INDEXED DATABASE ITEMS
  update : ts => pos.fetch("getAll", null, items => {
    // (C1) CLEAR OLD ITEMS
    pos.iTX().clear();

    // (C2) UPDATE ITEMS
    items = JSON.parse(items);
    let count = 0;
    for (let i of items) {
      let req = pos.iTX().put(i);
      req.onsuccess = () => {
        count++;
        if (count==items.length) {
          localStorage.setItem("POSUpdate", ts);
          pos.draw(cart.empty);
          alert("Item database updated.");
        }
      };
    }
  }),

  // (D) INITIALIZE
  init : () => {
    // (D1) IDB SUPPORT CHECK
    window.indexedDB = window.indexedDB || window.mozIndexedDB || window.webkitIndexedDB || window.msIndexedDB;
    if (!window.indexedDB) {
      alert("Your browser does not support indexed database.");
      return false;
    }

    // (D2) OPEN "POS" DATABASE
    let req = window.indexedDB.open(pos.iName, 1);

    // (D3) ON DATABASE ERROR
    req.onerror = evt => {
      alert("Indexed DB init error - " + evt.message);
      console.error(evt);
    };

    // (D4) UPGRADE NEEDED
    req.onupgradeneeded = evt => {
      // (D4-1) INIT UPGRADE
      pos.iDB = evt.target.result;
      pos.iDB.onerror = evt => {
        alert("Indexed DB upgrade error - " + evt.message);
        console.error(evt);
      };

      // (D4-2) VERSION 1
      if (evt.oldVersion < 1) {
        let store = pos.iDB.createObjectStore(pos.iName, { keyPath: "item_id" });
      }
    };
 
    // (D5) OPEN DATABASE OK
    req.onsuccess = evt => {
      // (D5-1) REGISTER IDB OBJECTS
      pos.iDB = evt.target.result;
      pos.iTX = () => {
        return pos.iDB
        .transaction(pos.iName, "readwrite")
        .objectStore(pos.iName);
      };

      // (D5-2) GET HTML ELEMENTS
      pos.hList = document.getElementById("list");
      pos.hCart = document.getElementById("cart");

      // (D5-3) LAST UPDATED - ITEMS
      pos.updated = localStorage.getItem("POSUpdate");
      if (pos.updated== null) { pos.updated = 0; }

      // (D5-4) CHECK SERVER FOR ITEM UPDATES
      pos.fetch("check", null, ts => {
        if (ts > pos.updated) { pos.update(ts); }
        else { pos.draw(() => { cart.load(); cart.draw(); }); }
      });
    };
  },
 
  // (E) GET & DRAW ITEMS
  draw : after => {
    let req = pos.iTX().getAll();
    req.onsuccess = () => {
      pos.hList.innerHTML = "";
      pos.items = {};
      for (let i of req.result) {
        pos.items[i["item_id"]] = i;
        let item = document.createElement("div");
        item.className = "item";
        item.innerHTML = `<img class="iImg" src="assets/${i["item_image"]}">
        <div class="iName">${i["item_name"]}</div>
        <div class="iPrice">$${i["item_price"]}</div>`;
        item.onclick = () => { cart.add(i["item_id"]); };
        pos.hList.appendChild(item);
      }
      if (after) { after(); }
    };
  }
};
window.onload = pos.init;

Right. This is potential brain damage material, so I am not going to explain line-by-line.

1. I will say that there are 3 main groups of properties for the pos object.
   • iName iDB iTX Indexed database
   • updated items Flags and data.
   • hList hCart HTML elements.
2. pos.fetch() A helper function to do an AJAX fetch call to 3-ajax-pos.php.
3. pos.update() Remember the part where we store a list of items in the browser’s local indexed database? This fetches item data from the server and saves them into the indexed database.
4. pos.init() Runs on window load. Look carefully, it’s pretty much a long-winded indexed database setup.
5. pos.draw() Draws the HTML item.

 

 

4C) POS CART JAVASCRIPT

var cart = {
  // (A) PROPERTIES
  items : {}, // current items in cart
  total : 0, // total amount

  // (B) SAVE CART ITEMS TO LOCALSTORAGE
  save : () => localStorage.setItem("POSCart", JSON.stringify(cart.items)),

  // (C) LOAD CART ITEMS FROM LOCALSTORAGE
  load : () => {
    cart.items = localStorage.getItem("POSCart");
    if (cart.items==null) { cart.items = {}; }
    else { cart.items = JSON.parse(cart.items); }
  },

  // (D) DRAW CART ITEMS
  draw : () => {
    // (D1) RESET TOTAL
    cart.total = 0;
 
    // (D2) CART EMPTY
    if (Object.keys(cart.items).length === 0) {
      pos.hCart.innerHTML = `<div class="cItem">Cart Empty</div>`;
    }
 
    // (D3) DRAW CART
    else {
      // (D3-1) RESET HTML
      pos.hCart.innerHTML = "";
 
      // (D3-2) CART ITEMS
      let item;
      for (let [id, qty] of Object.entries(cart.items)) {
        let itotal = pos.items[id]["item_price"] * qty;
        cart.total += itotal;
        item = document.createElement("div");
        item.className = "crow";
        item.innerHTML = `<div class="cDel" onclick="cart.change(${id}, 0)">X</div>
        <div class="cItem">
          <div class="cName">${pos.items[id]["item_name"]}</div>
          <div class="cPrice">$${itotal.toFixed(2)}</div>
        </div>
        <input type="number" min="0" class="cQty" onchange="cart.change(${id}, this.value)" value="${qty}">`;
        pos.hCart.appendChild(item);
      }

      // (D3-3) TOTAL
      item = document.createElement("div");
      item.className = "cRow";
      item.innerHTML = `<div class="cTotal">Total</div><div class="cAmt">$${cart.total.toFixed(2)}</div>`;
      pos.hCart.appendChild(item);
 
      // (D3-4) EMPTY
      item = document.createElement("input");
      item.type = "button";
      item.value = "Empty";
      item.onclick = cart.empty;
      pos.hCart.appendChild(item);
 
      // (D3-5) CHECKOUT
      item = document.createElement("input");
      item.type = "button";
      item.value = "Checkout";
      item.onclick = cart.checkout;
      pos.hCart.appendChild(item);
    }
  },

  // (E) ADD ITEM TO CART
  add : id => {
    if (cart.items[id]==undefined) { cart.items[id] = 1; }
    else { cart.items[id]++; }
    cart.save();
    cart.draw();
  },

  // (F) CHANGE QUANTITY + REMOVE ITEM FROM CART
  change : (id, qty) => {
    if (qty==0) { delete cart.items[id]; }
    else if (qty!="") { cart.items[id] = parseInt(qty); }
    cart.save();
    cart.draw();
  },

  // (G) EMPTY CART
  empty : () => {
    cart.items = {};
    cart.save();
    cart.draw();
  },

  // (H) CHECKOUT
  checkout : () => {
    // (H1) RESHUFFLE ITEMS
    let items = [];
    for (let [id, qty] of Object.entries(cart.items)) {
      items.push({
        n : pos.items[id]["item_name"],
        p : pos.items[id]["item_price"],
        q : qty
      });
    }

    // (H2) SEND TO SERVER
    pos.fetch("checkout", {
      items : JSON.stringify(items),
      total : cart.total.toFixed(2),
      timestamp : pos.updated
    }, res => {
      // (H3) "LOCAL ITEMS OUTDATED"
      if (isFinite(res)) {
        pos.update(parseInt(res));
      }

      // (H4) OK
      else if (res=="OK") {
        cart.empty();
        alert("OK");
      }
      
      // (H5) NOT OK
      else { alert(res); }
    });
  }
};

More potential brain damage material, quick point form.

1. The shopping cart is fully driven by the browser. Until checkout.
   • items List of items that are in the cart.
   • total The current total amount.
2. cart.save() Save cart.items into localStorage.
3. cart.load() Load cart.items from localStorage.
4. cart.draw() Draw the current cart items in HTML.
5. cart.add() Add item to cart.
6. cart.change() Change the quantity or remove it.
7. cart.empty() Empty the entire cart.
8. cart.checkout() Checkout the current cart items.

 

PART 5) PROGRESSIVE WEB APP

 

5A) HEADER METADATA

<!-- ANDROID + CHROME + APPLE + WINDOWS APP -->
<meta name="mobile-web-app-capable" content="yes">
<meta name="theme-color" content="white">
<link rel="apple-touch-icon" href="assets/icon-512.png">
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="black">
<meta name="apple-mobile-web-app-title" content="PHP POS">
<meta name="msapplication-TileImage" content="assets/icon-512.png">
<meta name="msapplication-TileColor" content="#ffffff">
 
<!-- WEB APP MANIFEST -->
<!-- https://web.dev/add-manifest/ -->
<link rel="manifest" href="5a-manifest.json">
 
<!-- SERVICE WORKER -->
<script>
if ("serviceWorker" in navigator) {
  navigator.serviceWorker.register("5b-worker.js");
}
</script>

We already have a fully functioning app at this point. But to bring it up another level, we can turn it into a progressive web app – An offline-capable and installable web app. To do that we need 3 things.

• Add web app metadata in the header, a pain as everyone has different values and icon sizes. The lazy way is to just provide a huge 512 X 512 icon and let the platforms resize themselves.
• Add a web app manifest.
• Register a service worker.

 

 

5B) WEB MANIFEST

{
  "short_name": "POS",
  "name": "PHP POS",
  "icons": [{
    "src": "assets/favicon.png",
    "sizes": "64x64",
    "type": "image/png"
  }, {
    "src": "assets/icon-512.png",
    "sizes": "512x512",
    "type": "image/png"
  }],
  "start_url": "4a-pos.html",
  "scope": "/",
  "background_color": "white",
  "theme_color": "white",
  "display": "standalone"
}

The manifest file is what it is. Information about the app name, icons, themes, settings, etc…

 

5C) SERVICE WORKER

// (A) CREATE/INSTALL CACHE
self.addEventListener("install", evt => {
  self.skipWaiting();
  evt.waitUntil(
    caches.open("PHPPOS")
    .then(cache => cache.addAll([
      "4a-pos.html",
      "4b-pos.js",
      "4c-cart.js",
      "4d-pos.css",
      "5a-manifest.json",
      "assets/favicon.png",
      "assets/icon-512.png"
    ]))
    .catch(err => console.error(err))
  );
});
 
// (B) CLAIM CONTROL INSTANTLY
self.addEventListener("activate", evt => self.clients.claim());
 
// (C) LOAD FROM CACHE FIRST, FALLBACK TO NETWORK IF NOT FOUND
self.addEventListener("fetch", evt => evt.respondWith(
  caches.match(evt.request).then(res => res || fetch(evt.request))
));

Lastly, if you have not heard of “service worker”, it is simply Javascript that runs in the background. In this one:

• (A) We create a new storage cache and save some of the POS project files.
• (C) Listen to fetch requests. If the requested file is found in the cache, we will use the cached copy. If not, fall back to load from the network.

In other words, making this app work in “offline mode”.

 

 

IT’S INCOMPLETE!

Not-so-intelligent lazy trolls will probably say “this is an incomplete system”! Of course, this is only a barebones tutorial. A lot of work needs to be done if you want to make this “good for production”.

• Small shops can probably get by without a user login system. But if you need a user login and admin panel, see the links below.
• Add more product fields as necessary, and capture staff id in the orders during checkout.
• Connect to online payment processors if you accept payment with the POS system.
• Create your own admin panel and library functions to manage the items.
• Create your own sales reports.
• If you want to better support “decentralized web app”, read up on “Javascript background synchronization API”.
• There will be performance issues when there are too many products. Build your own “products category”.
• Do your own cross origins checks, security, and testing if you want to open up the AJAX endpoint for app development.

Yep, the possibilities are endless. I cannot provide free consultations for everyone, so it’s up to you to complete your own system.

 

BARCODE SCANNERS, PRINTERS, CASH DRAWERS

• Barcode Scanner
  • Just buy a USB barcode scanner, pretty cheap these days.
  • Add a Universal Product Code (UPC) field to the items table.
  • Add a text field to the HTML interface. Manually enter UPC or scan to add an item to the cart.
• Printers & Cash Drawers
  • We can do window.print(), but it will always show a select printer dialog.
  • Probably better to do a server-side print – Use PHP to print the receipt and open the cash drawer on checkout.

 

CATEGORIES

• Add a new categories and products_to_categories table.
• Add new functions to the library to manage categories – Get/add/remove items from categories.
• Update the HTML interface to also include a “filter by category” selector.

 

COMPATIBILITY CHECKS

• Arrow Functions – CanIUse
• Fetch – CanIUse
• Add To Home Screen – Can I Use
• Indexed Database – Can I Use
• Service Worker – Can I Use

This example will not work on ancient browsers.

 

LINKS & REFERENCES

• Javascript POS System – Code Boxx
• Simple PHP Admin Panel – Code Boxx