Helpdesk System with jQuery, PHP & MySQL

TechArise Team

2 years ago

A Helpdesk or service desk is a one-stop point of contact that provides centralized information and support management service to handle a company’s internal or external queries. A helpdesk software solution enables companies to resolve customer grievances faster and efficiently by simply automating the complaint resolution process with the ticket management system.

In this tutorial, we will learn how to Build a Helpdesk System with jQuery, PHP & MySQL. This is a very simple example, you can just copy-paste and change according to your requirement.

Also Read: Login with Facebook using PHP

Before started to implement the Helpdesk System with jQuery, PHP & MySQL, look files structure:

helpdesk-system-php
- class
  - Database.php
  - Users.php
  - Tickets.php
  - Department.php
  - Time.php
- css
  - style.css
- js
  - comman.js
  - department.js
  - tickets.js
  - user.js
- templates
  - header.php
  - footer.php
- config.php
- menus.php
- dbConfig.php
- user.php
- login.php
- logout.php
- user_action.php
- add_ticket_model.php
- ticket.php
- ticket_action.php
- view_ticket.php
- department.php
- department_action.php

Step 1: Create the database and Table

For this tutorial, you need a MySQL database with the following table:

Step 2: Database Connection class

Create a file named Database.php inside “class/” folder.

<?php
/**
 * @package Database class
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 *   
 **/
// Database Connection
class Database
{
    public function dbConnect()
    {
        static $db= null;
        if (is_null($db)) {
            $con = new mysqli(HOST, USER_NAME, PASSWORD, DATABASE);
            if ($con->connect_error) {
                die("Error failed to connect to MySQL: " . $con->connect_error);
            } else {
                $db = $con;
            }
        }
        return $db;
    }
}
?>

<?php

/**

* @package Database class

* @author TechArise Team

* @email info@techarise.com

**/

// Database Connection

class Database

