Build Helpdesk System with jQuery, PHP & MySQL

Helpdesk Systems or Support Ticket Systems are commonly used systems in companies to help their customers to resolve their queries and issues. The Helpdesk Systems are used by both support teams and customers to add tickets, reply to tickets and resolve issues or queries. It allow customers to add ticket with issue details and the support replies to that ticket with solutions and details.

So if you’re thinking about developing web based Helpdesk Ticketing System with PHP, then you’re here at right place. In our previous tutorial, you have learned how create User Management System with PHP & MySQL, In this tutorial, you will learn how to develop Helpdesk Ticketing System with PHP and MySQL.

We will cover this tutorial step by step with live example of Helpdesk system to create ticket, list tickets, edit ticket, close ticket, reply to ticket, view ticket with replies etc..

Also, read:

So let’s start implementing Helpdesk Ticketing System with PHP and MySQL. Before we begin, take a look on files structure for this example.

index.php
ticket.php
ajax.js
process.php
Users.php: A class to hold user method.
Tickets.php: A class to hold ticket method.

Step1: Create MySQL Database Table

We will create MySQL database tables to build Helpdesk system. We will create hd_users table to store user login details.

CREATE TABLE `hd_users` (
  `id` int(11) NOT NULL,
  `email` varchar(250) NOT NULL,
  `password` varchar(250) NOT NULL,
  `sign_up_date` varchar(250) NOT NULL,
  `nick_name` varchar(250) NOT NULL,
  `user_group` int(11) NOT NULL,
  `last_login` varchar(250) NOT NULL,
  `url` varchar(270) NOT NULL,
  `allowed` int(11) NOT NULL,
  `most_recent_ip` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will create hd_departments table to store help team department details.

CREATE TABLE `hd_departments` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `hidden` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will create hd_tickets table to store ticket details.

CREATE TABLE `hd_tickets` (
  `id` int(11) NOT NULL,
  `uniqid` varchar(20) NOT NULL,
  `user` int(11) NOT NULL,
  `title` varchar(250) NOT NULL,
  `init_msg` text NOT NULL,
  `department` int(11) NOT NULL,
  `date` varchar(250) NOT NULL,
  `last_reply` int(11) NOT NULL,
  `user_read` int(11) NOT NULL,
  `admin_read` int(11) NOT NULL,
  `resolved` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will create hd_ticket_replies table to store ticket replies details.

CREATE TABLE `hd_ticket_replies` (
  `id` int(11) NOT NULL,
  `user` int(11) NOT NULL,
  `text` text NOT NULL,
  `ticket_id` text NOT NULL,
  `date` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step2: Create Tickets Dashboard

First we will create dashboard to display ticket listing with edit, close and view options.

View and manage tickets that may have responses from support team.

S/N	Ticket ID	Subject	Department	Created By	Created	Status

In ajax.js file, we will make ajax request to process.php with action listTicket to load ticket list with details.

var ticketData = $('#listTickets').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,
	"order":[],
	"ajax":{
		url:"process.php",
		type:"POST",
		data:{action:'listTicket'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0, 6, 7, 8, 9],
			"orderable":false,
		},
	],
	"pageLength": 10
});

In process.php, we will call ticket method showTickets() to get ticket details.

if(!empty($_POST['action']) && $_POST['action'] == 'listTicket') {
	$tickets->showTickets();
}

In class Tickets.php, we will create method showTickets() which return ticket details and JSON data.

