Simple Support Ticket System In PHP MySQL (Free Download)

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!

Download & Notes

PHP Ticket System

Extras

The End

DOWNLOAD & NOTES

Here is the download link to the example code, so you don’t have to copy-paste everything.

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

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) USERS

1-database.sql

-- (A) USERS
CREATE TABLE `users` (
  `user_id` bigint(20) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `user_email` varchar(255) NOT NULL,
  `user_level` varchar(1) NOT NULL DEFAULT 'U'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `user_email` (`user_email`),
  ADD KEY `user_level` (`user_level`);

ALTER TABLE `users`
  MODIFY `user_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

INSERT INTO `users` (`user_id`, `user_name`, `user_email`, `user_level`) VALUES
(1, 'Admin Doe', 'admin@doe.com', 'A'),
(2, 'User Doe', 'user@doe.com', 'U');

You should have your own users table, this is just a dummy one to better illustrate this example – There are only 2 users here, an administrator and a user.

1B) TICKETS

1-database.sql

-- (B) TICKETS
CREATE TABLE `tickets` (
  `ticket_id` bigint(20) NOT NULL,
  `ticket_date` datetime NOT NULL DEFAULT current_timestamp(),
  `ticket_status` int(11) NOT NULL DEFAULT 0,
  `ticket_subject` varchar(255) NOT NULL,
  `ticket_txt` text NOT NULL,
  `user_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `tickets`
  ADD PRIMARY KEY (`ticket_id`),
  ADD KEY `ticket_status` (`ticket_status`),
  ADD KEY `ticket_date` (`ticket_date`),
  ADD KEY `user_id` (`user_id`);

ALTER TABLE `tickets`
  MODIFY `ticket_id` bigint(20) NOT NULL AUTO_INCREMENT;

This ticket table should be pretty self-explanatory.

ticket_id Primary key and auto-increment.
ticket_date Date and time when the ticket is opened.
ticket_status Current ticket status… Up to you to decide. More on that later.
ticket_subject The “title” of the ticket.
ticket_txt Whatever request the user wants.
user_id The user ID, foreign key.

1C) TICKETS HISTORY

1-database.sql

-- (C) TICKET HISTORY
CREATE TABLE `ticket_history` (
  `ticket_id` bigint(20) NOT NULL,
  `history_date` datetime NOT NULL DEFAULT current_timestamp(),
  `history_status` int(11) NOT NULL DEFAULT 0,
  `history_note` text DEFAULT NULL,
  `user_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `ticket_history`
  ADD PRIMARY KEY (`ticket_id`,`history_date`),
  ADD KEY `history_status` (`history_status`),
  ADD KEY `user_id` (`user_id`);

Lastly, 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_note Notes, if any.
user_id Foreign key. The staff that updated the ticket.

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 () {
    $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 - RUN SQL QUERY
  function query ($sql, $data=null) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }

  // (D) GET ALL TICKETS
  function getAll ($uid=null) {
    $sql = 
    "SELECT t.`ticket_id`, t.`ticket_date`, t.`ticket_status`, t.`ticket_subject`,
     u.`user_id`, u.`user_name`, u.`user_email`
     FROM `tickets` t JOIN `users` u USING (`user_id`)";
    $data = null;
    if ($uid != null) {
      $sql .= " WHERE t.`user_id`=?";
      $data = [$uid];
    }
    $this->query($sql, $data);
    return $this->stmt->fetchAll();
  }

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

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

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

  // (H) UPDATE & ADD TICKET HISTORY
  function update ($status, $tid, $note, $uid) {
    // (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`, `history_status`, `history_note`, `user_id`) VALUES (?,?,?,?)",
      [$tid, $status, $note, $uid]
    );
    return true;
  }
}

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

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

// (K) NEW TICKET OBJECT
$_TKT = new Ticket();
 
// (L) CURRENT USER
// * DUMMY ONLY. TIE THIS INTO YOUR OWN USER SYSTEM.
// $_USR = ["id" => 2, "level" => "U"];
$_USR = ["id" => 1, "level" => "A"];
$_ADM = $_USR["level"]=="A";

This looks scary at first, but keep calm and look carefully.

(A, B, K) 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.
- add() 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 new, processing, done. Feel free to add more of your own as required – Maybe “canceled”, “procuring hardware”, “escalated”, etc…
(L) We masquerade between the administrator and user here. Users can only create and view their own tickets, while administrators can access all tickets.

PART 3) TICKET PAGE

