Simple Support Ticket System With PHP MySQL

Welcome to a tutorial on how to create a simple ticket system with PHP and MySQL. Want to make your own system for support tickets? Only to find complicated ones or paid packages? Well, creating a simple ticket system yourself really isn’t that bad. Read on for the 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 Ticket System Useful Bits & Links
The End

 

DOWNLOAD & NOTES

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

 

QUICK NOTES

  • Download and unzip into your HTTP folder.
  • Create a test database and import 1-database.sql.
  • Edit 2-lib-ticket.php, change the database settings to your own.
  • Access 3a-ticket.html in the browser. Captain Obvious, use http://, not file://.
  • The default dummy user is a… user. Edit 4-ajax.php if you want to switch to an admin user.
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.

 

 

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.

 

 

PHP MYSQL TICKET SYSTEM

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

 

PART 1) THE DATABASE

1A) TICKETS

1-database.sql
-- (A) TICKETS
CREATE TABLE `tickets` (
  `ticket_id` bigint(20) NOT NULL,
  `ticket_user` bigint(20) NOT NULL,
  `ticket_status` int(11) NOT NULL DEFAULT 0,
  `ticket_date` datetime NOT NULL DEFAULT current_timestamp(),
  `ticket_txt` text CHARACTER SET utf8mb4 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ALTER TABLE `tickets`
  ADD PRIMARY KEY (`ticket_id`),
  ADD KEY `ticket_status` (`ticket_status`),
  ADD KEY `ticket_user` (`ticket_user`),
  ADD KEY `ticket_date` (`ticket_date`);
 
ALTER TABLE `tickets`
  MODIFY `ticket_id` bigint(20) NOT NULL AUTO_INCREMENT;

First, let us start with the database. The tickets table should be pretty self-explanatory.

  • ticket_id Primary key and auto-increment.
  • ticket_user User ID who opened the ticket.
  • ticket_status Current ticket status… Up to you to decide. More on that later.
  • ticket_date Date and time when the ticket is opened.
  • ticket_txt Whatever request the user wants.

 

1B) TICKETS HISTORY

1-database.sql
-- (B) TICKET HISTORY
CREATE TABLE `ticket_history` (
  `ticket_id` bigint(20) NOT NULL,
  `history_date` datetime NOT NULL DEFAULT current_timestamp(),
  `ticket_status` int(11) NOT NULL DEFAULT 0,
  `history_staff` bigint(20) NOT NULL,
  `history_note` text CHARACTER SET utf8mb4 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
ALTER TABLE `ticket_history`
  ADD PRIMARY KEY (`ticket_id`,`history_date`),
  ADD KEY `ticket_user` (`history_staff`),
  ADD KEY `ticket_status` (`ticket_status`);

Next, we have a table to store the history of the support ticket.

  • ticket_id Composite primary and foreign key.
  • history_date Date when the ticket is updated.
  • ticket_status The new ticket status.
  • history_staff User ID. Staff that updated the ticket.
  • history_note Notes, if any.

 

 

PART 2) PHP LIBRARY

2-lib-ticket.php
<?php
class Ticket {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = null;
  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 FUNCTION - RUN SQL QUERY
  function query ($sql, $data=null) {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }

  // (D) GET ALL TICKETS
  function getAll ($uid=null) {
    $sql = "SELECT * FROM `tickets`";
    $data = null;
    if (is_numeric($uid)) {
      $sql .= " WHERE `ticket_user`=?";
      $data = [$uid];
    }
    $this->query($sql, $data);
    return $this->stmt->fetchAll();
  }

  // (E) GET TICKET
  function get ($tid) {
    $this->query(
      "SELECT * FROM `tickets` WHERE `ticket_id`=?", [$tid]
    );
    return $this->stmt->fetch();
  }

  // (F) GET TICKET HISTORY
  function getHistory ($tid) {
    $this->query(
      "SELECT * FROM `ticket_history`
       WHERE `ticket_id`=?
       ORDER BY `history_date` DESC", [$tid]
    );
    return $this->stmt->fetchAll();
  }

  // (G) ADD NEW TICKET
  function save ($uid, $txt) {
    $this->query(
      "INSERT INTO `tickets` (`ticket_user`, `ticket_txt`) VALUES (?,?)",
      [$uid, $txt]
    );
    return true;
  }

  // (H) UPDATE & ADD TICKET HISTORY
  function update ($tid, $status, $staff, $note) {
    // (H1) UPDATE TICKET
    $this->query(
      "UPDATE `tickets` SET `ticket_status`=? WHERE `ticket_id`=?",
      [$status, $tid]
    );

    // (H2) APPEND HISTORY
    $this->query(
      "INSERT INTO `ticket_history` (`ticket_id`, `ticket_status`, `history_staff`, `history_note`) VALUES (?,?,?,?)",
      [$tid, $status, $staff, $note]
    );
    return true;
  }
}