{

public function dbConnect()

{

static $db= null;

if (is_null($db)) {

$con = new mysqli(HOST, USER_NAME, PASSWORD, DATABASE);

if ($con->connect_error) {

die("Error failed to connect to MySQL: " . $con->connect_error);

} else {

$db = $con;

}

return $db;

}

Step 3: Create a class file

Create a class file named Users.php inside “class/” folder.

The class handles the User process.
__construct() – Loads the required class.
isLoggedIn() – check login
login() – handle login process
getUserInfo() – get user record from database
listUser() – list users record from database
insert() – Insert recored in database
update() – update user record from database
delete() – Delete user record from database

<?php
/**
 * @package Users class
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 *   
 */
class Users extends Database
{
	private $userTable = 'users';
	private $dbConnect = false;
	public function __construct()
	{
		$this->dbConnect = $this->dbConnect();
	}
	public function isLoggedIn()
	{
		if (isset($_SESSION["userid"])) {
			return true;
		} else {
			return false;
		}
	}
	public function login()
	{
		$errorMessage = '';
		if (!empty($_POST["login"]) && $_POST["email"] != '' && $_POST["password"] != '') {
			$email = $_POST['email'];
			$password = $_POST['password'];
			$sqlQuery = "SELECT * FROM " . $this->userTable . " 
				WHERE email='" . $email . "' AND password='" . md5($password) . "' AND status = 1";

			$resultSet = mysqli_query($this->dbConnect, $sqlQuery);
			$isValidLogin = mysqli_num_rows($resultSet);
			if ($isValidLogin) {
				$userDetails = mysqli_fetch_assoc($resultSet);
				$_SESSION["userid"] = $userDetails['id'];
				$_SESSION["user_name"] = $userDetails['name'];
				if ($userDetails['user_type'] == 'admin') {
					$_SESSION["admin"] = 1;
				}
				header("location: ticket.php");
			} else {
				$errorMessage = "Invalid login!";
			}
		} else if (!empty($_POST["login"])) {
			$errorMessage = "Enter Both user and password!";
		}
		return $errorMessage;
	}
	public function getUserInfo()
	{
		if (!empty($_SESSION["userid"])) {
			$sqlQuery = "SELECT * FROM " . $this->userTable . " 
				WHERE id ='" . $_SESSION["userid"] . "'";
			$result = mysqli_query($this->dbConnect, $sqlQuery);
			$userDetails = mysqli_fetch_assoc($result);
			return $userDetails;
		}
	}
	public function getColoumn($id, $column)
	{
		$sqlQuery = "SELECT * FROM " . $this->userTable . " 
			WHERE id ='" . $id . "'";
		$result = mysqli_query($this->dbConnect, $sqlQuery);
		$userDetails = mysqli_fetch_assoc($result);
		return $userDetails[$column];
	}


	public function listUser()
	{

		$sqlQuery = "SELECT id, name, email, create_date, user_type, status 
			FROM " . $this->userTable;

		if (!empty($_POST["search"]["value"])) {
			$sqlQuery .= ' (id LIKE "%' . $_POST["search"]["value"] . '%" ';
			$sqlQuery .= ' OR name LIKE "%' . $_POST["search"]["value"] . '%" ';
			$sqlQuery .= ' OR status LIKE "%' . $_POST["search"]["value"] . '%" ';
		}
		if (!empty($_POST["order"])) {
			$sqlQuery .= ' ORDER BY ' . $_POST['order']['0']['column'] . ' ' . $_POST['order']['0']['dir'] . ' ';
		} else {
			$sqlQuery .= ' ORDER BY id DESC ';
		}
		if ($_POST["length"] != -1) {
			$sqlQuery .= ' LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
		}

		$result = mysqli_query($this->dbConnect, $sqlQuery);
		$numRows = mysqli_num_rows($result);
		$userData = array();
		while ($user = mysqli_fetch_assoc($result)) {
			$userRows = array();
			$status = '';
			if ($user['status'] == 1) {
				$status = '<span class="label label-success">Active</span>';
			} else if ($user['status'] == 0) {
				$status = '<span class="label label-danger">Inactive</span>';
			}

			$userRole = '';
			if ($user['user_type'] == 'admin') {
				$userRole = '<span class="label label-danger">Admin</span>';
			} else if ($user['user_type'] == 'user') {
				$userRole = '<span class="label label-warning">Member</span>';
			}

			$userRows[] = $user['id'];
			$userRows[] = $user['name'];
			$userRows[] = $user['email'];
			$userRows[] = $user['create_date'];
			$userRows[] = $userRole;
			$userRows[] = $status;

			$userRows[] = '<button type="button" name="update" id="' . $user["id"] . '" class="btn btn-info btn-sm update"><i class="fa fa-edit"></i> Edit</button>';
			$userRows[] = '<button type="button" name="delete" id="' . $user["id"] . '" class="btn btn-danger btn-sm delete"><i class="fa fa-times"></i> Delete</button>';
			$userData[] = $userRows;
		}
		$output = array(
			"draw"				=>	intval($_POST["draw"]),
			"recordsTotal"  	=>  $numRows,
			"recordsFiltered" 	=> 	$numRows,
			"data"    			=> 	$userData
		);
		echo json_encode($output);
	}


	public function getUserDetails()
	{
		if ($this->id) {
			$sqlQuery = "
				SELECT id, name, email, password, create_date, user_type, status 
				FROM " . $this->userTable . " 
				WHERE id = '" . $this->id . "'";
			$result = mysqli_query($this->dbConnect, $sqlQuery);
			$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
			echo json_encode($row);
		}
	}

	public function insert()
	{
		if ($this->userName && $this->email) {
			$this->userName = strip_tags($this->userName);
			$this->newPassword = md5($this->newPassword);
			$queryInsert = "
				INSERT INTO " . $this->userTable . "(name, email, user_type, status, password) VALUES(
				'" . $this->userName . "', '" . $this->email . "', '" . $this->role . "','" . $this->status . "', '" . $this->newPassword . "')";
			mysqli_query($this->dbConnect, $queryInsert);
		}
	}

	public function update()
	{
		if ($this->updateUserId && $this->userName) {
			$this->userName = strip_tags($this->userName);

			$changePassword = '';
			if ($this->newPassword) {
				$this->newPassword = md5($this->newPassword);
				$changePassword = ", password = '" . $this->newPassword . "'";
			}

			$queryUpdate = "
				UPDATE " . $this->userTable . " 
				SET name = '" . $this->userName . "', email = '" . $this->email . "', user_type = '" . $this->role . "', status = '" . $this->status . "' $changePassword
				WHERE id = '" . $this->updateUserId . "'";
			mysqli_query($this->dbConnect, $queryUpdate);
		}
	}

	public function delete()
	{
		if ($this->deleteUserId) {
			$queryUpdate = "
				DELETE FROM " . $this->userTable . " 
				WHERE id = '" . $this->deleteUserId . "'";
			mysqli_query($this->dbConnect, $queryUpdate);
		}
	}
}
?>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

<?php

/**

* @package Users class

* @author TechArise Team

* @email info@techarise.com

class Users extends Database

{

private $userTable = 'users';

private $dbConnect = false;

public function __construct()

{

$this->dbConnect = $this->dbConnect();

}

public function isLoggedIn()

{

if (isset($_SESSION["userid"])) {

return true;

} else {

return false;

}

public function login()

{

$errorMessage = '';

if (!empty($_POST["login"]) && $_POST["email"] != '' && $_POST["password"] != '') {

$email = $_POST['email'];

$password = $_POST['password'];

$sqlQuery = "SELECT * FROM " . $this->userTable . "

WHERE email='" . $email . "' AND password='" . md5($password) . "' AND status = 1";

$resultSet = mysqli_query($this->dbConnect, $sqlQuery);

$isValidLogin = mysqli_num_rows($resultSet);

if ($isValidLogin) {

$userDetails = mysqli_fetch_assoc($resultSet);

$_SESSION["userid"] = $userDetails['id'];

$_SESSION["user_name"] = $userDetails['name'];

if ($userDetails['user_type'] == 'admin') {

$_SESSION["admin"] = 1;

}

header("location: ticket.php");

} else {

$errorMessage = "Invalid login!";

}

} else if (!empty($_POST["login"])) {

$errorMessage = "Enter Both user and password!";

}

return $errorMessage;

}

public function getUserInfo()

{

if (!empty($_SESSION["userid"])) {

$sqlQuery = "SELECT * FROM " . $this->userTable . "

WHERE id ='" . $_SESSION["userid"] . "'";

$result = mysqli_query($this->dbConnect, $sqlQuery);

$userDetails = mysqli_fetch_assoc($result);

return $userDetails;

}

public function getColoumn($id, $column)

{

$sqlQuery = "SELECT * FROM " . $this->userTable . "

WHERE id ='" . $id . "'";

$result = mysqli_query($this->dbConnect, $sqlQuery);

$userDetails = mysqli_fetch_assoc($result);

return $userDetails[$column];

}

public function listUser()

{

$sqlQuery = "SELECT id, name, email, create_date, user_type, status

FROM " . $this->userTable;

if (!empty($_POST["search"]["value"])) {

$sqlQuery .= ' (id LIKE "%' . $_POST["search"]["value"] . '%" ';

$sqlQuery .= ' OR name LIKE "%' . $_POST["search"]["value"] . '%" ';

$sqlQuery .= ' OR status LIKE "%' . $_POST["search"]["value"] . '%" ';

}

if (!empty($_POST["order"])) {

$sqlQuery .= ' ORDER BY ' . $_POST['order']['0']['column'] . ' ' . $_POST['order']['0']['dir'] . ' ';

} else {

$sqlQuery .= ' ORDER BY id DESC ';

}

if ($_POST["length"] != -1) {

$sqlQuery .= ' LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];

}

$result = mysqli_query($this->dbConnect, $sqlQuery);

$numRows = mysqli_num_rows($result);

$userData = array();

while ($user = mysqli_fetch_assoc($result)) {

$userRows = array();

$status = '';

if ($user['status'] == 1) {

$status = '<span class="label label-success">Active</span>';

} else if ($user['status'] == 0) {

$status = '<span class="label label-danger">Inactive</span>';

}

$userRole = '';

if ($user['user_type'] == 'admin') {

$userRole = '<span class="label label-danger">Admin</span>';

} else if ($user['user_type'] == 'user') {

$userRole = '<span class="label label-warning">Member</span>';

}

$userRows[] = $user['id'];

$userRows[] = $user['name'];

$userRows[] = $user['email'];

$userRows[] = $user['create_date'];

$userRows[] = $userRole;

$userRows[] = $status;

$userRows[] = '<button type="button" name="update" id="' . $user["id"] . '" class="btn btn-info btn-sm update"><i class="fa fa-edit"></i> Edit</button>';

$userRows[] = '<button type="button" name="delete" id="' . $user["id"] . '" class="btn btn-danger btn-sm delete"><i class="fa fa-times"></i> Delete</button>';

$userData[] = $userRows;

}

$output = array(

"draw" => intval($_POST["draw"]),

"recordsTotal" => $numRows,

"recordsFiltered" => $numRows,

"data" => $userData

);

echo json_encode($output);

}

public function getUserDetails()

{

if ($this->id) {

$sqlQuery = "

SELECT id, name, email, password, create_date, user_type, status

FROM " . $this->userTable . "

WHERE id = '" . $this->id . "'";

$result = mysqli_query($this->dbConnect, $sqlQuery);

$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

echo json_encode($row);

}

public function insert()

{

if ($this->userName && $this->email) {

$this->userName = strip_tags($this->userName);

$this->newPassword = md5($this->newPassword);

$queryInsert = "

INSERT INTO " . $this->userTable . "(name, email, user_type, status, password) VALUES(

'" . $this->userName . "', '" . $this->email . "', '" . $this->role . "','" . $this->status . "', '" . $this->newPassword . "')";

mysqli_query($this->dbConnect, $queryInsert);

}

public function update()

{

if ($this->updateUserId && $this->userName) {

$this->userName = strip_tags($this->userName);

$changePassword = '';

if ($this->newPassword) {

$this->newPassword = md5($this->newPassword);

$changePassword = ", password = '" . $this->newPassword . "'";

}

$queryUpdate = "

UPDATE " . $this->userTable . "

SET name = '" . $this->userName . "', email = '" . $this->email . "', user_type = '" . $this->role . "', status = '" . $this->status . "' $changePassword

WHERE id = '" . $this->updateUserId . "'";

mysqli_query($this->dbConnect, $queryUpdate);

}

public function delete()

{

if ($this->deleteUserId) {

$queryUpdate = "

DELETE FROM " . $this->userTable . "

WHERE id = '" . $this->deleteUserId . "'";

mysqli_query($this->dbConnect, $queryUpdate);

}

Step 4: Create a class file

Create a class file named Ticket.php inside “class/” folder.

The class handles the Ticket process.
__construct() – Loads the required class.
getDepartments() – get departments list from database
closeTicket() – close ticket process
getTicketDetails() – get ticket record from database
gellAllTicket() – get tiecket record from database
createTicket() – Insert new recored in database
updateTicket() – update ticket record from database
ticketInfo() – ticket record from database
delete() – Delete tecket record from database
saveTicketReplies() – Save Ticket reply record in database
getTicketReplies() – get tecket replies record from database
updateTicketReadStatus() – update tecket replies record in database

<?php
/**
 * @package Ticket class
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 *   
 **/

class Tickets extends Database {  
    private $ticketTable = 'tickets';
	private $ticketRepliesTable = 'ticket_replies';
	private $departmentsTable = 'department';
	private $userTable = 'users';
	private $dbConnect = false;
	public function __construct(){		
        $this->dbConnect = $this->dbConnect();
    } 
	
	public function gellAllTicket() {
		$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.name as creater, d.name as department, u.user_type, t.user, t.user_read, t.admin_read
			FROM ".$this->ticketTable." t 
			LEFT JOIN ".$this->userTable." u ON t.user = u.id 
			LEFT JOIN ".$this->departmentsTable." 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="view_ticket.php?id='.$ticket["uniqid"].'" class="btn btn-success btn-sm update"><i class="fa fa-eye"></i> View</a>';	
			$ticketRows[] = '<button type="button" name="update" id="'.$ticket["id"].'" class="btn btn-info btn-sm update" '.$disbaled.'><i class="fa fa-edit"></i> Edit</button>';
			$ticketRows[] = '<button type="button" name="delete" id="'.$ticket["id"].'" class="btn btn-danger btn-sm delete"  '.$disbaled.'><i class="fa fa-times"></i> Close</button>';
			$ticketData[] = $ticketRows;
		}
		$output = array(
			"draw"				=>	intval($_POST["draw"]),
			"recordsTotal"  	=>  $numRows,
			"recordsFiltered" 	=> 	$numRows,
			"data"    			=> 	$ticketData
		);
		echo json_encode($output);
	}	
	public function getRepliedTitle($title) {
		$title = $title.'<span class="answered">Answered</span>';
		return $title; 		
	}
	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>';
		}
	}	
	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, MYSQLI_ASSOC);
			echo json_encode($row);
		}
	}
	public function updateTicket() {
		if($_POST['ticketId']) {	
			$updateQuery = "UPDATE ".$this->ticketTable." 
			SET title = '".$_POST["subject"]."', department = '".$_POST["department"]."', init_msg = '".$_POST["message"]."', resolved = '".$_POST["status"]."'
			WHERE id ='".$_POST["ticketId"]."'";
			$isUpdated = mysqli_query($this->dbConnect, $updateQuery);		
		}	
	}		
	public function closeTicket(){
		if($_POST["ticketId"]) {
			$sqlDelete = "UPDATE ".$this->ticketTable." 
				SET resolved = '1'
				WHERE id = '".$_POST["ticketId"]."'";		
			mysqli_query($this->dbConnect, $sqlDelete);		
		}
	}	
	public function getDepartments() {       
		$sqlQuery = "SELECT * FROM ".$this->departmentsTable;
		$result = mysqli_query($this->dbConnect, $sqlQuery);
		while($department = mysqli_fetch_assoc($result) ) {       
            echo '<option value="' . $department['id'] . '">' . $department['name']  . '</option>';           
        }
    }	    
    public function ticketInfo($id) {  		
		$sqlQuery = "SELECT t.id, t.uniqid, t.title, t.user, t.init_msg as message, t.date, t.last_reply, t.resolved, u.name as creater, d.name as department 
			FROM ".$this->ticketTable." t 
			LEFT JOIN ".$this->userTable." u ON t.user = u.id 
			LEFT JOIN ".$this->departmentsTable." d ON t.department = d.id 
			WHERE t.uniqid = '".$id."'";	
		$result = mysqli_query($this->dbConnect, $sqlQuery);
        $tickets = mysqli_fetch_assoc($result);
        return $tickets;        
    }    
	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);
		} 
	}	
	public function getTicketReplies($id) {  		
		$sqlQuery = "SELECT r.id, r.text as message, r.date, u.name as creater, d.name as department, u.user_type  
			FROM ".$this->ticketRepliesTable." r
			LEFT JOIN ".$this->ticketTable." t ON r.ticket_id = t.id
			LEFT JOIN ".$this->userTable." u ON r.user = u.id 
			LEFT JOIN ".$this->departmentsTable." 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;
    }
	public function updateTicketReadStatus($ticketId) {
		$updateField = '';
		if(isset($_SESSION["admin"])) {
			$updateField = "admin_read = '1'";
		} else {
			$updateField = "user_read = '1'";
		}
		$updateTicket = "UPDATE ".$this->ticketTable." 
			SET $updateField
			WHERE id = '".$ticketId."'";				
		mysqli_query($this->dbConnect, $updateTicket);
	}
}    
?>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