3A) THE HTML

3a-ticket.html

<!-- (A) TICKET LIST -->
<div id="pgA">
  <!-- (A1) ADD TICKET -->
  <div id="tAdd" class="row" onclick="tix.show()">
    New Ticket
  </div>
 
  <!-- (A2) TICKET LIST -->
  <div id="tList"></div>
</div>
 
<!-- (B) TICKET FORM -->
<div id="pgB" class="hide"></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/update/view the ticket.

Yep, the rest is AJAX-driven.

3B) THE JAVASCRIPT

3b-ticket.js

var tix = {
  // (A) INIT
  hPga : null, // html page a
  hPgb : null, // html page b
  hList : null, // html ticket list
  init : () => {
    tix.hPga = document.getElementById("pgA");
    tix.hPgb = document.getElementById("pgB");
    tix.hList = document.getElementById("tList");
    tix.list();
  },
 
  // (B) HELPER - AJAX FETCH
  fetch : (data, loaded) => {
    // (B1) FORM DATA
    let 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))
    .catch(err => console.error(err));
  },
 
  // (C) HELPER - TOGGLE HTML PAGES
  toggle : pg => {
    if (pg=="A") {
      tix.hPgb.classList.add("hide");
      tix.hPga.classList.remove("hide");
    } else {
      tix.hPga.classList.add("hide");
      tix.hPgb.classList.remove("hide");
    }
  },
 
  // (D) LIST TICKETS
  list : () => tix.fetch(
    {
      req : "getAll",
      search : tix.find
    },
    txt => {
      tix.hList.innerHTML = txt;
      tix.toggle("A");
    }
  ),
 
  // (E) SHOW TICKET
  show : tid => tix.fetch(
    {
      req : "show",
      tid : tid ? tid : null
    },
    txt => {
      tix.hPgb.innerHTML = txt;
      tix.toggle("B");
    }
  ),
 
  // (F) SAVE TICKET
  save : () => {
    // (F1) FORM DATA
    let data = {
      req : "save",
      subject : document.getElementById("tSubject").value,
      txt : document.getElementById("tTxt").value,
      tid : document.getElementById("tID").value
    };
    if (data.tid=="") { delete data.tid; }
    else {
      data.status = document.getElementById("tStat").value;
      data.note = document.getElementById("tNote").value;
    }
  
    // (F2) AJAX FETCH
    tix.fetch(data, res => {
      if (res=="OK") { 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 to 4-ajax.php.
- 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="tList">.
- tix.show() Shows the selected ticket, AJAX load into <div id="pgB">.
- tix.save() Add/update a ticket.

PART 4) AJAX HANDLER

4-ajax.php

<?php
if (isset($_POST["req"])) {
  require "2-lib-ticket.php";
  switch ($_POST["req"]) {
    // (A) LIST TICKETS
    case "getAll":
      $tickets = $_TKT->getAll($_ADM ? null : $_USR["id"]);
      if (count($tickets)>0) { foreach ($tickets as $t) { ?>
      <div class="row">
        <div class="grow">
            <span class="tStat s<?=$t["ticket_status"]?>"><?=TKT_STAT[$t["ticket_status"]]?></span>
            <div class="tUser"><?=$t["user_name"]?> &#x2022; <?=$t["ticket_date"]?></div>
            <div class="tTxt"><?=$t["ticket_subject"]?></div>
        </div>
        <div class="tShow" onclick="tix.show(<?=$t["ticket_id"]?>)">&#x27A4;</div>
      </div>
      <?php }} else { echo "No tickets found."; }
      break;
 
    // (B) ADD/UPDATE/VIEW TICKET
    case "show":
      // (B1) SET "PAGE MODE"
      // 1 - ADD | 2 - UPDATE | 3 - VIEW
      if (is_numeric($_POST["tid"])) { $mode = $_ADM ? 2 : 3 ; }
      else { $mode = 1; }
 
      // (B2) UPDATE OR VIEW - 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 (!$_ADM && $t["user_id"]!=$_USR["id"]) { exit("Invalid Ticket"); }
      }
 
      // (B3) TICKET FORM ?>
      <form class="section" onsubmit="return tix.save()">
        <h2><?=$mode==1?"ADD":($mode==2?"UPDATE":"VIEW")?> TICKET</h2>
        <input type="hidden" id="tID" value="<?=isset($t)?$t["ticket_id"]:""?>">
 
        <?php if ($mode!=1) { ?>
        <label>Created At</label>
        <input type="text" readonly value="<?=$t["ticket_date"]?>">
 
        <label>Created By</label>
        <input type="text" readonly value="<?=$t["user_name"]?>">
        <?php } ?>
 
        <label>Subject</label>
        <input type="text" id="tSubject" required<?=$mode==1?"":" readonly"?>
               value="<?=isset($t)?$t["ticket_subject"]:""?>">
 
        <label>Details</label>
        <textarea id="tTxt" required<?=$mode==1?"":" readonly"?>><?=isset($t)?$t["ticket_txt"]:""?></textarea>
 
        <?php if ($mode==2 || $mode==3) { ?>
        <label>Status</label>
        <select id="tStat"<?=$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 } ?>
 
        <?php if ($mode==2) { ?>
        <label>Update Notes (If Any)</label>
        <input type="text" id="tNote">
        <?php } ?>
 
        <input type="button" value="Back" onclick="tix.toggle('A')">
        <?php if ($mode!=3) { ?>
        <input type="submit" value="Save">
        <?php } ?>
      </form>
 
      <?php
      // (B4) TICKET HISTORY 
      if ($mode==2 && count($h)>0) { ?>
      <div id="tHistory" class="section">
        <h2>TICKET HISTORY</h2>
        <?php foreach ($h as $i) { ?>
        <div class="hRow">
          <div class="hDate">[<?=$i["history_date"]?>] <?=TKT_STAT[$i["history_status"]]?></div>
          <div class="hNote"><?=$i["history_note"]?></div>
        </div>
        <?php } ?>
      </div>
      <?php }
      break;
 
    // (C) SAVE TICKET
    case "save":
      if (isset($_POST["tid"])) {
        $_TKT->update($_POST["status"], $_POST["tid"], $_POST["note"], $_USR["id"]);
      } else {
        $_TKT->add($_POST["subject"], $_POST["txt"], $_USR["id"]);
      }
      echo "OK";
      break;
}}

Lastly, the Javascript sends a $_POST["req"] to this script, and we handle the various requests accordingly.

getAll Shows the list of tickets.
show Show the add/update/view ticket form.
save Save a submitted ticket.

EXTRAS

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

BAREBONES SYSTEM 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?
Proper pagination.
Reports – KPI, response rate, percentage of tickets closed, etc…
Security – Login, user roles, access permissions, etc…

LINKS & REFERENCES

Simple PHP MySQL Login – Code Boxx
Simple PHP User Role – Code Boxx
PHP Admin Panel – Code Boxx

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!

Bill D

November 6, 2022 at 2:17 am

The new interface appearance sucks in my opinion. The original was perfect. A quick slam-n-go interface. I’m struggling to interface with the simple login example though. I’m also looking at the user roles example for clues. Ultimately I want a user to log in, redirect to their group tickets maybe? The admin user can see status of all tickets.

PS: I have that blue/magenta smoke background (saw on your U-tube video on my Pinephone SXMO. Thanks for all you do!

W.S. Toh
November 6, 2022 at 3:49 am

When in doubt, divide and conquer.

1) Add a simple login system.
2) Add session checks to protect the ticket pages.
3) Add session checks to protect the AJAX handler.
4) Factor user ID into the ticket system.
5) Add a “group id” to the user table. Redirect the users and get the tickets accordingly.
6) Feel free to rebuild the entire HTML interface – Bootstrap, React, Vue, Angular, jQuery, or native HTML/CSS/JS. Up to you.

Good luck.

Bill

September 11, 2022 at 6:26 pm

I added a status item named “closed”.

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

// (J) TICKET STATUS CODES
define("TKT_STAT", ["Pending", "Processing", "Closed", "Enroute", "Onsite", "Customer delay"]);

PS: This is so awsome on mobile device!

Simple Support Ticket System In PHP MySQL (Free Download)

TABLE OF CONTENTS

DOWNLOAD & NOTES

EXAMPLE CODE DOWNLOAD

SORRY FOR THE ADS...

PHP MYSQL TICKET SYSTEM

PART 1) THE DATABASE

1A) USERS

1B) TICKETS

1C) TICKETS HISTORY

PART 2) PHP LIBRARY

PART 3) TICKET PAGE

3A) THE HTML

3B) THE JAVASCRIPT

PART 4) AJAX HANDLER

EXTRAS

BAREBONES SYSTEM ONLY

LINKS & REFERENCES

THE END

3 thoughts on “Simple Support Ticket System In PHP MySQL (Free Download)”