// (I) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (J) TICKET STATUS CODES
define("TKT_STAT", ["Pending", "Processing", "Done", "Canceled"]);

// (K) NEW TICKET OBJECT
$_TKT = new Ticket();

This looks scary, but keep calm and look carefully.

  • (A & B) When $_TKT = new Ticket() is created, the constructor will connect to the database. The destructor closes the connection.
  • (C) query() A helper function to run an SQL query.
  • (D To H) The “actual” ticket functions.
    • getAll() Get all tickets.
    • get() Get the specified ticket.
    • getHistory() Get the history of the ticket.
    • save() Create a new ticket.
    • update() Update a ticket and add to the history of the ticket.
  • (I) Database settings – Change to your own.
  • (J) The ticket status codes. This is currently set to a simple pending, processing, done. Feel free to add more of your own as required – Maybe “canceled”, “procuring hardware”, “escalated”, etc…
  • (K) Don’t think this needs explanation.

 

 

PART 3) TICKET PAGE

3A) THE HTML

3a-ticket.html
<!-- (A) TICKET LIST -->
<div id="pgA">
  <input type="button" value="New Ticket" onclick="tix.show()"/>
  <div id="list"></div>
</div>
 
<!-- (B) TICKET DETAILS -->
<div id="pgB" class="ninja"></div>

Very simple, this is pretty much “2 pages in 1”.

  • pgA List of tickets.
  • pgB When the user/admin clicks on a ticket – Add/Show/Edit the ticket.

Yep, the rest is AJAX-driven.

 

3B) THE JAVASCRIPT

3b-ticket.js
var tix = {
  // (A) INIT
  ha : null, hb : null, // html page a & b
  hlist : null, // html tickets list
  init : () => {
    tix.ha = document.getElementById("pgA");
    tix.hb = document.getElementById("pgB");
    tix.hlist = document.getElementById("list");
    tix.list();
  },

  // (B) HELPER FUNCTION FOR AJAX FETCH
  fetch : (data, loaded) => {
    // (B1) FORM DATA
    let form;
    if (data instanceof FormData) { form = data; }
    else {
      form = new FormData;
      for (let [k, v] of Object.entries(data)) { form.append(k, v); }
    }

    // (B2) FETCH!
    fetch("4-ajax.php", {
      method: "post",
      body: form
    })
    .then(res => res.text())
    .then(txt => { loaded(txt); });
  },

  // (C) HELPER - TOGGLE PAGES
  toggle : (pg) => {
    if (pg=="A") {
      tix.hb.classList.add("ninja");
      tix.ha.classList.remove("ninja");
    } else {
      tix.ha.classList.add("ninja");
      tix.hb.classList.remove("ninja");
    }
  },

  // (D) LIST TICKETS
  list : () => {
    tix.fetch(
      {"req" : "list"},
      (txt) => {
        tix.hlist.innerHTML = txt;
        tix.toggle("A");
      }
    );
  },

  // (E) SHOW TICKET
  show : (tid) => {
    tix.fetch(
      {
        "req" : "show",
        "tid" : tid ? tid : null
      },
      (txt) => {
        tix.hb.innerHTML = txt;
        tix.toggle("B");
      }
    );
  },

  // (F) SAVE TICKET
  save : () => {
    let data = new FormData(document.getElementById("form"));
    data.append("req", "save");
    tix.fetch(data, (res) => {
      if (res=="OK") {
        alert("Ticket saved");
        tix.list();
      } else { alert(res); }
    });
    return false;
  }
};

window.onload = tix.init;

Right, not going to explain this line-by-line. But the Javascript pretty much deals with the HTML interface.

  • (A) tix.init() Runs on page load. Gets all the HTML elements, and shows the list of tickets.
  • (B & C) Helper functions.
    • tix.fetch() Does an AJAX fetch.
    • tix.toggle() Toggles between <div id="pgA"> and <div id="pgB">.
  • (D to F) HTML interface “drivers”.
    • tix.list() Loads the list of tickets into <div id="list">.
    • tix.show() Shows the selected ticket, AJAX load into <div id="pgB">.
    • tix.save() Add/update a ticket.

 

 

PART 4) AJAX HANDLER

4A) INITIALIZE

4-ajax.php
<?php
// (A) LOAD LIBRARY
require "2-lib-ticket.php";
 
// (B) DEMO USER - TIE IN WITH YOUR OWN SYSTEM!
/* USER */
$user = [
  "id" => 9,
  "role" => "U"
];
 
/* ADMIN *
$user = [
"id" => 99,
"role" => "A"
]; */
 
$isadm = $user["role"] == "A";
 
// (C) HANDLE REQUESTS
if ($_POST["req"]) { switch ($_POST["req"]) {
  // ...
}

Lastly, all the AJAX requests from the HTML page will be handled by this script. The first half of it is pretty much just “initialization”.

  1. Load the ticket library. Captain Obvious at your service.
  2. For this demo, we only have a dummy user and admin. Tie this into your own user system, or create a new one – Links below.
  3. The Javascript sends a $_POST["req"] to this script, and we handle the various requests accordingly.

 

4B) REQUEST HANDLERS

