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!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Create a database and import 1-database.sql.
  • Change the database settings in 2-lib.php to your own.
  • Edit invlib/invoicr.php, set the company information to your own (C1).
  • If you want to generate DOCX or PDF invoices, PHPWord and MPDF are required but not included in the zip file.
    • Install Composer.
    • Open the command line and navigate to cd YOUR-PROJECT-FOLDER/invlib.
    • Run composer require phpoffice/phpword and composer require mpdf/mpdf.
    • Set the theme and output mode in 3-print.php.
  • That’s all – Launch 5a-admin.html in the browser.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

SCREENSHOT

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

BILLING SYSTEM WITH PHP MYSQL

All right, let us now get into the details of building a billing system with PHP and MYSQL.

 

PART 1) THE DATABASE

1A) BILLS

1-database.sql
-- (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

1-database.sql
-- (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

1-database.sql
-- (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

2-lib.php
<?php
class Bill {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct() { try {
    $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
    ]);
  } catch (Exception $ex) { exit($ex->getMessage()); }}

  // (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) {
    $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", "utf8");
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

3-print.php
<?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

4-ajax.php
<?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

5a-admin.html
<!-- (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.

 

 

EXTRA BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

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

Will work on all modern “Grade A” browsers.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *