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:

• User Management System with PHP & MySQL
• Build Online Voting System with PHP & MySQL
• School Management System with PHP & MySQL
• Project Management System with PHP and MySQL
• Hospital Management System with PHP & MySQL

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.

<p>View and manage tickets that may have responses from support team.</p>		
<table id="listTickets" class="table table-bordered table-striped">
	<thead>
		<tr>
			<th>S/N</th>
			<th>Ticket ID</th>
			<th>Subject</th>
			<th>Department</th>
			<th>Created By</th>					
			<th>Created</th>	
			<th>Status</th>
			<th></th>
			<th></th>
			<th></th>					
		</tr>
	</thead>
</table>

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 = '<span class="label label-success">Open</span>';
		} else if($ticket['resolved'] == 1) {
			$status = '<span class="label label-danger">Closed</span>';
		}	
		$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[] = '<a href="ticket.php?id='.$ticket["uniqid"].'" class="btn btn-success btn-xs update">View Ticket</a>';	
		$ticketRows[] = '<button type="button" name="update" id="'.$ticket["id"].'" class="btn btn-warning btn-xs update" '.$disbaled.'>Edit</button>';
		$ticketRows[] = '<button type="button" name="delete" id="'.$ticket["id"].'" class="btn btn-danger btn-xs delete"  '.$disbaled.'>Close</button>';
		$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.

<div id="ticketModal" class="modal fade">
	<div class="modal-dialog">
		<form method="post" id="ticketForm">
			<div class="modal-content">
				<div class="modal-header">
					<button type="button" class="close" data-dismiss="modal">×</button>
					<h4 class="modal-title"><i class="fa fa-plus"></i> Add Ticket</h4>
				</div>
				<div class="modal-body">
					<div class="form-group"
						<label for="subject" class="control-label">Subject</label>
						<input type="text" class="form-control" id="subject" name="subject" placeholder="Subject" required>			
					</div>
					<div class="form-group">
						<label for="department" class="control-label">Department</label>							
						<select id="department" name="department" class="form-control" placeholder="Department...">					
							<?php $tickets->getDepartments(); ?>
						</select>						
					</div>						
					<div class="form-group">
						<label for="message" class="control-label">Message</label>							
						<textarea class="form-control" rows="5" id="message" name="message"></textarea>							
					</div>	
					<div class="form-group">
						<label for="status" class="control-label">Status</label>							
						<label class="radio-inline">
							<input type="radio" name="status" id="open" value="0" checked required>Open
						</label>
						<?php if(isset($_SESSION["admin"])) { ?>
							<label class="radio-inline">
								<input type="radio" name="status" id="close" value="1" required>Close
							</label>
						<?php } ?>	
					</div>
				</div>
				<div class="modal-footer">
					<input type="hidden" name="ticketId" id="ticketId" />
					<input type="hidden" name="action" id="action" value="" />
					<input type="submit" name="save" id="save" class="btn btn-info" value="Save" />
					<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
				</div>
			</div>
		</form>
	</div>
</div>

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 '<div class="alert error">Please fill in all fields.</div>';
	}
}	

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("<i class='fa fa-plus'></i> 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.

<section class="comment-list">          
	<article class="row">            
		<div class="col-md-10 col-sm-10">
			<div class="panel panel-default arrow left">
				<div class="panel-heading right">
				<?php if($ticketDetails['resolved']) { ?>
				<button type="button" class="btn btn-danger btn-sm">
				  <span class="glyphicon glyphicon-eye-close"></span> Closed
				</button>
				<?php } else { ?>
				<button type="button" class="btn btn-success btn-sm">
				  <span class="glyphicon glyphicon-eye-open"></span> Open
				</button>
				<?php } ?>
				<span class="ticket-title"><?php echo $ticketDetails['title']; ?></span>
				</div>
				<div class="panel-body">						
					<div class="comment-post">
					<p>
					<?php echo $ticketDetails['message']; ?>
					</p>
					</div>                 
				</div>
				<div class="panel-heading right">
					<span class="glyphicon glyphicon-time"></span> <time class="comment-date" datetime="16-12-2014 01:05"><i class="fa fa-clock-o"></i> <?php echo $time->ago($ticketDetails['date']); ?></time>
					  <span class="glyphicon glyphicon-user"></span> <?php echo $ticketDetails['creater']; ?>
					  <span class="glyphicon glyphicon-briefcase"></span> <?php echo $ticketDetails['department']; ?>
				</div>
			</div>			 
		</div>
	</article>			
	<?php foreach ($ticketReplies as $replies) { ?>		
		<article class="row">
			<div class="col-md-10 col-sm-10">
				<div class="panel panel-default arrow right">
					<div class="panel-heading">
						<?php if($replies['user_group'] == 1) { ?>							
							<span class="glyphicon glyphicon-user"></span> <?php echo $ticketDetails['department']; ?>
						<?php } else { ?>
							<span class="glyphicon glyphicon-user"></span> <?php echo $replies['creater']; ?>
						<?php } ?>
						  <span class="glyphicon glyphicon-time"></span> <time class="comment-date" datetime="16-12-2014 01:05"><i class="fa fa-clock-o"></i> <?php echo $time->ago($replies['date']); ?></time>							
					</div>
					<div class="panel-body">						
						<div class="comment-post">
						<p>
						<?php echo $replies['message']; ?>
						</p>
						</div>                  
					</div>						
				</div>
			</div>            
		</article> 		
	<?php } ?>	
</section>	

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.

<form method="post" id="ticketReply">
	<article class="row">
		<div class="col-md-10 col-sm-10">				
			<div class="form-group">							
				<textarea class="form-control" rows="5" id="message" name="message" placeholder="Enter your reply..." required></textarea>	
			</div>				
	</div>
	</article>  
	<article class="row">
		<div class="col-md-10 col-sm-10">
			<div class="form-group">							
				<input type="submit" name="reply" id="reply" class="btn btn-success" value="Reply" />		
			</div>
		</div>
	</article> 
	<input type="hidden" name="ticketId" id="ticketId" value="<?php echo $ticketDetails['id']; ?>" />	
	<input type="hidden" name="action" id="action" value="saveTicketReplies" />			
</form>

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:

• User Management System with PHP & MySQL
• Datatables Add Edit Delete with Ajax, PHP & MySQL
• Build Online Voting System with PHP & MySQL
• School Management System with PHP & MySQL
• DataTables Add Edit Delete with CodeIgniter
• Create RESTful API using CodeIgniter
• Build Reusable Captcha Script with PHP
• Product Search Filtering using Ajax, PHP & MySQL
• Image Upload and Crop in Modal with jQuery, PHP & MySQL
• Build Push Notification System with PHP & MySQL
• Project Management System with PHP and MySQL
• Hospital Management System with PHP & MySQL
• Build Newsletter System with PHP and MySQL
• Skeleton Screen Loading Effect with Ajax and PHP
• Build Discussion Forum with PHP and MySQL
• Customer Relationship Management (CRM) System with PHP & MySQL
• Online Exam System with PHP & MySQL
• Expense Management System with PHP & MySQL

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