public function showTickets(){
	$sqlWhere="";	
	if(!isset($_SESSION["admin"])) {
		$sqlWhere .= " WHERE t.user="".$_SESSION["userid"]."" ";
		if(!empty($_POST["search"]["value"])){
			$sqlWhere .= " and ";
		}
	} else if(isset($_SESSION["admin"]) && !empty($_POST["search"]["value"])) {
		$sqlWhere .= " WHERE ";
	} 		
	$time = new time;  			 
	$sqlQuery = "SELECT t.id, t.uniqid, t.title, t.init_msg as message, t.date, t.last_reply, t.resolved, u.nick_name as creater, d.name as department, u.user_group, t.user, t.user_read, t.admin_read
		FROM hd_tickets t 
		LEFT JOIN hd_users u ON t.user = u.id 
		LEFT JOIN hd_departments d ON t.department = d.id $sqlWhere ";
	if(!empty($_POST["search"]["value"])){
		$sqlQuery .= ' (uniqid LIKE "%'.$_POST["search"]["value"].'%" ';					
		$sqlQuery .= ' OR title LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR resolved LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR last_reply LIKE "%'.$_POST["search"]["value"].'%") ';			
	}
	if(!empty($_POST["order"])){
		$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
	} else {
		$sqlQuery .= 'ORDER BY t.id DESC ';
	}
	if($_POST["length"] != -1){
		$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
	}	
	$result = mysqli_query($this->dbConnect, $sqlQuery);
	$numRows = mysqli_num_rows($result);
	$ticketData = array();	
	while( $ticket = mysqli_fetch_assoc($result) ) {		
		$ticketRows = array();			
		$status="";
		if($ticket['resolved'] == 0)	{
			$status="Open";
		} else if($ticket['resolved'] == 1) {
			$status="Closed";
		}	
		$title = $ticket['title'];
		if((isset($_SESSION["admin"]) && !$ticket['admin_read'] && $ticket['last_reply'] != $_SESSION["userid"]) || (!isset($_SESSION["admin"]) && !$ticket['user_read'] && $ticket['last_reply'] != $ticket['user'])) {
			$title = $this->getRepliedTitle($ticket['title']);			
		}
		$disbaled = '';
		if(!isset($_SESSION["admin"])) {
			$disbaled = 'disabled';
		}			
		$ticketRows[] = $ticket['id'];
		$ticketRows[] = $ticket['uniqid'];
		$ticketRows[] = $title;
		$ticketRows[] = $ticket['department'];
		$ticketRows[] = $ticket['creater']; 			
		$ticketRows[] = $time->ago($ticket['date']);
		$ticketRows[] = $status;
		$ticketRows[] = 'View Ticket';	
		$ticketRows[] = '';
		$ticketRows[] = '';
		$ticketData[] = $ticketRows;
	}
	$output = array(
		"draw"				=>	intval($_POST["draw"]),
		"recordsTotal"  	=>  $numRows,
		"recordsFiltered" 	=> 	$numRows,
		"data"    			=> 	$ticketData
	);
	echo json_encode($output);
}

Step3: Create Ticket

We will create design of ticket create modal in add_ticket_modal.php and include in files where ticket create modal required.

In ajax.php, we will make ajax request to process.php to save ticket details with action createTicket.

$(document).on('submit','#ticketForm', function(event){
	event.preventDefault();
	$('#save').attr('disabled','disabled');
	var formData = $(this).serialize();
	$.ajax({
		url:"process.php",
		method:"POST",
		data:formData,
		success:function(data){				
			$('#ticketForm')[0].reset();
			$('#ticketModal').modal('hide');				
			$('#save').attr('disabled', false);
			ticketData.ajax.reload();
		}
	})
});

In process.php, we will call ticket method createTicket() to save ticket details.

if(!empty($_POST['action']) && $_POST['action'] == 'createTicket') {
	$tickets->createTicket();
}

In class Tickets.php, we will create method createTicket() which save ticket details to MySQL database table.

public function createTicket() {      
	if(!empty($_POST['subject']) && !empty($_POST['message'])) {                
		$date = new DateTime();
		$date = $date->getTimestamp();
		$uniqid = uniqid();                
		$message = strip_tags($_POST['subject']);              
		$queryInsert = "INSERT INTO ".$this->ticketTable." (uniqid, user, title, init_msg, department, date, last_reply, user_read, admin_read, resolved) 
		VALUES('".$uniqid."', '".$_SESSION["userid"]."', '".$_POST['subject']."', '".$message."', '".$_POST['department']."', '".$date."', '".$_SESSION["userid"]."', 0, 0, '".$_POST['status']."')";			
		mysqli_query($this->dbConnect, $queryInsert);			
		echo 'success ' . $uniqid;
	} else {
		echo 'Please fill in all fields.';
	}
}

Step4: Edit Ticket

We will make ajax request to process.php with action getTicketDetails load ticket details edit ticket form.

$(document).on('click', '.update', function(){
	var ticketId = $(this).attr("id");
	var action = 'getTicketDetails';
	$.ajax({
		url:'process.php',
		method:"POST",
		data:{ticketId:ticketId, action:action},
		dataType:"json",
		success:function(data){
			$('#ticketModal').modal('show');
			$('#ticketId').val(data.id);
			$('#subject').val(data.title);
			$('#message').val(data.init_msg);
			if(data.gender == '0') {
				$('#open').prop("checked", true);
			} else if(data.gender == '1') {
				$('#close').prop("checked", true);
			}
			$('.modal-title').html(" Edit Ticket");
			$('#action').val('updateTicket');
			$('#save').val('Save Ticket');
		}
	})
});

In process.php, we will call ticket method getTicketDetails() to get ticket details to fill in edit form.

if(!empty($_POST['action']) && $_POST['action'] == 'getTicketDetails') {
	$tickets->getTicketDetails();
}

In class Tickets.php, we will create method getTicketDetails() to get ticket details from MySQL database table.