<?php

/**

* @package Ticket class

* @author TechArise Team

* @email info@techarise.com

**/

class Tickets extends Database {

private $ticketTable = 'tickets';

private $ticketRepliesTable = 'ticket_replies';

private $departmentsTable = 'department';

private $userTable = 'users';

private $dbConnect = false;

public function __construct(){

$this->dbConnect = $this->dbConnect();

}

public function gellAllTicket() {

$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.name as creater, d.name as department, u.user_type, t.user, t.user_read, t.admin_read

FROM ".$this->ticketTable." t

LEFT JOIN ".$this->userTable." u ON t.user = u.id

LEFT JOIN ".$this->departmentsTable." 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="view_ticket.php?id='.$ticket["uniqid"].'" class="btn btn-success btn-sm update"><i class="fa fa-eye"></i> View</a>';

$ticketRows[] = '<button type="button" name="update" id="'.$ticket["id"].'" class="btn btn-info btn-sm update" '.$disbaled.'><i class="fa fa-edit"></i> Edit</button>';

$ticketRows[] = '<button type="button" name="delete" id="'.$ticket["id"].'" class="btn btn-danger btn-sm delete" '.$disbaled.'><i class="fa fa-times"></i> Close</button>';

$ticketData[] = $ticketRows;

}

$output = array(

"draw" => intval($_POST["draw"]),

"recordsTotal" => $numRows,

"recordsFiltered" => $numRows,

"data" => $ticketData

);

echo json_encode($output);

}

