Simple Billing System In PHP MYSQL (Free Download)

Welcome to a tutorial on how to create a simple billing system in PHP and MYSQL. Need to create and manage bills in your project? Well, a simple billing system really isn’t climbing Mount Everest… Read on for an 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) THE DATABASE

1A) BILLS

-- (A) BILLS
CREATE TABLE `bills` (
  `bill_id` bigint(20) NOT NULL,
  `bill_to` text NOT NULL,
  `bill_ship` text NOT NULL,
  `bill_dop` date NOT NULL DEFAULT current_timestamp(),
  `bill_due` date NOT NULL DEFAULT current_timestamp(),
  `bill_notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `bills`
  ADD PRIMARY KEY (`bill_id`),
  ADD KEY (`bill_dop`),
  ADD KEY (`bill_due`);

ALTER TABLE `bills`
  MODIFY `bill_id` bigint(20) NOT NULL AUTO_INCREMENT;

First, let us address the elephant in the room – We need a bills table to hold the data.

• bill_id Primary key, auto-increment.
• bill_to Bill to.
• bill_ship Ship to.
• bill_dop Date of purchase.
• bill_due Payment due date.
• bill_notes Payment notes, if any.

 

1B) BILL ITEMS

-- (B) BILL ITEMS
CREATE TABLE `bill_items` (
  `bill_id` bigint(20) NOT NULL,
  `item_id` bigint(20) NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `item_desc` varchar(255) DEFAULT NULL,
  `item_qty` bigint(20) NOT NULL,
  `item_each` decimal(12,2) NOT NULL,
  `item_amt` decimal(12,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `bill_items`
  ADD PRIMARY KEY (`bill_id`,`item_id`);

Next, a table to hold the billable items.

• bill_id Composite primary and foreign key.
• item_id Composite primary key.
• item_name Item name.
• item_desc Item description.
• item_qty Item quantity.
• item_each Cost of each item.
• item_amt Total amount (pretty much quantity X each).

 

 

1C) BILL TOTALS

-- (C) BILL TOTALS
CREATE TABLE `bill_totals` (
  `bill_id` bigint(20) NOT NULL,
  `total_id` bigint(20) NOT NULL,
  `total_name` varchar(255) NOT NULL,
  `total_amt` decimal(12,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `bill_totals`
  ADD PRIMARY KEY (`bill_id`,`total_id`);

Finally, a table for the totals.

• bill_id Composite primary and foreign key.
• total_id Composite primary key.
• total_name Total name. E.G. Sub Total, Discount, Grand Total, etc…
• total_amt Total amount. Can be negative for discounts.

 

PART 2) PHP LIBRARY

<?php
class Bill {
  // (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 - EXECUTE SQL QUERY
  function exec ($sql, $data=null) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }

  // (D) SAVE BILL
  function save ($to, $ship, $dop, $due, $notes, $items, $totals, $id=null) {
    // (D1) AUTO-COMMIT OFF
    $this->pdo->beginTransaction();

    // (D2) UPDATE ONLY - DELETE OLD ITEMS & TOTALS
    if ($id!=null) {
      $this->exec("DELETE FROM `bill_items` WHERE `bill_id`=?", [$id]);
      $this->exec("DELETE FROM `bill_totals` WHERE `bill_id`=?", [$id]);
    }

    // (D3) MAIN ENTRY
    if ($id==null) {
      $sql = "INSERT INTO `bills` (`bill_to`, `bill_ship`, `bill_dop`, `bill_due`, `bill_notes`) VALUES (?,?,?,?,?)";
      $data = [$to, $ship, $dop, $due, $notes];
    } else {
      $sql = "UPDATE `bills` SET `bill_to`=?, `bill_ship`=?, `bill_dop`=?, `bill_due`=?, `bill_notes`=? WHERE `bill_id`=?";
      $data = [$to, $ship, $dop, $due, $notes, $id];
    }
    $this->exec($sql, $data);
    if ($id==null) { $id = $this->pdo->lastInsertId(); }

    // (D4) ITEMS
    // (D4-1) ITEMS LIST
    $data = []; $j = 1;
    foreach ($items as $i) {
      array_push($data, $id, $j, $i["n"], isset($i["d"])?$i["d"]:null, $i["q"], $i["e"], $i["a"]);
      $j++;
    }

    // (D4-2) ITEMS SQL
    $sql = "INSERT INTO `bill_items` (`bill_id`, `item_id`, `item_name`, `item_desc`, `item_qty`, `item_each`, `item_amt`) VALUES ";
    $sql .= str_repeat("(?,?,?,?,?,?,?),", $j-1);
    $sql = substr($sql, 0, -1) . ";";

    // (D4-3) INSERT ITEMS
    $this->exec($sql, $data);

    // (D5) TOTALS
    // (D5-1) TOTALS LIST
    $data = []; $j = 1;
    foreach ($totals as $t) {
      array_push($data, $id, $j, $t["n"], $t["a"]);
      $j++;
    }

    // (D5-2) ITEMS SQL
    $sql = "INSERT INTO `bill_totals` (`bill_id`, `total_id`, `total_name`, `total_amt`) VALUES ";
    $sql .= str_repeat("(?,?,?,?),", $j-1);
    $sql = substr($sql, 0, -1) . ";";

    // (D5-3) INSERT TOTALS
    $this->exec($sql, $data);

    // (D6) DONE
    $this->pdo->commit();
    return true;
  }

  // (E) DELETE BILL
  function del ($id) {
    $this->pdo->beginTransaction();
    $this->exec("DELETE FROM `bills` WHERE `bill_id`=?", [$id]);
    $this->exec("DELETE FROM `bill_items` WHERE `bill_id`=?", [$id]);
    $this->exec("DELETE FROM `bill_totals` WHERE `bill_id`=?", [$id]);
    $this->pdo->commit();
    return true;
  }

  // (F) GET ALL BILLS
  function getAll () {
    $this->exec("SELECT * FROM `bills`");
    return $this->stmt->fetchAll();
  }

  // (G) GET BILL
  function get ($id) {
    // (G1) MAIN ENTRY
    $this->exec("SELECT * FROM `bills` WHERE `bill_id`=?", [$id]);
    $bill = $this->stmt->fetch();
    if ($bill===false) { return false; }

    // (G2) ITEMS
    $this->exec("SELECT `item_name`, `item_desc`, `item_qty`, `item_each`, `item_amt` FROM `bill_items` WHERE `bill_id`=?", [$id]);
    $bill["items"] = $this->stmt->fetchAll(PDO::FETCH_NUM);

    // (G3) TOTALS
    $this->exec("SELECT `total_name`, `total_amt` FROM `bill_totals` WHERE `bill_id`=?", [$id]);
    $bill["totals"] = $this->stmt->fetchAll(PDO::FETCH_NUM);

    // (G4) DONE
    return $bill;
  }
}

// (H) SETTINGS - CHANGE THESE TO YOUR OWN !
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (I) DATABASE OBJECT
$_BILL = new Bill();

With the database in place, the next step is to create a library to work with it. This looks complicated, but is actually very straightforward:

• (A, B, I) When $_BILL = new Bill() is created, the constructor connects to the database. The destructor closes the connection.
• (C) exec() A helper function to run an SQL query.
• (D To G) The “actual” library functions, pretty much a collection of SQL.
  • save() Add or update a bill.
  • del() Delete a bill.
  • getAll() Get all bills.
  • get() Get a specified bill.
• (H) Remember to change the database settings to your own.

 

 

PART 3) PRINT INVOICE PAGE

<?php
// (A) GET BILL
if (!isset($_GET["id"])) { exit("Invalid bill"); }
require "2-lib.php";
$bill = $_BILL->get($_GET["id"]);
if ($bill===false) { exit("Invalid bill"); }

// (B) GENERATE INVOICE
require "invlib/invoicr.php";
$invoicr->set("head", [
	["Invoice #", $bill["bill_id"]],
	["DOP", $bill["bill_dop"]],
	["Due Date", $bill["bill_due"]]
]);
$invoicr->set("billto", preg_split("/\r\n|\r|\n/", $bill["bill_to"]));
$invoicr->set("shipto", preg_split("/\r\n|\r|\n/", $bill["bill_ship"]));
$invoicr->set("items", $bill["items"]);
$invoicr->set("totals", $bill["totals"]);
$invoicr->set("notes", preg_split("/\r\n|\r|\n/", $bill["bill_notes"]));

// (C) CHOOSE A TEMPLATE
 $invoicr->template("apple");
// $invoicr->template("banana");
// $invoicr->template("blueberry");
// $invoicr->template("lime");
// $invoicr->template("simple");
// $invoicr->template("strawberry");

// (D) OUTPUT
// (D1) OUTPUT IN HTML
$invoicr->outputHTML(); // display in browser
// $invoicr->outputHTML(1); // display in browser
// $invoicr->outputHTML(2, "invoice.html"); // force download
// $invoicr->outputHTML(3, "invoice.html"); // save to file on server

// (D2) OUTPUT IN PDF
// $invoicr->outputPDF(); // display in browser
// $invoicr->outputPDF(1); // display in browser
// $invoicr->outputPDF(2, "invoice.pdf"); // force download
// $invoicr->outputPDF(3, "invoice.pdf"); // save to file on server

// (D3) OUTPUT IN DOCX
// $invoicr->outputDOCX(); // display in browser
// $invoicr->outputDOCX(1, "invoice.docx"); // force download
// $invoicr->outputDOCX(2, "invoice.docx"); // save to file on server

That’s right. With this page, we already have a fully functioning system without an HTML interface. For example, we can manually create bills using the library:

<?php
require "2-lib.php";
$_BILL->save(
  "JON DOE\r\nBILL TO ADDRESS", "JOY DOE\r\nSHIP TO ADDRESS",
  date("Y-m-d"), date("Y-m-d", strtotime("+7 days")), "NOTES",
  [
    ["n"=>"Foo Bar", "d"=>"Foolish Bar", "q"=>10, "e"=>1.2, "a"=>12],
    ["n"=>"Goo Bar", "q"=>4, "e"=>2.5, "a"=>10]
  ],
  [
    ["n" => "Sub Total", "a"=>"22"],
    ["n" => "Discount", "a"=>"-2"],
    ["n" => "Grand Total", "a"=>"20"]
  ]
);

Then, access http://site.com/3-print.php?id=ABC to generate the invoice.

 

 

PART 4) AJAX HANDLER

<?php
if (isset($_POST["req"])) {
  require "2-lib.php";
  switch($_POST["req"]) {
    // (A) SAVE BILL
    case "save":
      echo $_BILL->save(
        $_POST["to"], $_POST["ship"], $_POST["dop"], $_POST["due"], $_POST["notes"],
        json_decode($_POST["items"], 1), json_decode($_POST["totals"], 1),
        isset($_POST["id"]) ? $_POST["id"] : null
      ) ? "OK" : "ERROR" ;
      break;

    // (B) DELETE BILL
    case "del":
      echo $_BILL->del($_POST["id"]) ? "OK" : "ERROR" ;
      break;

    // (C) GET ALL BILLS
    case "getAll":
      echo json_encode($_BILL->getAll());
      break;

    // (D) GET BILL
    case "get":
      echo json_encode($_BILL->get($_POST["id"]));
      break;
}}

But for the sake of completeness, we will complete the system. Here, we have an AJAX handler. Very simply, the HTML/Javascript will send a request followed by the required parameters to run a library function. For example, send $_POST["req"]="del" and $_POST["id"]=123 to delete bill ID 123.

 

PART 5) DUMMY ADMIN PAGE

<!-- (A) BILLS LIST -->
<div id="blWrap" class="section">
  <div id="blAdd" onclick="bill.toggle(0)">&#10010;</div>
  <div id="blList"></div>
</div>
 
<!-- (B) BILL FORM -->
<div id="bfWrap" class="hide">
  <!-- (B1) TITLE -->
  <h2 id="bfTitle">ADD BILL</h2>
 
  <!-- (B2) BILLING INFO -->
  <h4>BILLING INFORMATION</h4>
  <form id="bfForm" class="section">
    <input type="hidden" id="bfID">
    <label>Bill To</label>
    <textarea id="bfBill" required></textarea>
    <label>Ship To</label>
    <textarea id="bfShip" required></textarea>
    <label>Notes (if any)</label>
    <textarea id="bfNotes"></textarea>
    <label>DOP</label>
    <input type="date" id="bfDOP" required>
    <label>Due Date</label>
    <input type="date" id="bfDue" required>
  </form>
 
  <!-- (B2) ITEMS -->
  <h4>ITEMS</h4>
  <div id="bfiWrap" class="section">
    <div id="bfiList"></div>
    <form id="bfiForm" onsubmit="return bill.aRow(1)">
      <input type="text" id="bfiName" placeholder="Name" required>
      <input type="text" id="bfiDesc" placeholder="Desc">
      <input type="number" id="bfiQty" placeholder="Qty" min="0" step="0.1" required>
      <input type="number" id="bfiEach" placeholder="Price Each" min="0" step="0.01" required>
      <input type="submit" id="bfiAdd" value="Add">
    </form>
  </div>
 
  <!-- (B3) TOTALS -->
  <h4>TOTALS</h4>
  <div id="bftWrap" class="section">
    <div class="bRow flex">
      <div class="bfInfo bftTitle">Sub Total</div>
      <div class="bftAmt">$<span id="bftSub">0.00</span></div>
    </div>
    <div id="bftList"></div>
    <div class="bRow flex">
      <div class="bfInfo bftTitle">Grand Total</div>
      <div class="bftAmt">$<span id="bftGrand">0.00</span></div>
    </div>
    <form id="bftForm" class="flex" onsubmit="return bill.aRow(2)">
      <input type="text" id="bftName" placeholder="Name" required>
      <input type="number" id="bftAmt" placeholder="Amount (negative for discounts)" step="0.01" required>
      <input type="submit" id="bfiAdd" value="Add">
    </form>
  </div>
 
  <input type="button" value="Back" onclick="bill.toggle(false)">
  <input type="button" value="Save" onclick="bill.save()">
</div>

Lastly, we have a “2 pages in 1” dummy admin page.

1. List of bills.
2. Add/edit bill form.

Basically, a heavily AJAX-driven page. We load data from 4-ajax.php and generate the HTML list/form using Javascript. Not going to explain the Javascript and CSS here, they are brain damage material… Go through those in your own free time. Or feel free to discard this page entirely, and rebuild using your own favorite library.

 

 

 

COMPLETE YOUR OWN SYSTEM

Before the trolls go on their usual looney tunes, this is ultimately a tutorial only. You have to complete your own system:

• If you don’t already have a user login system, see the links below.
• Protect the library functions, and limit access to administrators only.
• Do your own pagination, so the bills list doesn’t “overflow”.
• Add your own search feature. Index the “bill to” and “ship to” fields if you want.
• Redo the HTML admin page if you want.
• Add your own features to manage the bills – Add a “bill status”, and track the payment.
• Add your own feature to send the invoice via email, or allow customers to view online.
• Maybe add your own online payment.

Yep, the possibilities are endless. It is up to you to complete your own system now.

 

COMPATIBILITY CHECKS

• Arrow Functions – CanIUse
• Fetch – CanIUse

Will work on all modern “Grade A” browsers.

 

LINKS & REFERENCES

• Simple User Login System With PHP MYSQL – Code Boxx
• Simple Pagination With PHP MYSQL – Code Boxx
• Invoicr PHP Invoice Generator – Code Boxx