4-ajax.php
// (C0) INVALID
default: echo "Invalid request"; break;
 
 // (C1) LIST TICKETS
case "list":
  $tickets = $_TKT->getAll($isadm?null:$user["id"]);
  if (count($tickets)==0) { echo "<div>No tickets found.</div>"; }
  else { foreach ($tickets as $t) { printf("
  <div class='row'>
    <div class='left'>
      <div>%s | %s</div>
      <div>%s</div>
    </div>
    <div class='right'>
      <input type='button' value='View' onclick='tix.show(%u)'/>
    </div>
  </div>",
    $t["ticket_date"], TKT_STAT[$t["ticket_status"]],
    $t["ticket_txt"],
    $t["ticket_id"]);
  }}
  break;
 
// (C2) ADD/VIEW/UPDATE TICKET
case "show":
  // (C2-1) "MODE"
  // 1 - ADD | 2 - UPDATE | 3 - VIEW
  if (is_numeric($_POST["tid"])) { $mode = $isadm ? 2 : 3 ; }
  else { $mode = 1; }
 
  // (C2-2) VIEW OR UPDATE - GET TICKET
  if ($mode == 2 || $mode == 3) {
    $t = $_TKT->get($_POST["tid"]);
    $h = $_TKT->getHistory($_POST["tid"]);
    if (!is_array($t)) { exit("Invalid Ticket"); }
    if (!$isadm && $t["ticket_user"]!=$user["id"]) { exit("Invalid Ticket"); }
  }
 
  // (C2-3) TICKET FORM ?>
  <h1>
    <?=$mode==1 ? "ADD" : ($mode==2 ? "UPDATE" : "VIEW")?> TICKET
  </h1>
  <form id="form" onsubmit="return tix.save()">
    <!-- TICKET FORM -->
    <div class="section">
      <input type="hidden" name="ticket_id" value="<?=isset($t) ? $t["ticket_id"] : ""?>"/>
      <input type="hidden" name="ticket_user" value="<?=isset($t) ? $t["ticket_user"] : ""?>"/>
 
      <label>Request</label>
      <input type="text" name="ticket_txt" required
        <?=$mode==1 ? "" : " disabled"?>
        <?=isset($t) ? " value='".$t['ticket_txt']."'" : ""?>/>
 
      <?php if ($mode!=1) { ?>
      <label>Created</label>
      <input type="text" name="ticket_date" disabled value="<?=$t["ticket_date"]?>"/>
 
      <label>Status</label>
      <select name="ticket_status"<?=$mode==3?" disabled":""?>><?php
        foreach (TKT_STAT as $k=>$v) {
          printf("<option value='%u'%s>%s</option>",
          $k, $k==$t["ticket_status"]?" selected":"", $v
        );
      }
      ?></select>

      <?php if ($mode==2) { ?>
      <label>Notes</label>
      <input type="text" name="history_note"/>
      <?php } ?>
      <?php } ?>
    </div>
 
    <!-- TICKET HISTORY -->
    <?php if (isset($h) && count($h)>0) { ?>
    <div class="section"><?php
      foreach ($h as $i) {
        printf("<div>[%s] %s | %s</div>",
          $i["history_date"], TKT_STAT[$i["ticket_status"]], $i["history_note"]
        );
      }
    ?></div>
    <?php } ?>
 
    <!-- TICKET BUTTONS -->
    <div class="section">
      <?php if ($mode!=3) { ?>
      <input type="submit" value="Save"/>
      <?php } ?>
      <input type="button" value="Back" onclick="tix.toggle('A')"/>
    </div>
  </form>
  <?php break;
 
// (C3) SAVE
case "save":
  if (is_numeric($_POST["ticket_id"])) {
    $_TKT->update($_POST["ticket_id"], $_POST["ticket_status"], $user["id"], $_POST["history_note"]);
  } else {
    $_TKT->save($user["id"], $_POST["ticket_txt"]);
  }
  echo "OK";
  break;
}}

Finally, not going to explain this line-by-line once again. But this is practically the “missing HTML” – Get the list of tickets from the database, and display them in HTML. Show the “add/view/update” ticket form. Save the submitted ticket.

 

 

USEFUL 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.

 

BAREBONES ONLY

Before the trolls start banging on the keyboard – Of course, this is a barebones system and starting point only. Plenty of stuff still needs to be done to make this a “worthy system”.

  • Integrate with your existing user system, or create a new one (links below).
  • A nice “ticket request” page, and “admin panel”.
  • Complete your own “ticket handling process”. Pending, processing, completed, dropped?
  • Reports – KPI, response rate, percentage of tickets closed, etc…
  • Security – Login, user roles, access permissions, etc…

 

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.