public function getRepliedTitle($title) {

$title = $title.'<span class="answered">Answered</span>';

return $title;

}

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

}

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, MYSQLI_ASSOC);

echo json_encode($row);

}

public function updateTicket() {

if($_POST['ticketId']) {

$updateQuery = "UPDATE ".$this->ticketTable."

SET title = '".$_POST["subject"]."', department = '".$_POST["department"]."', init_msg = '".$_POST["message"]."', resolved = '".$_POST["status"]."'

WHERE id ='".$_POST["ticketId"]."'";

$isUpdated = mysqli_query($this->dbConnect, $updateQuery);

}

public function closeTicket(){

if($_POST["ticketId"]) {

$sqlDelete = "UPDATE ".$this->ticketTable."

SET resolved = '1'

WHERE id = '".$_POST["ticketId"]."'";

mysqli_query($this->dbConnect, $sqlDelete);

}

public function getDepartments() {

$sqlQuery = "SELECT * FROM ".$this->departmentsTable;

$result = mysqli_query($this->dbConnect, $sqlQuery);

while($department = mysqli_fetch_assoc($result) ) {

echo '<option value="' . $department['id'] . '">' . $department['name'] . '</option>';

}

public function ticketInfo($id) {

$sqlQuery = "SELECT t.id, t.uniqid, t.title, t.user, t.init_msg as message, t.date, t.last_reply, t.resolved, u.name as creater, d.name as department

FROM ".$this->ticketTable." t

LEFT JOIN ".$this->userTable." u ON t.user = u.id

LEFT JOIN ".$this->departmentsTable." d ON t.department = d.id

WHERE t.uniqid = '".$id."'";

$result = mysqli_query($this->dbConnect, $sqlQuery);

$tickets = mysqli_fetch_assoc($result);

return $tickets;

}

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

}

public function getTicketReplies($id) {

$sqlQuery = "SELECT r.id, r.text as message, r.date, u.name as creater, d.name as department, u.user_type

FROM ".$this->ticketRepliesTable." r

LEFT JOIN ".$this->ticketTable." t ON r.ticket_id = t.id

LEFT JOIN ".$this->userTable." u ON r.user = u.id

LEFT JOIN ".$this->departmentsTable." 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;

}

public function updateTicketReadStatus($ticketId) {

$updateField = '';

if(isset($_SESSION["admin"])) {

$updateField = "admin_read = '1'";

} else {

$updateField = "user_read = '1'";

}

$updateTicket = "UPDATE ".$this->ticketTable."

SET $updateField

WHERE id = '".$ticketId."'";

mysqli_query($this->dbConnect, $updateTicket);

}

Step 5: Create a class file

Create a class file named Department.php inside “class/” folder.

The class handles the Department process.
__construct() – Loads the required class.
listDepartment() – get departments list from database
getDepartmentDetails() – get department record from database
gellAllTicket() – get tiecket record from database
insert() – Insert new recored in database
update() – update department record from database
delete() – Delete department record from database

<?php
/**
 * @package Department class
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 *   
 **/
class Department extends Database {  
    
	private $departmentsTable = 'department';
	
	private $dbConnect = false;
	public function __construct(){		
        $this->dbConnect = $this->dbConnect();
    } 
	public function listDepartment(){
			 			 
		$sqlQuery = "SELECT id, name, status
			FROM ".$this->departmentsTable;
			
		if(!empty($_POST["search"]["value"])){
			$sqlQuery .= ' (id LIKE "%'.$_POST["search"]["value"].'%" ';					
			$sqlQuery .= ' OR name LIKE "%'.$_POST["search"]["value"].'%" ';
			$sqlQuery .= ' OR status LIKE "%'.$_POST["search"]["value"].'%" ';					
		}
		if(!empty($_POST["order"])){
			$sqlQuery .= ' ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
		} else {
			$sqlQuery .= ' ORDER BY id DESC ';
		}
		if($_POST["length"] != -1){
			$sqlQuery .= ' LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
		}	
		
		$result = mysqli_query($this->dbConnect, $sqlQuery);
		$numRows = mysqli_num_rows($result);
		$departmentData = array();	
		while( $department = mysqli_fetch_assoc($result) ) {		
			$departmentRows = array();			
			$status = '';
			if($department['status'] == 1)	{
				$status = '<span class="label label-success">Enabled</span>';
			} else if($department['status'] == 0) {
				$status = '<span class="label label-danger">Disabled</span>';
			}	
			
			$departmentRows[] = $department['id'];
			$departmentRows[] = $department['name'];			
			$departmentRows[] = $status;
				
			$departmentRows[] = '<button type="button" name="update" id="'.$department["id"].'" class="btn btn-info btn-sm update"><i class="fa fa-edit"></i> Edit</button>';
			$departmentRows[] = '<button type="button" name="delete" id="'.$department["id"].'" class="btn btn-danger btn-sm delete"><i class="fa fa-times"></i> Delete</button>';
			$departmentData[] = $departmentRows;
		}
		$output = array(
			"draw"				=>	intval($_POST["draw"]),
			"recordsTotal"  	=>  $numRows,
			"recordsFiltered" 	=> 	$numRows,
			"data"    			=> 	$departmentData
		);
		echo json_encode($output);
	}	
	
		
	public function getDepartmentDetails(){		
		if($this->departmentId) {		
			$sqlQuery = "
				SELECT id, name, status 
				FROM ".$this->departmentsTable." 
				WHERE id = '".$this->departmentId."'";
			$result = mysqli_query($this->dbConnect, $sqlQuery);	
			$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
			echo json_encode($row);
		}		
	}
	
	public function insert() {      
		if($this->department) {		              
			$this->department = strip_tags($this->department);              
			$queryInsert = "INSERT INTO ".$this->departmentsTable." (name, status) 
			VALUES('".$this->department."', '".$this->status."')";			
			mysqli_query($this->dbConnect, $queryInsert);			
		}
	}

	public function update() {      
		if($this->departmentId && $this->department) {		              
			$this->department = strip_tags($this->department);              
			$queryUpdate = "
				UPDATE ".$this->departmentsTable." 
				SET name = '".$this->department."', status = '".$this->status."' 
				WHERE id = '".$this->departmentId."'";				
			mysqli_query($this->dbConnect, $queryUpdate);			
		}
	}	
	
	public function delete() {      
		if($this->departmentId) {		          
			$queryUpdate = "
				DELETE FROM ".$this->departmentsTable." 
				WHERE id = '".$this->departmentId."'";				
			mysqli_query($this->dbConnect, $queryUpdate);			
		}
	}
	
}
?>

100

101

102

103

104

105

106

107

108

109

<?php

/**

* @package Department class

* @author TechArise Team

* @email info@techarise.com

**/

class Department extends Database {

private $departmentsTable = 'department';

private $dbConnect = false;

public function __construct(){

$this->dbConnect = $this->dbConnect();

}

public function listDepartment(){

$sqlQuery = "SELECT id, name, status

FROM ".$this->departmentsTable;

if(!empty($_POST["search"]["value"])){

$sqlQuery .= ' (id LIKE "%'.$_POST["search"]["value"].'%" ';

$sqlQuery .= ' OR name LIKE "%'.$_POST["search"]["value"].'%" ';

$sqlQuery .= ' OR status LIKE "%'.$_POST["search"]["value"].'%" ';

}

if(!empty($_POST["order"])){

$sqlQuery .= ' ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';

} else {

$sqlQuery .= ' ORDER BY id DESC ';

}

if($_POST["length"] != -1){

$sqlQuery .= ' LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];

}

$result = mysqli_query($this->dbConnect, $sqlQuery);

$numRows = mysqli_num_rows($result);

$departmentData = array();

while( $department = mysqli_fetch_assoc($result) ) {

$departmentRows = array();

$status = '';

if($department['status'] == 1) {

$status = '<span class="label label-success">Enabled</span>';

} else if($department['status'] == 0) {

$status = '<span class="label label-danger">Disabled</span>';

}

$departmentRows[] = $department['id'];

$departmentRows[] = $department['name'];

$departmentRows[] = $status;

$departmentRows[] = '<button type="button" name="update" id="'.$department["id"].'" class="btn btn-info btn-sm update"><i class="fa fa-edit"></i> Edit</button>';

$departmentRows[] = '<button type="button" name="delete" id="'.$department["id"].'" class="btn btn-danger btn-sm delete"><i class="fa fa-times"></i> Delete</button>';

$departmentData[] = $departmentRows;

}

$output = array(

"draw" => intval($_POST["draw"]),

"recordsTotal" => $numRows,

"recordsFiltered" => $numRows,

"data" => $departmentData

);

echo json_encode($output);

}

public function getDepartmentDetails(){

if($this->departmentId) {

$sqlQuery = "

SELECT id, name, status

FROM ".$this->departmentsTable."

WHERE id = '".$this->departmentId."'";

$result = mysqli_query($this->dbConnect, $sqlQuery);

$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

echo json_encode($row);

}

public function insert() {

if($this->department) {

$this->department = strip_tags($this->department);

$queryInsert = "INSERT INTO ".$this->departmentsTable." (name, status)

VALUES('".$this->department."', '".$this->status."')";

mysqli_query($this->dbConnect, $queryInsert);

}

public function update() {

if($this->departmentId && $this->department) {

$this->department = strip_tags($this->department);

$queryUpdate = "

UPDATE ".$this->departmentsTable."

SET name = '".$this->department."', status = '".$this->status."'

WHERE id = '".$this->departmentId."'";

mysqli_query($this->dbConnect, $queryUpdate);

}

public function delete() {

if($this->departmentId) {

$queryUpdate = "

DELETE FROM ".$this->departmentsTable."

WHERE id = '".$this->departmentId."'";

mysqli_query($this->dbConnect, $queryUpdate);

}

Step 6: Create a class file

Create a class file named Time.php inside “class/” folder.

<?php
/**
 * @package Time class
 *
 * @author TechArise Team
 *
 * @email  info@techarise.com
 *   
 **/
class Time extends Database
{
    private $dbConnect = false;
    public function __construct()
    {
        $this->dbConnect = $this->dbConnect();
    }
    public function ago($time)
    {
        $periods = array("second", "minute", "hour", "day", "week", "month", "year", "decade");
        $lengths = array("60", "60", "24", "7", "4.35", "12", "10");
        $now = time();
        $difference = $now - $time;
        $tense = 'ago';
        for ($j = 0; $difference >= $lengths[$j] && $j < count($lengths) - 1; $j++) {
            $difference /= $lengths[$j];
        }
        $difference = round($difference);
        if ($difference != 1) {
            $periods[$j] .= "s";
        }
        return $difference . " " . $periods[$j] . " ago";
    }
}
?>

<?php

/**

* @package Time class

* @author TechArise Team

* @email info@techarise.com

**/

class Time extends Database

{

private $dbConnect = false;

public function __construct()

{

$this->dbConnect = $this->dbConnect();

}

public function ago($time)

{

$periods = array("second", "minute", "hour", "day", "week", "month", "year", "decade");

$lengths = array("60", "60", "24", "7", "4.35", "12", "10");

$now = time();

$difference = $now - $time;

$tense = 'ago';

for ($j = 0; $difference >= $lengths[$j] && $j < count($lengths) - 1; $j++) {

$difference /= $lengths[$j];

}

$difference = round($difference);

if ($difference != 1) {

$periods[$j] .= "s";

}

return $difference . " " . $periods[$j] . " ago";

}

Demo Download

Also Read: Login with Google Account using PHP