Pure PHP Point of sale system with MySQL

A few days ago I did an exercise on a sales system in PHP. It is written in pure PHP, no Javascript. Mind you, for the styles I used a Bootstrap variant.

The files don’t have a structure, but like I said, it’s an example. I also write this because it either serves someone else or it serves myself for some references.

For data persistence it uses MySQL. This POS Save products and sales. It does not handle user permissions. We store the shopping cart in the session, and well, I better explain it in parts in this post.

Obviously this POS software is free and open source.

Test finished system

Preview

This is what the finished system looks like:

On-line

To give you a little idea, you can visit this link to test the system. In the same way, watch it on YouTube:

Download

If you like to download it and try it in your local environment, here below I leave it:

https://github.com/parzibyte/ventas_pdo/archive/master.zip

Sales system in PHP

Database

Here I leave the SQL scheme that was used. Also keep some records so you have something to work with at the start.

DROP DATABASE IF EXISTS ventas;
CREATE DATABASE IF NOT EXISTS ventas;
USE ventas;
CREATE TABLE productos(
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 codigo VARCHAR(255) NOT NULL,
 descripcion VARCHAR(255) NOT NULL,
 precioVenta DECIMAL(5, 2) NOT NULL,
 precioCompra DECIMAL(5, 2) NOT NULL,
 existencia DECIMAL(5, 2) NOT NULL,
 PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

CREATE TABLE ventas(
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 fecha DATETIME NOT NULL,
 total DECIMAL(7,2),
 PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

CREATE TABLE productos_vendidos(
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 id_producto BIGINT UNSIGNED NOT NULL,
 cantidad BIGINT UNSIGNED NOT NULL,
 id_venta BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(id),
 FOREIGN KEY(id_producto) REFERENCES productos(id) ON DELETE CASCADE,
 FOREIGN KEY(id_venta) REFERENCES ventas(id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

INSERT INTO productos(id, codigo, descripcion, precioVenta, precioCompra, existencia) 
VALUES
(1, '1', 'Galletas chokis', 15, 10, 100),
(2, '2', 'Mermelada de fresa', 80, 65, 100),
(3, '3', 'Aceite', 20, 18, 100),
(4, '4', 'Palomitas de maíz', 15, 12, 100),
(5, '5', 'Doritos', 8, 5, 100);

# Correcto

And to connect to the MariaDB/MySQL database we use PDO. The code to achieve this is as follows:

<?php

$contraseña = "1d3ed423r43crt34";
$usuario = "root";
$nombre_base_de_datos = "ventas";
try{
 $base_de_datos = new PDO('mysql:host=localhost;dbname=' . $nombre_base_de_datos, $usuario, $contraseña);
  $base_de_datos->query("set names utf8;");
    $base_de_datos->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
    $base_de_datos->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $base_de_datos->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
}catch(Exception $e){
 echo "Ocurrió algo con la base de datos: " . $e->getMessage();
}
?>

Remember to change the user, password and database name.

Product management

Add product

It is a simple form that asks for the information. It only asks for the barcode, the description, the sale price, the purchase price and the existence.

<?php include_once "encabezado.php" ?>

<div class="col-xs-12">
 <h1>Nuevo producto</h1>
 <form method="post" action="nuevo.php">
  <label for="codigo">Código de barras:</label>
  <input class="form-control" name="codigo" required type="text" id="codigo" placeholder="Escribe el código">

  <label for="descripcion">Descripción:</label>
  <textarea required id="descripcion" name="descripcion" cols="30" rows="5" class="form-control"></textarea>

  <label for="precioVenta">Precio de venta:</label>
  <input class="form-control" name="precioVenta" required type="number" id="precioVenta" placeholder="Precio de venta">

  <label for="precioCompra">Precio de compra:</label>
  <input class="form-control" name="precioCompra" required type="number" id="precioCompra" placeholder="Precio de compra">

  <label for="existencia">Existencia:</label>
  <input class="form-control" name="existencia" required type="number" id="existencia" placeholder="Cantidad o existencia">
  <br><br><input class="btn btn-info" type="submit" value="Guardar">
 </form>
</div>
<?php include_once "pie.php" ?>

We are including some files which are the header and footer. These files will be explained below.

When submitting the form, the data is received in a file called nuevo.php. This file has the following code:

<?php include_once "encabezado.php" ?>
<?php
#Salir si alguno de los datos no está presente
if(!isset($_POST["codigo"]) || !isset($_POST["descripcion"]) || !isset($_POST["precioVenta"]) || !isset($_POST["precioCompra"]) || !isset($_POST["existencia"])) exit();

#Si todo va bien, se ejecuta esta parte del código...

include_once "base_de_datos.php";
$codigo = $_POST["codigo"];
$descripcion = $_POST["descripcion"];
$precioVenta = $_POST["precioVenta"];
$precioCompra = $_POST["precioCompra"];
$existencia = $_POST["existencia"];

$sentencia = $base_de_datos->prepare("INSERT INTO productos(codigo, descripcion, precioVenta, precioCompra, existencia) VALUES (?, ?, ?, ?, ?);");
$resultado = $sentencia->execute([$codigo, $descripcion, $precioVenta, $precioCompra, $existencia]);

if($resultado === TRUE){
 header("Location: ./listar.php");
 exit;
}
else echo "Algo salió mal. Por favor verifica que la tabla exista";


?>
<?php include_once "pie.php" ?>

As you can see, we are not doing any validation and we are inserting the data as it comes from the form. In case everything goes well, we redirect to Listar.php so that it gives the illusion that the product was saved right there and was added to the table.

List products

Speaking of listing products, here’s the code that draws the table:

<?php include_once "encabezado.php" ?>
<?php
include_once "base_de_datos.php";
$sentencia = $base_de_datos->query("SELECT * FROM productos;");
$productos = $sentencia->fetchAll(PDO::FETCH_OBJ);
?>

 <div class="col-xs-12">
  <h1>Productos</h1>
  <div>
   <a class="btn btn-success" href="./formulario.php">Nuevo <i class="fa fa-plus"></i></a>
  </div>
  <br>
  <table class="table table-bordered">
   <thead>
    <tr>
     <th>ID</th>
     <th>Código</th>
     <th>Descripción</th>
     <th>Precio de compra</th>
     <th>Precio de venta</th>
     <th>Existencia</th>
     <th>Editar</th>
     <th>Eliminar</th>
    </tr>
   </thead>
   <tbody>
    <?php foreach($productos as $producto){ ?>
    <tr>
     <td><?php echo $producto->id ?></td>
     <td><?php echo $producto->codigo ?></td>
     <td><?php echo $producto->descripcion ?></td>
     <td><?php echo $producto->precioCompra ?></td>
     <td><?php echo $producto->precioVenta ?></td>
     <td><?php echo $producto->existencia ?></td>
     <td><a class="btn btn-warning" href="<?php echo "editar.php?id=" . $producto->id?>"><i class="fa fa-edit"></i></a></td>
     <td><a class="btn btn-danger" href="<?php echo "eliminar.php?id=" . $producto->id?>"><i class="fa fa-trash"></i></a></td>
    </tr>
    <?php } ?>
   </tbody>
  </table>
 </div>
<?php include_once "pie.php" ?>

Right there we include the buttons that are used to edit and delete the product, which are nothing more than links to another page. By the way, we also put a button to add a new product; It is also a link that leads to the form.

Edit product

When we click on the edit button, it redirects us to another file. In it, we read the id using $_GET. Then, we make a query to the database where the id is the one we read.

If the product does not exist, we indicate it. In case it exists, then we draw the same form but now we fill it with the value attribute of the input tag. For the textarea it is different, since the content must go between the tags, not as an attribute.

<?php
if(!isset($_GET["id"])) exit();
$id = $_GET["id"];
include_once "base_de_datos.php";
$sentencia = $base_de_datos->prepare("SELECT * FROM productos WHERE id = ?;");
$sentencia->execute([$id]);
$producto = $sentencia->fetch(PDO::FETCH_OBJ);
if($producto === FALSE){
 echo "¡No existe algún producto con ese ID!";
 exit();
}

?>
<?php include_once "encabezado.php" ?>
 <div class="col-xs-12">
  <h1>Editar producto con el ID <?php echo $producto->id; ?></h1>
  <form method="post" action="guardarDatosEditados.php">
   <input type="hidden" name="id" value="<?php echo $producto->id; ?>">
 
   <label for="codigo">Código de barras:</label>
   <input value="<?php echo $producto->codigo ?>" class="form-control" name="codigo" required type="text" id="codigo" placeholder="Escribe el código">

   <label for="descripcion">Descripción:</label>
   <textarea required id="descripcion" name="descripcion" cols="30" rows="5" class="form-control"><?php echo $producto->descripcion ?></textarea>

   <label for="precioVenta">Precio de venta:</label>
   <input value="<?php echo $producto->precioVenta ?>" class="form-control" name="precioVenta" required type="number" id="precioVenta" placeholder="Precio de venta">

   <label for="precioCompra">Precio de compra:</label>
   <input value="<?php echo $producto->precioCompra ?>" class="form-control" name="precioCompra" required type="number" id="precioCompra" placeholder="Precio de compra">

   <label for="existencia">Existencia:</label>
   <input value="<?php echo $producto->existencia ?>" class="form-control" name="existencia" required type="number" id="existencia" placeholder="Cantidad o existencia">
   <br><br><input class="btn btn-info" type="submit" value="Guardar">
   <a class="btn btn-warning" href="./listar.php">Cancelar</a>
  </form>
 </div>
<?php include_once "pie.php" ?>

Please note that to know the id that we are editing (with which we will do the where) I am saving it in a hidden input.

There are two buttons. One of them submits the form; the other is simply a link to Listar.php. The point of all this is to provide the user with navigation buttons.

Save Changes

Above we saw the form to edit. Now let’s look at the file where we actually save the changes. Is this:

<?php

#Salir si alguno de los datos no está presente
if(
 !isset($_POST["codigo"]) || 
 !isset($_POST["descripcion"]) || 
 !isset($_POST["precioCompra"]) || 
 !isset($_POST["precioVenta"]) || 
 !isset($_POST["existencia"]) || 
 !isset($_POST["id"])
) exit();

#Si todo va bien, se ejecuta esta parte del código...

include_once "base_de_datos.php";
$id = $_POST["id"];
$codigo = $_POST["codigo"];
$descripcion = $_POST["descripcion"];
$precioCompra = $_POST["precioCompra"];
$precioVenta = $_POST["precioVenta"];
$existencia = $_POST["existencia"];

$sentencia = $base_de_datos->prepare("UPDATE productos SET codigo = ?, descripcion = ?, precioCompra = ?, precioVenta = ?, existencia = ? WHERE id = ?;");
$resultado = $sentencia->execute([$codigo, $descripcion, $precioCompra, $precioVenta, $existencia, $id]);

if($resultado === TRUE){
 header("Location: ./listar.php");
 exit;
}
else echo "Algo salió mal. Por favor verifica que la tabla exista, así como el ID del producto";
?>

It is Very similar to the form for inserting a new one, but this one edits. If everything goes well, it takes us to Listar.php that will be in charge of displaying the products, with the changes that have been made.

Remove product

Finally let’s look at the delete one. Please note that it does not ask for confirmation, so you have to do it carefully.

<?php
if(!isset($_GET["id"])) exit();
$id = $_GET["id"];
include_once "base_de_datos.php";
$sentencia = $base_de_datos->prepare("DELETE FROM productos WHERE id = ?;");
$resultado = $sentencia->execute([$id]);
if($resultado === TRUE){
 header("Location: ./listar.php");
 exit;
}
else echo "Algo salió mal";
?>

And this way we finish the product management (CRUD) in this POS.

Making sells

This was the part that I liked the most. It only works with arrays and sessions, but I liked the result.

Selling interface

<?php 
include_once "encabezado.php";
session_start();
if(!isset($_SESSION["carrito"])) $_SESSION["carrito"] = [];
$granTotal = 0;
?>
 <div class="col-xs-12">
  <h1>Vender</h1>
  <?php
   if(isset($_GET["status"])){
    if($_GET["status"] === "1"){
     ?>
      <div class="alert alert-success">
       <strong>¡Correcto!</strong> Venta realizada correctamente
      </div>
     <?php
    }else if($_GET["status"] === "2"){
     ?>
     <div class="alert alert-info">
       <strong>Venta cancelada</strong>
      </div>
     <?php
    }else if($_GET["status"] === "3"){
     ?>
     <div class="alert alert-info">
       <strong>Ok</strong> Producto quitado de la lista
      </div>
     <?php
    }else if($_GET["status"] === "4"){
     ?>
     <div class="alert alert-warning">
       <strong>Error:</strong> El producto que buscas no existe
      </div>
     <?php
    }else if($_GET["status"] === "5"){
     ?>
     <div class="alert alert-danger">
       <strong>Error: </strong>El producto está agotado
      </div>
     <?php
    }else{
     ?>
     <div class="alert alert-danger">
       <strong>Error:</strong> Algo salió mal mientras se realizaba la venta
      </div>
     <?php
    }
   }
  ?>
  <br>
  <form method="post" action="agregarAlCarrito.php">
   <label for="codigo">Código de barras:</label>
   <input autocomplete="off" autofocus class="form-control" name="codigo" required type="text" id="codigo" placeholder="Escribe el código">
  </form>
  <br><br>
  <table class="table table-bordered">
   <thead>
    <tr>
     <th>ID</th>
     <th>Código</th>
     <th>Descripción</th>
     <th>Precio de venta</th>
     <th>Cantidad</th>
     <th>Total</th>
     <th>Quitar</th>
    </tr>
   </thead>
   <tbody>
    <?php foreach($_SESSION["carrito"] as $indice => $producto){ 
      $granTotal += $producto->total;
     ?>
    <tr>
     <td><?php echo $producto->id ?></td>
     <td><?php echo $producto->codigo ?></td>
     <td><?php echo $producto->descripcion ?></td>
     <td><?php echo $producto->precioVenta ?></td>
     <td><?php echo $producto->cantidad ?></td>
     <td><?php echo $producto->total ?></td>
     <td><a class="btn btn-danger" href="<?php echo "quitarDelCarrito.php?indice=" . $indice?>"><i class="fa fa-trash"></i></a></td>
    </tr>
    <?php } ?>
   </tbody>
  </table>

  <h3>Total: <?php echo $granTotal; ?></h3>
  <form action="./terminarVenta.php" method="POST">
   <input name="total" type="hidden" value="<?php echo $granTotal;?>">
   <button type="submit" class="btn btn-success">Terminar venta</button>
   <a href="./cancelarVenta.php" class="btn btn-danger">Cancelar venta</a>
  </form>
 </div>
<?php include_once "pie.php" ?>

The interface Shows a table, which will be of the products that make up the sale. It also shows the total, which at the beginning is 0. And at the bottom 2 buttons that are to finish selling or to cancel the sale. There are also some ifs that show an alert like “Non-existent product” or things like that.

Note that it has an input, that is to read the barcode. It is actually an input within a form. When this form is sent, it goes to the file that we will see below.

Finally, if we look at the header, we will notice that it declares the cart index in the superglobal array of $_SESSION. That is where we will place the products 😉

Add to shopping cart

I repeat that this is the part that I liked the most. We start by checking the existence of the product, if it does not exist, we return to vender.php and we pass the status 5 that will say that there is no longer stock of the product.

In case the product does not exist (that is, it does not exist, not that its existence is 0) we send status 4.

<?php
if(!isset($_POST["codigo"])) return;
$codigo = $_POST["codigo"];
include_once "base_de_datos.php";
$sentencia = $base_de_datos->prepare("SELECT * FROM productos WHERE codigo = ? LIMIT 1;");
$sentencia->execute([$codigo]);
$producto = $sentencia->fetch(PDO::FETCH_OBJ);
if($producto){
 if($producto->existencia < 1){
  header("Location: ./vender.php?status=5");
  exit;
 }
 session_start();
 $indice = false;
 for ($i=0; $i < count($_SESSION["carrito"]); $i++) { 
  if($_SESSION["carrito"][$i]->codigo === $codigo){
   $indice = $i;
   break;
  }
 }
 if($indice === FALSE){
  $producto->cantidad = 1;
  $producto->total = $producto->precioVenta;
  array_push($_SESSION["carrito"], $producto);
 }else{
  $_SESSION["carrito"][$indice]->cantidad++;
  $_SESSION["carrito"][$indice]->total = $_SESSION["carrito"][$indice]->cantidad * $_SESSION["carrito"][$indice]->precioVenta;
 }
 header("Location: ./vender.php");
}else header("Location: ./vender.php?status=4");
?>

But assuming that everything is fine and that the product exists, we log in and go through the cart array to see if we had already added that product before. In case we have added it, then we only change the amount.

That is, if “Strawberry jam” already exists and they reread the code that belongs to that product, we will not show both products in the table. Better we show one and indicate that its quantity is 2, and so on.

If it does not exist in the cart, then we add it to the array and put its quantity, which by default is 1. Also, by default, its total will be the sale price. It is not necessary to do multiplication.

Then, if it already exists, we calculate its total, which is the result of multiplying its sale price by the quantity in the cart.

Remove from shopping cart

Now let’s see how to remove products from the cart. We simply remove the element that belongs to the index that we receive in $_GET. The code is very easy to understand:

<?php
if(!isset($_GET["indice"])) return;
$indice = $_GET["indice"];

session_start();
array_splice($_SESSION["carrito"], $indice, 1);
header("Location: ./vender.php?status=3");
?>

Oh, and we go back to vender.php with status 3 which I think says was successfully removed.

Finishing sale

It is a simple file that will insert into the database the products sold, as well as the sale, its date and its total. It looks like this:

<?php
if(!isset($_POST["total"])) exit;


session_start();


$total = $_POST["total"];
include_once "base_de_datos.php";


$ahora = date("Y-m-d H:i:s");


$sentencia = $base_de_datos->prepare("INSERT INTO ventas(fecha, total) VALUES (?, ?);");
$sentencia->execute([$ahora, $total]);

$sentencia = $base_de_datos->prepare("SELECT id FROM ventas ORDER BY id DESC LIMIT 1;");
$sentencia->execute();
$resultado = $sentencia->fetch(PDO::FETCH_OBJ);

$idVenta = $resultado === false ? 1 : $resultado->id;

$base_de_datos->beginTransaction();
$sentencia = $base_de_datos->prepare("INSERT INTO productos_vendidos(id_producto, id_venta, cantidad) VALUES (?, ?, ?);");
$sentenciaExistencia = $base_de_datos->prepare("UPDATE productos SET existencia = existencia - ? WHERE id = ?;");
foreach ($_SESSION["carrito"] as $producto) {
 $total += $producto->total;
 $sentencia->execute([$producto->id, $idVenta, $producto->cantidad]);
 $sentenciaExistencia->execute([$producto->cantidad, $producto->id]);
}
$base_de_datos->commit();
unset($_SESSION["carrito"]);
$_SESSION["carrito"] = [];
header("Location: ./vender.php?status=1");
?>

We subtract stock of products, we take the server time and save the sale. Then we clean the cart.

If you ask me for beginTransaction and commit, it is to (although it is not noticeable) speed up the process. It’s like making a lot of changes to the database but not saving them until we’re done. More information in Low performance in statements prepared with PHP and MySQL.

By the way, we go back to listing with a status anyway.

Cancel sale

To cancel the sale we simply empty the array and that’s it. We return to listing with a status.

<?php

session_start();

unset($_SESSION["carrito"]);
$_SESSION["carrito"] = [];

header("Location: ./vender.php?status=2");
?>

And below I show you a picture when you cancel the sale:

Sales

To finish this great tutorial let’s look at the sales report. It consists of two things: listing sales and being able to delete them.

List sales – Sales report

Please do not blame me, but I do not know how (and if someone does, explain to me) to make a query which result contains an array. The thing is, I wanted something to show the products sold for sale. The only thing I could come up with was a group_concat. Anyway, the code looks like this:

<?php include_once "encabezado.php" ?>
<?php
include_once "base_de_datos.php";
$sentencia = $base_de_datos->query("SELECT ventas.total, ventas.fecha, ventas.id, GROUP_CONCAT( productos.codigo, '..',  productos.descripcion, '..', productos_vendidos.cantidad SEPARATOR '__') AS productos FROM ventas INNER JOIN productos_vendidos ON productos_vendidos.id_venta = ventas.id INNER JOIN productos ON productos.id = productos_vendidos.id_producto GROUP BY ventas.id ORDER BY ventas.id;");
$ventas = $sentencia->fetchAll(PDO::FETCH_OBJ);
?>

 <div class="col-xs-12">
  <h1>Ventas</h1>
  <div>
   <a class="btn btn-success" href="./vender.php">Nueva <i class="fa fa-plus"></i></a>
  </div>
  <br>
  <table class="table table-bordered">
   <thead>
    <tr>
     <th>Número</th>
     <th>Fecha</th>
     <th>Productos vendidos</th>
     <th>Total</th>
     <th>Eliminar</th>
    </tr>
   </thead>
   <tbody>
    <?php foreach($ventas as $venta){ ?>
    <tr>
     <td><?php echo $venta->id ?></td>
     <td><?php echo $venta->fecha ?></td>
     <td>
      <table class="table table-bordered">
       <thead>
        <tr>
         <th>Código</th>
         <th>Descripción</th>
         <th>Cantidad</th>
        </tr>
       </thead>
       <tbody>
        <?php foreach(explode("__", $venta->productos) as $productosConcatenados){ 
        $producto = explode("..", $productosConcatenados)
        ?>
        <tr>
         <td><?php echo $producto[0] ?></td>
         <td><?php echo $producto[1] ?></td>
         <td><?php echo $producto[2] ?></td>
        </tr>
        <?php } ?>
       </tbody>
      </table>
     </td>
     <td><?php echo $venta->total ?></td>
     <td><a class="btn btn-danger" href="<?php echo "eliminarVenta.php?id=" . $venta->id?>"><i class="fa fa-trash"></i></a></td>
    </tr>
    <?php } ?>
   </tbody>
  </table>
 </div>
<?php include_once "pie.php" ?>

In this way, we concatenate it in the query but “deconcatenate” it in PHP. And there is a nice result.

Delete sale

To delete a sale is like deleting a product. Note that it does not ask for confirmation either. The code looks like this:

<?php
if(!isset($_GET["id"])) exit();
$id = $_GET["id"];
include_once "base_de_datos.php";
$sentencia = $base_de_datos->prepare("DELETE FROM ventas WHERE id = ?;");
$resultado = $sentencia->execute([$id]);
if($resultado === TRUE){
 header("Location: ./ventas.php");
 exit;
}
else echo "Algo salió mal";
?>

Header and footer

As promised, here is the header and footer. They only define the container, the navigation menu, some meta tags and load the css libraries.

Header is:

<!DOCTYPE html>
<html lang="es">
<head>
 <meta charset="UTF-8">
 <title>Ventas</title>
 
 <link rel="stylesheet" href="./css/fontawesome-all.min.css">
 <link rel="stylesheet" href="./css/2.css">
 <link rel="stylesheet" href="./css/estilo.css">
</head>
<body>
 <nav class="navbar navbar-inverse navbar-fixed-top">
  <div class="container">
   <div class="navbar-header">
    <a class="navbar-brand" href="#">POS</a>
   </div>
   <div id="navbar" class="collapse navbar-collapse">
    <ul class="nav navbar-nav">
     <li><a href="./listar.php">Productos</a></li>
     <li><a href="./vender.php">Vender</a></li>
     <li><a href="./ventas.php">Ventas</a></li>
    </ul>
   </div>
  </div>
 </nav>
 <div class="container">
  <div class="row">

And footer is:

  </div>
 </div>
</body>
</html>

Conclusion

Like I said, it has no structure. It was done quickly, but it works and that’s what counts.

By the way, if you want to see something similar to this system but in Laravel, check out this post.