用jQuery、PHP和MySQL构建服务台系统

服务台系统或支持票据系统是公司常用的系统，以帮助客户解决他们的疑问和问题。服务台系统被支持团队和客户用来添加票据、回复票据和解决问题或查询。它允许客户添加带有问题细节的票据，而支持人员则通过解决方案和细节对该票据进行回复。

因此，如果你正在考虑用PHP开发基于Web的服务台票务系统，那么你就来对地方了。在我们之前的教程中，你已经学会了如何用PHP和MySQL创建用户管理系统，在本教程中，你将学习如何用PHP和MySQL开发服务台票务系统。

我们将通过帮助台系统的实例一步一步地介绍本教程，包括创建票据、列出票据、编辑票据、关闭票据、回复票据、查看票据与回复等。

另外，请阅读。

• 使用PHP和MySQL的用户管理系统
• 用PHP和MySQL建立在线投票系统
• 用PHP和MySQL建立学校管理系统
• 用PHP和MySQL建立项目管理系统
• 用PHP和MySQL建立医院管理系统

所以，让我们开始用PHP和MySQL实现帮助台票务系统。在我们开始之前，请看一下这个例子的文件结构。

• index.php
• ticket.php
• ajax.js
• process.php
• Users.php。一个存放用户方法的类。
• Tickets.php:一个用于保存票据方法的类。

步骤1：创建MySQL数据库表

我们将创建MySQL数据库表来建立服务台系统。我们将创建hd_users表来存储用户的登录信息。

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;

我们将创建hd_departments表来存储帮助团队部门的详细信息。

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

我们将创建hd_tickets表来存储票据细节。

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;

我们将创建hd_ticket_replies表来存储票务回复的详细信息。

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;

第二步：创建票务仪表板

首先，我们将创建仪表板来显示票据列表，包括编辑、关闭和查看选项。

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

在ajax.js文件中，我们将用动作listTicket向process.php发出ajax请求，以加载票据列表的详细信息。

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
});			

在process.php中，我们将调用票务方法**showTickets()**来获取票务细节。

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

在Ticket.php类中，我们将创建**showTickets()**方法，返回票据细节和JSON数据。

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);
}	

第三步：创建门票

我们将在add_ticket_modal.php中创建门票创建模式的设计，并包含在门票创建模式所需的文件中。

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

在ajax.php中，我们将向process.php发出ajax请求，用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();
		}
	})
});			

在process.php中，我们将调用票务方法**createTicket()**来保存票务细节。

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

在Ticket.php类中，我们将创建方法createTicket()，将票据信息保存到MySQL数据库表中。

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>';
	}
}	

第四步：编辑门票

我们将向process.php发出ajax请求，用getTicketDetails动作加载票面细节，编辑票面。

$(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');
		}
	})
});			

在process.php中，我们将调用票务方法**getTicketDetails()**来获取票务信息，以填写在编辑表单中。

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

在Ticket.php类中，我们将创建**getTicketDetails()**方法，从MySQL数据库表中获取票据详细信息。

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);
	}
}

第五步：查看有回复的票据

在ticket.php中，我们将创建设计来查看带有回复的票据。

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

我们将调用票务方法来获取票务详情和票务回复。

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

在Ticket.php类中，我们将创建**getTicketReplies()**方法，从MySQL数据库表中获得票据回复的详细信息。

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;
}

第六步：制作票务回复

我们将在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>

我们将处理票据回复表单的提交，并通过动作saveTicketReplies向process.php发出ajax请求。

$(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();
		}
	})
});		

在process.php中，我们将调用门票方法**saveTicketReplies()**来保存门票回复。

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

在Ticket.php类中，我们将创建**saveTicketReplies()**方法，把票据回复保存到MySQL数据库表中。

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);
	} 
}	

我们还处理了与票据、客户和支持有关的其他功能。要获得所有的文件，你可以下载完整的项目代码，以便根据你的要求来实现和使用它。

你可能也喜欢。

• 用PHP和MySQL开发的用户管理系统
• 用Ajax、PHP和MySQL添加编辑删除数据表格
• 用PHP和MySQL构建在线投票系统
• 用PHP和MySQL建立学校管理系统
• 使用CodeIgniter添加编辑删除数据表
• 使用CodeIgniter创建RESTful API
• 用PHP构建可重复使用的验证码脚本
• 使用Ajax、PHP和MySQL进行产品搜索过滤
• 用jQuery、PHP和MySQL在模版中上传和裁剪图片
• 用PHP和MySQL构建推送通知系统
• 用PHP和MySQL构建项目管理系统
• 用PHP和MySQL建立医院管理系统
• 用PHP和MySQL建立电子报系统
• 用Ajax和PHP实现骨架屏幕加载效果
• 用PHP和MySQL建立讨论区
• 用PHP和MySQL构建客户关系管理（CRM）系统
• 用PHP和MySQL建立在线考试系统
• 用PHP和MySQL建立费用管理系统

你可以从演示链接中查看实时演示，也可以从下面的下载链接中下载脚本。
演示 下载

用jQuery、PHP和MySQL构建服务台系统》一文首次出现在WD上。