Skip to main content

Ticketing System with PHP and MySQL

Updated on: coderszine team PHP, Tutorial

In our previous tutorial, we have explained how to develop Student Attendance System with PHP and MySQL. In this tutorial, we will explain how to develop Ticketing System with PHP and MySQL.

A ticketing system is customer support tool that business can use to manage support related tasks on their website. The ticket system consist of ticket listing, create new ticket, view ticket ticket, reply ticket, open/close ticket and assign ticket to users.

We will cover following features in this system.

  • User login.
  • Ticket Listing.
  • Sort Ticket Listing.
  • Create New Ticket.
  • View Ticket.
  • Reply Ticket.
  • Open/Close Ticket.

So let’s implement ticketing system. The major files are:

  • index.php
  • login.php
  • ticket.php
  • User.php: User class to hold methods related to user.
  • Ticket.php: Ticket class to hold methods related to tickets.

Step1: Create MySQL Database Table

First we will create database tables for our system. We will create table ticket_user to store user details.

CREATE TABLE `ticket_user` (
  `userid` int(11) NOT NULL,
  `email` varchar(200) NOT NULL,
  `password` varchar(200) NOT NULL,
  `name` varchar(200) NOT NULL,
  `role` enum('admin','member') NOT NULL,
  `status` enum('Active','Inactive') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `ticket_user`
  ADD PRIMARY KEY (`userid`);
  
ALTER TABLE `ticket_user`
  MODIFY `userid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

we will create table department to store department details.

CREATE TABLE `department` (
  `id` int(11) NOT NULL,
  `department` varchar(255) NOT NULL,
  `status` enum('enable','disable') NOT NULL DEFAULT 'enable'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `department`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `department`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

we will create table tickets to store tickets details.

CREATE TABLE `tickets` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `userid` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  `mentioned` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `status` enum('open','closed','resolved') NOT NULL DEFAULT 'open'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `tickets`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `tickets`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

we will create table ticket_replies to store tickets reply details.

CREATE TABLE `ticket_replies` (
  `id` int(11) NOT NULL,
  `ticket_id` int(11) NOT NULL,
  `comments` text NOT NULL,
  `created_by` varchar(255) NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `ticket_replies`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `ticket_replies`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

Step2: User Login

We will create login form and implement user login functionality to access ticketing system.

<div class="panel panel-info">
			
	<div style="padding-top:30px" class="panel-body" >
		
		<?php if (!empty($loginMessage) && $loginMessage !='') { ?>
			<div id="login-alert" class="alert alert-danger col-sm-12"><?php echo $loginMessage; ?></div>                            
		<?php } ?>
		
		<form id="loginform" class="form-horizontal" role="form" method="POST" action="">                                    
			<div style="margin-bottom: 25px" class="input-group">
				<span class="input-group-addon"><i class="glyphicon glyphicon-user"></i></span>
				<input type="text" class="form-control" id="email" name="email" value="<?php if(!empty($_POST["email"])) { echo $_POST["email"]; } ?>" placeholder="email" style="background:white;" required>                                     
			</div>                                
			<div style="margin-bottom: 25px" class="input-group">
				<span class="input-group-addon"><i class="glyphicon glyphicon-lock"></i></span>
				<input type="password" class="form-control" id="password" name="password" value="<?php if(!empty($_POST["password"])) { echo $_POST["password"]; } ?>" placeholder="password" required>
			</div>						
			
			<div style="margin-top:10px" class="form-group">                          
				<input type="submit" name="login" value="Login" class="btn btn-info">	
			</div>		
						
		</form>   
	</div>                     
</div>

We will handle form submit and perform user login by calling method login() from class User.php.

$user = new User($db);
$loginMessage = '';
if(!empty($_POST["login"]) && !empty($_POST["email"]) && !empty($_POST["password"])) {	
	$user->email = $_POST["email"];
	$user->password = $_POST["password"];	
	if($user->login()) {
		header("Location: index.php");	
	} else {
		$loginMessage = 'Invalid login! Please try again.';
	}
} else if(!empty($_POST["login"])) {
	$loginMessage = 'Fill all fields.';
}

we will implement method login() in class User.php to handle login functionality.

public function login(){
	if($this->email && $this->password) {			
		$sqlQuery = "
			SELECT * FROM ".$this->userTable." 
			WHERE email = ? AND password = ?";			
		$stmt = $this->conn->prepare($sqlQuery);
		$password = md5($this->password);
		$stmt->bind_param("ss", $this->email, $password);	
		$stmt->execute();
		$result = $stmt->get_result();
		if($result->num_rows > 0){
			$user = $result->fetch_assoc();
			$_SESSION["userid"] = $user['userid'];
			$_SESSION["role"] = $user['role'];
			$_SESSION["name"] = $user['name'];					
			return 1;		
		} else {
			return 0;		
		}			
	} else {
		return 0;
	}
}

Step3: Ticket Listing

We will implement ticket listing functionality to list all tickets to logged in user. We will call method getTicket() from class Ticket.php and loop through to list tickets with details.

<div class="row">						
	<div class="col-md-12">
		<ul class="list-group fa-padding">
			<?php
			if(isset($_GET['userid']) && !empty($_GET['userid'])) {
				$ticket->userId = $_GET['userid'];
			}
			
			if(isset($_GET['status']) && !empty($_GET['status'])) {
				$ticket->status = $_GET['status'];
			}	

			if(isset($_GET['order']) && !empty($_GET['order'])) {
				$ticket->order = $_GET['order'];
			}	
			
			$ticketResult = $ticket->getTicket();
			while ($ticketDetails = $ticketResult->fetch_assoc()) {
				$ticket->id = $ticketDetails["id"];
			?>
			<li class="list-group-item">
				<div class="media">
					<i class="fa fa-code pull-left"></i>
					<div class="media-body">
						<a href="ticket.php?ticket_id=<?php echo $ticketDetails["id"]; ?>"><strong><?php echo $ticketDetails['title']; ?></strong> <span class="number pull-right"># <?php echo $ticketDetails['id']; ?> </span></a>
						<p class="info">Opened by <a href="#"><?php echo $ticketDetails['name']; ?></a> <?php echo $ticket->timeElapsedString($ticketDetails['created']); ?> <i class="fa fa-comments"></i> <a href="#"><?php echo $ticket->getReplyCount(); ?> Reply</a></p>
					</div>
				</div>
			</li>
			<?php
			}
			?>								
		</ul>
	</div>						
</div>

We will implement method getTicket() in class Ticket.php.

public function getTicket(){		
	$sqlWhere = '';		

	if(!empty($this->userId)) {
		$sqlWhere = " AND ticket.userid = '".$this->userId."'";
	}		

	$status = 'open';
	if(!empty($this->status) && $this->status == 'closed') {
		$status = 'closed';
	}
	
	$order = ' ORDER BY id DESC';
	if(!empty($this->order) && $this->order == 'oldest') {
		$order = ' ORDER BY id ASC';
	}
	
	$sqlQuery = "
		SELECT ticket.id, ticket.title, ticket.message, ticket.userid, ticket.mentioned, ticket.created, ticket.status, user.name
		FROM ".$this->ticketsTable." ticket
		LEFT JOIN ".$this->userTable." user ON user.userid = ticket.userid
		LEFT JOIN ".$this->departmentTable." department ON department.id = ticket.department_id
		WHERE ticket.status = '".$status."' $sqlWhere $order";	
		
	$stmt = $this->conn->prepare($sqlQuery);			
	$stmt->execute();
	$result = $stmt->get_result();
	return $result;
}

Step4: Create New Ticket

We will create modal form to create new ticket and save into MySQL database table.

<div class="modal fade" id="newIssue" tabindex="-1" role="dialog" aria-labelledby="newIssue" aria-hidden="true">
	<div class="modal-wrapper">
		<div class="modal-dialog">
			<div class="modal-content">
				<div class="modal-header bg-blue">
					<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
					<h4 class="modal-title"><i class="fa fa-pencil"></i> Create New Ticket</h4>
				</div>
				<form id="ticketForm" method="post">
					<div class="modal-body">
						<div class="form-group">
							<input name="subject" type="text" class="form-control" placeholder="Subject">
						</div>	
						<div class="form-group">
							<select style="height:34px;" class="form-control" id="department" name="department">
								<option value=''>--Select Department--</option>
								<?php 
								$result = $ticket->departmentList();
								while ($department = $result->fetch_assoc()) { 	
								?>
									<option value="<?php echo $department['id']; ?>"><?php echo ucfirst($department['department']); ?></option>							
								<?php } ?>
							</select>
						</div>	
						<div class="form-group">
							<textarea name="message" class="form-control" placeholder="Please detail your issue or question" style="height: 120px;"></textarea>
						</div>											
					</div>
					<div class="modal-footer">
						<input name="action" type="hidden" value="createTicket">
						<button type="submit" class="btn btn-default" data-dismiss="modal"><i class="fa fa-times"></i> Close</button>
						<button type="submit" class="btn btn-primary pull-right"><i class="fa fa-pencil"></i> Create</button>
					</div>
				</form>
			</div>
		</div>
	</div>
</div>

We will handle modal form submit in ticket.js and call function saveTicket() to save ticket.

$("#ticketForm").submit(function(event){	
		saveTicket();	
		return false;
	});

we will implement function saveTicket() in ticket.js to save ticket with Ajax.

function saveTicket(){
	 $.ajax({
		type: "POST",
		url: "action.php",
		cache:false,
		data: $('form#ticketForm').serialize(),
		success: function(response){			
			$("#newIssue").modal('hide');
			location.reload();
		},
		error: function(){
			alert("Error");
		}
	});
}

We will check for action creatTicket and call method insert() to insert ticket details.

public function insert(){
		
	if($this->subject && $this->message) {

		$stmt = $this->conn->prepare("
		INSERT INTO ".$this->ticketsTable."(`title`, `message`, `userid`, `department_id`)
		VALUES(?,?,?,?)");
	
		$this->subject = htmlspecialchars(strip_tags($this->subject));
		$this->message = htmlspecialchars(strip_tags($this->message));
		$this->department = htmlspecialchars(strip_tags($this->department));			
		
		$stmt->bind_param("ssii", $this->subject, $this->message, $_SESSION["userid"], $this->department);
		
		if($stmt->execute()){
			return true;
		}		
	}
}

Step5: View Ticket

We will implement functionality to view ticket details and replies. We will call method getTicketDetail() from class Ticket.php and display details.

<div class="row">						
	<div class="col-md-12">
		<ul class="list-group fa-padding">
			<?php								
			if(!empty($_GET['ticket_id']) && $_GET['ticket_id']) {
				$ticket->ticket_id = $_GET['ticket_id'];
			}
			$ticketResult = $ticket->getTicketDetail();
			$replyCount = 0;
			while ($ticketDetails = $ticketResult->fetch_assoc()) {	
									
			?>									
			<?php if($replyCount == 0) { ?>
			<li class="list-group-item" id="ticketReplyDetails">
				<div class="row">
				
					<?php
					if($ticketDetails['status'] == 'closed') {
					?>
					<a class="btn btn-warning" title="Make Open" data-ticket-id="<?php echo $ticketDetails['id']; ?>" id="openTicket">Open Ticket</a>
					<?php } else { ?>
					<a class="btn btn-danger" title="Make Closed" data-ticket-id="<?php echo $ticketDetails['id']; ?>" id="closeTicket">Close Ticket</a>
					<?php } ?>
					
					<?php 
					if($ticketDetails['status'] == 'open') {
					?>										              
					<a class="btn btn-default" data-ticket-id="<?php echo $ticketDetails['id']; ?>" id="ticketReplyButton" title="Reply to ticket">Reply</a>
					<?php } ?>
				</div>								
			</li>
			<li class="list-group-item">
				<div class="media">
					<div class="media-body">
						<div>
						<span class="number pull-right"># <?php echo $ticketDetails['id']; ?> </span>
						<span style="font-size:26px;padding-bottom:10px;"><?php echo $ticketDetails['title']; ?></span>
						
						<?php 
						if($ticketDetails['status'] == 'open') {
							echo "<span style='color:red;font-weight:bold;background-color:black;padding:4px;'>Open</span>";
						} else if($ticketDetails['status'] == 'closed') {
							echo "<span style='color:green;font-weight:bold;background-color:black;padding:4px;'>Closed</span>"; 
						}										
						
						?>											
						</span>
						</div>
						<p class="info">Replied by <a href="#"><?php echo $ticketDetails['name']; ?></a> <?php echo $ticket->timeElapsedString($ticketDetails['created']); ?> <i class="fa fa-comments"></i> 					
						
						</p>
						
						<p><?php echo $ticketDetails["message"]; ?></p>
					</div>
				</div>
			</li>
			<?php } ?>		
			<?php if($ticketDetails["comments"]) { ?>								
			<li class="list-group-item">
				<div class="media">
					<div class="media-body">										
						<p class="info">Replied by <a href="#"><?php echo $ticketDetails['name']; ?></a> <?php echo $ticket->timeElapsedString($ticketDetails['reply_date']); ?> <i class="fa fa-comments"></i> 					
						
						</p>
						
						<p><?php echo $ticketDetails["comments"]; ?></p>
					</div>
				</div>
			</li>
			<?php } ?>
			
			<?php
			$replyCount++;	
			}
			?>								
		</ul>						
	</div>						
</div>

we will implement method getTicketDetail() in class Ticket.php to get ticket details.

public function getTicketDetail(){			
	if($_SESSION["userid"] && $this->ticket_id) {				
		$sqlQuery = "
			SELECT ticket.id, ticket.title, ticket.message, ticket.userid, ticket.mentioned, ticket.created, ticket.status, user.name, reply.comments, reply.created AS reply_date
			FROM ".$this->ticketsTable." ticket
			LEFT JOIN ".$this->ticketReplyTable." reply ON ticket.id = reply.ticket_id
			LEFT JOIN ".$this->userTable." user ON user.userid = ticket.userid				
			WHERE ticket.id = '".$this->ticket_id."'";	
			
		$stmt = $this->conn->prepare($sqlQuery);			
		$stmt->execute();
		$result = $stmt->get_result();
		return $result;
	}
}

Step6: Implement Ticket Reply

We will open a modal form and implement functionality to make ticket reply. 

<div class="modal fade" id="ticketReplyModal" tabindex="-1" role="dialog" aria-labelledby="ticketReply" aria-hidden="true">
	<div class="modal-wrapper">
		<div class="modal-dialog">
			<div class="modal-content">
				<div class="modal-header bg-blue">
					<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
					<h4 class="modal-title"><i class="fa fa-pencil"></i> Reply to ticket</h4>
				</div>
				<form id="replyForm" method="post">
					<div class="modal-body">								
						<div class="form-group">
							<textarea name="message" class="form-control" placeholder="Please detail your issue or question" style="height: 120px;"></textarea>
						</div>											
					</div>
					<div class="modal-footer">
						<input name="ticketId" id="ticketId" type="hidden" value="">
						<input name="action" type="hidden" value="replyTicket">
						<button type="submit" class="btn btn-default" data-dismiss="modal"><i class="fa fa-times"></i> Close</button>
						<button type="submit" id="save" class="btn btn-primary pull-right"><i class="fa fa-pencil"></i> Reply</button>
					</div>
				</form>
			</div>
		</div>
	</div>
</div>

In ticket.js, we will handle modal form submit and make ajax request to to implement ticket reply save functionality.

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

We will implement method saveTicketReply() in class Ticket.php to save ticekt replies into database.

function saveTicketReply() {
		
	if($_SESSION["userid"] && $this->ticketId && $this->replyMessage) {
		
		$stmt = $this->conn->prepare("
		INSERT INTO ".$this->ticketReplyTable."(`ticket_id`, `comments`, `created_by`)
		VALUES(?,?,?)");
	
		$this->replyMessage = htmlspecialchars(strip_tags($this->replyMessage));
		$this->ticketId = htmlspecialchars(strip_tags($this->ticketId));
					
		$stmt->bind_param("iss", $this->ticketId, $this->replyMessage, $_SESSION["userid"]);
		
		if($stmt->execute()){
			return true;
		}		
	}	
	
}

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