public function getTicketDetails(){
	if($_POST['ticketId']) {	
		$sqlQuery = "
			SELECT * FROM ".$this->ticketTable." 
			WHERE id = '".$_POST["ticketId"]."'";
		$result = mysqli_query($this->dbConnect, $sqlQuery);	
		$row = mysqli_fetch_array($result, MYSQL_ASSOC);
		echo json_encode($row);
	}
}

Step5: View Ticket with Replies

In ticket.php, we will create design to view ticket with replies.

          
	            
		
			
				
				
				
				
				
				
				
				
										
					
					
					
					
					                 
				
				
					  ago($ticketDetails['date']); ?>
					   
					   
				
						 
		
				
			
		
			
				
					
													
							 
						
							 
						
						    ago($replies['date']); ?>

We will call ticket methods to get ticket details and ticket replies.

$ticketDetails = $tickets->ticketInfo($_GET['id']);
$ticketReplies = $tickets->getTicketReplies($ticketDetails['id']);

In class Tickets.php, we will create method getTicketReplies() to get ticket replies details from MySQL database table.

public function getTicketReplies($id) {  		
	$sqlQuery = "SELECT r.id, r.text as message, r.date, u.nick_name as creater, d.name as department, u.user_group  
		FROM ".$this->ticketRepliesTable." r
		LEFT JOIN ".$this->ticketTable." t ON r.ticket_id = t.id
		LEFT JOIN hd_users u ON r.user = u.id 
		LEFT JOIN hd_departments d ON t.department = d.id 
		WHERE r.ticket_id = '".$id."'";	
	$result = mysqli_query($this->dbConnect, $sqlQuery);
	$data= array();
	while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
		$data[]=$row;            
	}
	return $data;
}

Step6: Make Ticket Reply

We will create design of ticket reply form in ticket.php.

We will handle ticket reply form submit and make ajax request to process.php with action saveTicketReplies.

$(document).on('submit','#ticketReply', function(event){
	event.preventDefault();
	$('#reply').attr('disabled','disabled');
	var formData = $(this).serialize();
	$.ajax({
		url:"process.php",
		method:"POST",
		data:formData,
		success:function(data){				
			$('#ticketReply')[0].reset();
			$('#reply').attr('disabled', false);
			location.reload();
		}
	})
});

In process.php, we will call ticket method saveTicketReplies() to save ticket replies.

if(!empty($_POST['action']) && $_POST['action'] == 'saveTicketReplies') {
	$tickets->saveTicketReplies();
}

In class Tickets.php, we will create method saveTicketReplies() to save ticket replies into MySQL database table.

public function saveTicketReplies () {
	if($_POST['message']) {
		$date = new DateTime();
		$date = $date->getTimestamp();
		$queryInsert = "INSERT INTO ".$this->ticketRepliesTable." (user, text, ticket_id, date) 
			VALUES('".$_SESSION["userid"]."', '".$_POST['message']."', '".$_POST['ticketId']."', '".$date."')";
		mysqli_query($this->dbConnect, $queryInsert);				
		$updateTicket = "UPDATE ".$this->ticketTable." 
			SET last_reply = '".$_SESSION["userid"]."', user_read = '0', admin_read = '0' 
			WHERE id = '".$_POST['ticketId']."'";				
		mysqli_query($this->dbConnect, $updateTicket);
	} 
}

We have also handled other functionalities related to tickets, customers and supports. To get all files, you can download complete project code to enhance it to implement and use according to your requirement.

You may also like:

You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download

Credit goes to the respective owner!

Build Helpdesk System with jQuery, PHP & MySQL | WD

Bydinesh

Step1: Create MySQL Database Table

Step2: Create Tickets Dashboard

Step3: Create Ticket

Step4: Edit Ticket

Step5: View Ticket with Replies

Step6: Make Ticket Reply

Like this:

Related Post

Reading Comprehension Breakdowns are Often Knowledge-Problems Disguised As Skill Problems. An example., Teach Like a Champion

“Data, Data, Data.” Inside Statement Games’ Winning Strategy With Sports Fans – MobileGroove

Amazon’s smart thermostat falls to its lowest price of the year in early October Prime Day deal

Leave a Reply Cancel reply

You missed

Reading Comprehension Breakdowns are Often Knowledge-Problems Disguised As Skill Problems. An example., Teach Like a Champion

“Data, Data, Data.” Inside Statement Games’ Winning Strategy With Sports Fans – MobileGroove

Amazon’s smart thermostat falls to its lowest price of the year in early October Prime Day deal

Warning: Nationwide Emergency Alert Test Is Happening Tomorrow on Your Phones, TVs

Bydinesh

Step1: Create MySQL Database Table

Step2: Create Tickets Dashboard

Step3: Create Ticket

Add Ticket

Step4: Edit Ticket

Step5: View Ticket with Replies

Step6: Make Ticket Reply

Share this:

Like this:

Related Post

Leave a Reply Cancel reply

You missed