The issue is that the form submission is being processed again when you reload the page because the browser resubmits the POST data. Here's the fixed version that prevents duplicate ticket creation on page reload:
The key changes made to fix the duplicate ticket issue:
header("Location: tickets.php");
exit();
header("Location: tickets.php");
exit();
This prevents the form from being resubmitted when the page is reloaded, as the browser will now make a GET request to tickets.php instead of resending the POST data.
The same fix was applied to both the ticket creation and update forms to ensure consistent behavior.
This is a common pattern in web development known as the Post/Redirect/Get (PRG) pattern, which prevents duplicate form submissions when users refresh the page.
The issue is that when you reload the page after submitting the form, the browser resubmits the POST data. Here's the fixed code with proper redirect-after-POST pattern:
<?php
// Start session for custom authentication
session_start();
// Database connection
$host = 'localhost';
$dbname = 'helpdesk';
$username = 'root';
$password = '';
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// Custom security check
if (empty($_SESSION['user_id'])) {
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Get current user data
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$_SESSION['user_id']]);
$current_user = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$current_user) {
session_destroy();
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Base URL for ticket system
$base_url = 'http://localhost/helpdesk/?page_id=70';
// Check if viewing/editing ticket
$viewing_ticket = isset($_GET['ticket_id']);
$editing_ticket = $viewing_ticket && isset($_GET['action']) && $_GET['action'] === 'edit';
$ticket_id = $viewing_ticket ? (int)$_GET['ticket_id'] : 0;
// Handle form submissions
$message = '';
$message_type = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (isset($_POST['create_ticket'])) {
$subject = htmlspecialchars($_POST['subject']);
$description = htmlspecialchars($_POST['description']);
$priority = htmlspecialchars($_POST['priority']);
$department_id = (int)$_POST['department_id'];
try {
$ticket_number = 'TKT-' . date('Ymd-His');
$stmt = $pdo->prepare("INSERT INTO tickets
(ticket_number, subject, description, priority, department_id, created_by, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, 'open', NOW(), NOW())");
$stmt->execute([
$ticket_number,
$subject,
$description,
$priority,
$department_id,
$current_user['id']
]);
$ticket_id = $pdo->lastInsertId();
// Add initial message
$stmt = $pdo->prepare("INSERT INTO ticket_messages
(ticket_id, user_id, message, created_at)
VALUES (?, ?, ?, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$description
]);
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$message = 'Error creating ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['update_status'])) {
$ticket_id = (int)$_POST['ticket_id'];
$status = htmlspecialchars($_POST['status']);
try {
$stmt = $pdo->prepare("UPDATE tickets SET status = ?, updated_at = NOW() WHERE id = ?");
$stmt->execute([$status, $ticket_id]);
// Add status change message
$stmt = $pdo->prepare("INSERT INTO ticket_messages
(ticket_id, user_id, message, created_at)
VALUES (?, ?, ?, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
"Status changed to: " . ucfirst($status)
]);
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$message = 'Error updating ticket status: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['assign_ticket'])) {
$ticket_id = (int)$_POST['ticket_id'];
$assign_to = (int)$_POST['assign_to'];
try {
$stmt = $pdo->prepare("UPDATE tickets SET assigned_to = ?, status = 'assigned', updated_at = NOW() WHERE id = ?");
$stmt->execute([$assign_to, $ticket_id]);
// Add assignment message
$stmt = $pdo->prepare("INSERT INTO ticket_messages
(ticket_id, user_id, message, created_at)
VALUES (?, ?, ?, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
"Ticket assigned to staff ID: " . $assign_to
]);
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$message = 'Error assigning ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['add_message'])) {
$ticket_id = (int)$_POST['ticket_id'];
$message_text = htmlspecialchars($_POST['message']);
try {
$stmt = $pdo->prepare("INSERT INTO ticket_messages
(ticket_id, user_id, message, created_at)
VALUES (?, ?, ?, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$message_text
]);
// Update ticket's updated_at
$stmt = $pdo->prepare("UPDATE tickets SET updated_at = NOW() WHERE id = ?");
$stmt->execute([$ticket_id]);
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$message = 'Error adding message: ' . $e->getMessage();
$message_type = 'error';
}
}
}
// Get ticket details if viewing a ticket
if ($viewing_ticket) {
// Get ticket details
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.id = ?
");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch(PDO::FETCH_ASSOC);
// Check if ticket exists and user has permission
if (!$ticket) {
die("Ticket not found");
}
if ($current_user['role'] === 'user' && $ticket['created_by'] != $current_user['id']) {
die("You don't have permission to view this ticket");
}
// Get ticket messages
$stmt = $pdo->prepare("
SELECT m.*, CONCAT(u.first_name, ' ', u.last_name) as user_name, u.role as user_role
FROM ticket_messages m
JOIN users u ON m.user_id = u.id
WHERE m.ticket_id = ?
ORDER BY m.created_at ASC
");
$stmt->execute([$ticket_id]);
$messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get all necessary data for ticket list
if (!$viewing_ticket) {
try {
// Get tickets based on role
if ($current_user['role'] === 'admin' || $current_user['role'] === 'staff') {
$tickets = $pdo->query("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
ORDER BY t.created_at DESC
")->fetchAll(PDO::FETCH_ASSOC);
} else {
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.created_by = ?
ORDER BY t.created_at DESC
");
$stmt->execute([$current_user['id']]);
$tickets = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get departments for dropdown
$departments = $pdo->query("SELECT * FROM departments")->fetchAll(PDO::FETCH_ASSOC);
// Get staff for assignment dropdown
$staff_members = $pdo->query("
SELECT id, CONCAT(first_name, ' ', last_name) as name
FROM users
WHERE role IN ('staff', 'admin')
")->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
die("Error fetching data: " . $e->getMessage());
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title><?= $viewing_ticket ? 'Ticket Details' : 'Ticket System' ?></title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
<style>
/* Your existing CSS styles here */
</style>
</head>
<body>
<div class="helpdesk-container">
<!-- Your existing HTML structure here -->
</div>
<script>
document.addEventListener('DOMContentLoaded', function() {
// AJAX form submissions
document.querySelectorAll('.status-update-form select, .assign-form select').forEach(select => {
select.addEventListener('change', function() {
const form = this.closest('form');
fetch('', {
method: 'POST',
body: new FormData(form)
})
.then(response => {
if (response.ok) {
window.location.reload();
} else {
alert('Error updating ticket');
}
})
.catch(error => {
console.error('Error:', error);
alert('Error updating ticket');
});
});
});
});
</script>
</body>
</html>
<?php
// Start session for custom authentication
session_start();
// Database connection
$host = 'localhost';
$dbname = 'helpdesk';
$username = 'root';
$password = '';
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// Custom security check
if (empty($_SESSION['user_id'])) {
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Get current user data
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$_SESSION['user_id']]);
$current_user = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$current_user) {
session_destroy();
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Base URL for ticket system
$base_url = 'http://localhost/helpdesk/?page_id=70';
// Check if viewing/editing ticket
$viewing_ticket = isset($_GET['ticket_id']);
$editing_ticket = $viewing_ticket && isset($_GET['action']) && $_GET['action'] === 'edit';
$ticket_id = $viewing_ticket ? (int)$_GET['ticket_id'] : 0;
// Handle form submissions
$message = '';
$message_type = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (isset($_POST['create_ticket'])) {
$subject = htmlspecialchars($_POST['subject']);
$description = htmlspecialchars($_POST['description']);
$priority = htmlspecialchars($_POST['priority']);
$department_id = (int)$_POST['department_id'];
try {
$ticket_number = 'TKT-' . date('Ymd-His');
$stmt = $pdo->prepare("INSERT INTO tickets
(ticket_number, subject, description, priority, department_id, created_by, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, 'open', NOW(), NOW())");
$stmt->execute([
$ticket_number,
$subject,
$description,
$priority,
$department_id,
$current_user['id']
]);
$ticket_id = $pdo->lastInsertId();
// Add initial message
$stmt = $pdo->prepare("INSERT INTO ticket_messages
(ticket_id, user_id, message, created_at)
VALUES (?, ?, ?, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$description
]);
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$message = 'Error creating ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['update_status'])) {
$ticket_id = (int)$_POST['ticket_id'];
$status = htmlspecialchars($_POST['status']);
try {
$stmt = $pdo->prepare("UPDATE tickets SET status = ?, updated_at = NOW() WHERE id = ?");
$stmt->execute([$status, $ticket_id]);
// Add status change message
$stmt = $pdo->prepare("INSERT INTO ticket_messages
(ticket_id, user_id, message, created_at)
VALUES (?, ?, ?, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
"Status changed to: " . ucfirst($status)
]);
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$message = 'Error updating ticket status: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['assign_ticket'])) {
$ticket_id = (int)$_POST['ticket_id'];
$assign_to = (int)$_POST['assign_to'];
try {
$stmt = $pdo->prepare("UPDATE tickets SET assigned_to = ?, status = 'assigned', updated_at = NOW() WHERE id = ?");
$stmt->execute([$assign_to, $ticket_id]);
// Add assignment message
$stmt = $pdo->prepare("INSERT INTO ticket_messages
(ticket_id, user_id, message, created_at)
VALUES (?, ?, ?, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
"Ticket assigned to staff ID: " . $assign_to
]);
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$message = 'Error assigning ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['add_message'])) {
$ticket_id = (int)$_POST['ticket_id'];
$message_text = htmlspecialchars($_POST['message']);
try {
$stmt = $pdo->prepare("INSERT INTO ticket_messages
(ticket_id, user_id, message, created_at)
VALUES (?, ?, ?, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$message_text
]);
// Update ticket's updated_at
$stmt = $pdo->prepare("UPDATE tickets SET updated_at = NOW() WHERE id = ?");
$stmt->execute([$ticket_id]);
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$message = 'Error adding message: ' . $e->getMessage();
$message_type = 'error';
}
}
}
// Get ticket details if viewing a ticket
if ($viewing_ticket) {
// Get ticket details
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.id = ?
");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch(PDO::FETCH_ASSOC);
// Check if ticket exists and user has permission
if (!$ticket) {
die("Ticket not found");
}
if ($current_user['role'] === 'user' && $ticket['created_by'] != $current_user['id']) {
die("You don't have permission to view this ticket");
}
// Get ticket messages
$stmt = $pdo->prepare("
SELECT m.*, CONCAT(u.first_name, ' ', u.last_name) as user_name, u.role as user_role
FROM ticket_messages m
JOIN users u ON m.user_id = u.id
WHERE m.ticket_id = ?
ORDER BY m.created_at ASC
");
$stmt->execute([$ticket_id]);
$messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get all necessary data for ticket list
if (!$viewing_ticket) {
try {
// Get tickets based on role
if ($current_user['role'] === 'admin' || $current_user['role'] === 'staff') {
$tickets = $pdo->query("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
ORDER BY t.created_at DESC
")->fetchAll(PDO::FETCH_ASSOC);
} else {
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.created_by = ?
ORDER BY t.created_at DESC
");
$stmt->execute([$current_user['id']]);
$tickets = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get departments for dropdown
$departments = $pdo->query("SELECT * FROM departments")->fetchAll(PDO::FETCH_ASSOC);
// Get staff for assignment dropdown
$staff_members = $pdo->query("
SELECT id, CONCAT(first_name, ' ', last_name) as name
FROM users
WHERE role IN ('staff', 'admin')
")->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
die("Error fetching data: " . $e->getMessage());
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title><?= $viewing_ticket ? 'Ticket Details' : 'Ticket System' ?></title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
<style>
/* Your existing CSS styles here */
</style>
</head>
<body>
<div class="helpdesk-container">
<!-- Your existing HTML structure here -->
</div>
<script>
document.addEventListener('DOMContentLoaded', function() {
// AJAX form submissions
document.querySelectorAll('.status-update-form select, .assign-form select').forEach(select => {
select.addEventListener('change', function() {
const form = this.closest('form');
fetch('', {
method: 'POST',
body: new FormData(form)
})
.then(response => {
if (response.ok) {
window.location.reload();
} else {
alert('Error updating ticket');
}
})
.catch(error => {
console.error('Error:', error);
alert('Error updating ticket');
});
});
});
});
</script>
</body>
</html>
The key changes made to fix the duplicate ticket issue:
header("Location: ...") followed by exitThe rest of your code remains the same, just with this critical fix for the form submission handling. This is a standard pattern called "Post/Redirect/Get" (PRG) that prevents duplicate form submissions.
For the create ticket form specifically, after successfully creating the ticket, it now redirects to the ticket view page for that new ticket, which prevents the form from being resubmitted on refresh._
I'll fix both issues - the SQL error and the duplicate ticket creation on refresh. Here's the corrected code:
<?php
// Start session for custom authentication
session_start();
// Database connection
$host = 'localhost';
$dbname = 'helpdesk';
$username = 'root';
$password = '';
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// Custom security check
if (empty($_SESSION['user_id'])) {
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Get current user data
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$_SESSION['user_id']]);
$current_user = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$current_user) {
session_destroy();
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Base URL for ticket system
$base_url = 'http://localhost/helpdesk/?page_id=70';
// Check if viewing/editing ticket
$viewing_ticket = isset($_GET['ticket_id']);
$editing_ticket = $viewing_ticket && isset($_GET['action']) && $_GET['action'] === 'edit';
$ticket_id = $viewing_ticket ? (int)$_GET['ticket_id'] : 0;
// Handle form submissions
$message = '';
$message_type = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (isset($_POST['create_ticket'])) {
$subject = htmlspecialchars($_POST['subject']);
$description = htmlspecialchars($_POST['description']);
$priority = htmlspecialchars($_POST['priority']);
$department_id = (int)$_POST['department_id'];
try {
// Start transaction
$pdo->beginTransaction();
$ticket_number = 'TKT-' . date('Ymd-His');
$stmt = $pdo->prepare("INSERT INTO tickets
(ticket_number, subject, description, priority, department_id, created_by, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, 'open', NOW(), NOW())");
$stmt->execute([
$ticket_number,
$subject,
$description,
$priority,
$department_id,
$current_user['id']
]);
$ticket_id = $pdo->lastInsertId();
// Add initial message using the correct column names
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
// For initial message, set recipient to NULL or department head if needed
$stmt->execute([
$ticket_id,
$current_user['id'],
NULL, // Or set to department head if you have that
$description
]);
// Commit transaction
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error creating ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['update_status'])) {
$ticket_id = (int)$_POST['ticket_id'];
$status = htmlspecialchars($_POST['status']);
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE tickets SET status = ?, updated_at = NOW() WHERE id = ?");
$stmt->execute([$status, $ticket_id]);
// Add status change message using correct column names
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
NULL, // Or set to appropriate recipient
"Status changed to: " . ucfirst($status)
]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error updating ticket status: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['assign_ticket'])) {
$ticket_id = (int)$_POST['ticket_id'];
$assign_to = (int)$_POST['assign_to'];
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE tickets SET assigned_to = ?, status = 'assigned', updated_at = NOW() WHERE id = ?");
$stmt->execute([$assign_to, $ticket_id]);
// Add assignment message using correct column names
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$assign_to, // Recipient is the assigned staff
"Ticket assigned to you"
]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error assigning ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['add_message'])) {
$ticket_id = (int)$_POST['ticket_id'];
$message_text = htmlspecialchars($_POST['message']);
try {
$pdo->beginTransaction();
// Get the assigned staff for this ticket to set as recipient
$stmt = $pdo->prepare("SELECT assigned_to FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
$recipient_id = $ticket['assigned_to'] ?? NULL;
// Add message using correct column names
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
$message_text
]);
// Update ticket's updated_at
$stmt = $pdo->prepare("UPDATE tickets SET updated_at = NOW() WHERE id = ?");
$stmt->execute([$ticket_id]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error adding message: ' . $e->getMessage();
$message_type = 'error';
}
}
}
// Get ticket details if viewing a ticket
if ($viewing_ticket) {
// Get ticket details
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.id = ?
");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch(PDO::FETCH_ASSOC);
// Check if ticket exists and user has permission
if (!$ticket) {
die("Ticket not found");
}
if ($current_user['role'] === 'user' && $ticket['created_by'] != $current_user['id']) {
die("You don't have permission to view this ticket");
}
// Get ticket messages with correct column names
$stmt = $pdo->prepare("
SELECT m.*,
CONCAT(s.first_name, ' ', s.last_name) as sender_name,
s.role as sender_role,
CONCAT(r.first_name, ' ', r.last_name) as recipient_name
FROM messages m
LEFT JOIN users s ON m.sender_id = s.id
LEFT JOIN users r ON m.recipient_id = r.id
WHERE m.ticket_id = ?
ORDER BY m.created_at ASC
");
$stmt->execute([$ticket_id]);
$messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get all necessary data for ticket list
if (!$viewing_ticket) {
try {
// Get tickets based on role
if ($current_user['role'] === 'admin' || $current_user['role'] === 'staff') {
$tickets = $pdo->query("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
ORDER BY t.created_at DESC
")->fetchAll(PDO::FETCH_ASSOC);
} else {
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.created_by = ?
ORDER BY t.created_at DESC
");
$stmt->execute([$current_user['id']]);
$tickets = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get departments for dropdown
$departments = $pdo->query("SELECT * FROM departments")->fetchAll(PDO::FETCH_ASSOC);
// Get staff for assignment dropdown
$staff_members = $pdo->query("
SELECT id, CONCAT(first_name, ' ', last_name) as name
FROM users
WHERE role IN ('staff', 'admin')
")->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
die("Error fetching data: " . $e->getMessage());
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title><?= $viewing_ticket ? 'Ticket Details' : 'Ticket System' ?></title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
<style>
/* Your existing CSS styles here */
</style>
</head>
<body>
<div class="helpdesk-container">
<!-- Your existing HTML structure here -->
</div>
<script>
document.addEventListener('DOMContentLoaded', function() {
// AJAX form submissions
document.querySelectorAll('.status-update-form select, .assign-form select').forEach(select => {
select.addEventListener('change', function() {
const form = this.closest('form');
fetch('', {
method: 'POST',
body: new FormData(form)
})
.then(response => {
if (response.ok) {
window.location.reload();
} else {
alert('Error updating ticket');
}
})
.catch(error => {
console.error('Error:', error);
alert('Error updating ticket');
});
});
});
});
</script>
</body>
</html>
<?php
// Start session for custom authentication
session_start();
// Database connection
$host = 'localhost';
$dbname = 'helpdesk';
$username = 'root';
$password = '';
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// Custom security check
if (empty($_SESSION['user_id'])) {
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Get current user data
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$_SESSION['user_id']]);
$current_user = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$current_user) {
session_destroy();
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Base URL for ticket system
$base_url = 'http://localhost/helpdesk/?page_id=70';
// Check if viewing/editing ticket
$viewing_ticket = isset($_GET['ticket_id']);
$editing_ticket = $viewing_ticket && isset($_GET['action']) && $_GET['action'] === 'edit';
$ticket_id = $viewing_ticket ? (int)$_GET['ticket_id'] : 0;
// Handle form submissions
$message = '';
$message_type = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (isset($_POST['create_ticket'])) {
$subject = htmlspecialchars($_POST['subject']);
$description = htmlspecialchars($_POST['description']);
$priority = htmlspecialchars($_POST['priority']);
$department_id = (int)$_POST['department_id'];
try {
// Start transaction
$pdo->beginTransaction();
$ticket_number = 'TKT-' . date('Ymd-His');
$stmt = $pdo->prepare("INSERT INTO tickets
(ticket_number, subject, description, priority, department_id, created_by, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, 'open', NOW(), NOW())");
$stmt->execute([
$ticket_number,
$subject,
$description,
$priority,
$department_id,
$current_user['id']
]);
$ticket_id = $pdo->lastInsertId();
// Add initial message using the correct column names
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
// For initial message, set recipient to NULL or department head if needed
$stmt->execute([
$ticket_id,
$current_user['id'],
NULL, // Or set to department head if you have that
$description
]);
// Commit transaction
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error creating ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['update_status'])) {
$ticket_id = (int)$_POST['ticket_id'];
$status = htmlspecialchars($_POST['status']);
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE tickets SET status = ?, updated_at = NOW() WHERE id = ?");
$stmt->execute([$status, $ticket_id]);
// Add status change message using correct column names
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
NULL, // Or set to appropriate recipient
"Status changed to: " . ucfirst($status)
]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error updating ticket status: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['assign_ticket'])) {
$ticket_id = (int)$_POST['ticket_id'];
$assign_to = (int)$_POST['assign_to'];
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE tickets SET assigned_to = ?, status = 'assigned', updated_at = NOW() WHERE id = ?");
$stmt->execute([$assign_to, $ticket_id]);
// Add assignment message using correct column names
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$assign_to, // Recipient is the assigned staff
"Ticket assigned to you"
]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error assigning ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['add_message'])) {
$ticket_id = (int)$_POST['ticket_id'];
$message_text = htmlspecialchars($_POST['message']);
try {
$pdo->beginTransaction();
// Get the assigned staff for this ticket to set as recipient
$stmt = $pdo->prepare("SELECT assigned_to FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
$recipient_id = $ticket['assigned_to'] ?? NULL;
// Add message using correct column names
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
$message_text
]);
// Update ticket's updated_at
$stmt = $pdo->prepare("UPDATE tickets SET updated_at = NOW() WHERE id = ?");
$stmt->execute([$ticket_id]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error adding message: ' . $e->getMessage();
$message_type = 'error';
}
}
}
// Get ticket details if viewing a ticket
if ($viewing_ticket) {
// Get ticket details
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.id = ?
");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch(PDO::FETCH_ASSOC);
// Check if ticket exists and user has permission
if (!$ticket) {
die("Ticket not found");
}
if ($current_user['role'] === 'user' && $ticket['created_by'] != $current_user['id']) {
die("You don't have permission to view this ticket");
}
// Get ticket messages with correct column names
$stmt = $pdo->prepare("
SELECT m.*,
CONCAT(s.first_name, ' ', s.last_name) as sender_name,
s.role as sender_role,
CONCAT(r.first_name, ' ', r.last_name) as recipient_name
FROM messages m
LEFT JOIN users s ON m.sender_id = s.id
LEFT JOIN users r ON m.recipient_id = r.id
WHERE m.ticket_id = ?
ORDER BY m.created_at ASC
");
$stmt->execute([$ticket_id]);
$messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get all necessary data for ticket list
if (!$viewing_ticket) {
try {
// Get tickets based on role
if ($current_user['role'] === 'admin' || $current_user['role'] === 'staff') {
$tickets = $pdo->query("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
ORDER BY t.created_at DESC
")->fetchAll(PDO::FETCH_ASSOC);
} else {
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.created_by = ?
ORDER BY t.created_at DESC
");
$stmt->execute([$current_user['id']]);
$tickets = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get departments for dropdown
$departments = $pdo->query("SELECT * FROM departments")->fetchAll(PDO::FETCH_ASSOC);
// Get staff for assignment dropdown
$staff_members = $pdo->query("
SELECT id, CONCAT(first_name, ' ', last_name) as name
FROM users
WHERE role IN ('staff', 'admin')
")->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
die("Error fetching data: " . $e->getMessage());
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title><?= $viewing_ticket ? 'Ticket Details' : 'Ticket System' ?></title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
<style>
/* Your existing CSS styles here */
</style>
</head>
<body>
<div class="helpdesk-container">
<!-- Your existing HTML structure here -->
</div>
<script>
document.addEventListener('DOMContentLoaded', function() {
// AJAX form submissions
document.querySelectorAll('.status-update-form select, .assign-form select').forEach(select => {
select.addEventListener('change', function() {
const form = this.closest('form');
fetch('', {
method: 'POST',
body: new FormData(form)
})
.then(response => {
if (response.ok) {
window.location.reload();
} else {
alert('Error updating ticket');
}
})
.catch(error => {
console.error('Error:', error);
alert('Error updating ticket');
});
});
});
});
</script>
</body>
</html>
Key changes made:
Fixed the SQL error by updating all message-related queries to use the correct column names from your database schema:
user_id to sender_id and added recipient_idticket_messages to messagesis_read fieldImproved the duplicate ticket prevention by:
Updated the message queries to:
Added proper recipient handling for messages:
The duplicate ticket issue should now be completely resolved since we:
The code now matches your database schema exactly while maintaining all the functionality you need._
I'll fix the issue with the recipient_id constraint. Since your database requires recipient_id to be non-null, we need to ensure we always provide a valid recipient when creating messages. Here's the corrected code:
<?php
// Start session for custom authentication
session_start();
// Database connection
$host = 'localhost';
$dbname = 'helpdesk';
$username = 'root';
$password = '';
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// Custom security check
if (empty($_SESSION['user_id'])) {
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Get current user data
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$_SESSION['user_id']]);
$current_user = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$current_user) {
session_destroy();
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Base URL for ticket system
$base_url = 'http://localhost/helpdesk/?page_id=70';
// Check if viewing/editing ticket
$viewing_ticket = isset($_GET['ticket_id']);
$editing_ticket = $viewing_ticket && isset($_GET['action']) && $_GET['action'] === 'edit';
$ticket_id = $viewing_ticket ? (int)$_GET['ticket_id'] : 0;
// Handle form submissions
$message = '';
$message_type = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (isset($_POST['create_ticket'])) {
$subject = htmlspecialchars($_POST['subject']);
$description = htmlspecialchars($_POST['description']);
$priority = htmlspecialchars($_POST['priority']);
$department_id = (int)$_POST['department_id'];
try {
// Start transaction
$pdo->beginTransaction();
$ticket_number = 'TKT-' . date('Ymd-His');
$stmt = $pdo->prepare("INSERT INTO tickets
(ticket_number, subject, description, priority, department_id, created_by, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, 'open', NOW(), NOW())");
$stmt->execute([
$ticket_number,
$subject,
$description,
$priority,
$department_id,
$current_user['id']
]);
$ticket_id = $pdo->lastInsertId();
// Get department head as recipient for initial message
$stmt = $pdo->prepare("SELECT head_id FROM departments WHERE id = ?");
$stmt->execute([$department_id]);
$department = $stmt->fetch();
$recipient_id = $department['head_id'] ?? $current_user['id']; // Fallback to current user if no department head
// Add initial message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
$description
]);
// Commit transaction
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error creating ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['update_status'])) {
$ticket_id = (int)$_POST['ticket_id'];
$status = htmlspecialchars($_POST['status']);
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE tickets SET status = ?, updated_at = NOW() WHERE id = ?");
$stmt->execute([$status, $ticket_id]);
// Get the assigned staff for this ticket to set as recipient
$stmt = $pdo->prepare("SELECT assigned_to FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
$recipient_id = $ticket['assigned_to'] ?? $current_user['id']; // Fallback to current user if not assigned
// Add status change message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
"Status changed to: " . ucfirst($status)
]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error updating ticket status: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['assign_ticket'])) {
$ticket_id = (int)$_POST['ticket_id'];
$assign_to = (int)$_POST['assign_to'];
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE tickets SET assigned_to = ?, status = 'assigned', updated_at = NOW() WHERE id = ?");
$stmt->execute([$assign_to, $ticket_id]);
// Add assignment message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$assign_to, // Recipient is the assigned staff
"Ticket assigned to you"
]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error assigning ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['add_message'])) {
$ticket_id = (int)$_POST['ticket_id'];
$message_text = htmlspecialchars($_POST['message']);
try {
$pdo->beginTransaction();
// Get the assigned staff for this ticket to set as recipient
$stmt = $pdo->prepare("SELECT assigned_to FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
$recipient_id = $ticket['assigned_to'] ?? $current_user['id']; // Fallback to current user if not assigned
// Add message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
$message_text
]);
// Update ticket's updated_at
$stmt = $pdo->prepare("UPDATE tickets SET updated_at = NOW() WHERE id = ?");
$stmt->execute([$ticket_id]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error adding message: ' . $e->getMessage();
$message_type = 'error';
}
}
}
// Get ticket details if viewing a ticket
if ($viewing_ticket) {
// Get ticket details
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.id = ?
");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch(PDO::FETCH_ASSOC);
// Check if ticket exists and user has permission
if (!$ticket) {
die("Ticket not found");
}
if ($current_user['role'] === 'user' && $ticket['created_by'] != $current_user['id']) {
die("You don't have permission to view this ticket");
}
// Get ticket messages with correct column names
$stmt = $pdo->prepare("
SELECT m.*,
CONCAT(s.first_name, ' ', s.last_name) as sender_name,
s.role as sender_role,
CONCAT(r.first_name, ' ', r.last_name) as recipient_name
FROM messages m
LEFT JOIN users s ON m.sender_id = s.id
LEFT JOIN users r ON m.recipient_id = r.id
WHERE m.ticket_id = ?
ORDER BY m.created_at ASC
");
$stmt->execute([$ticket_id]);
$messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get all necessary data for ticket list
if (!$viewing_ticket) {
try {
// Get tickets based on role
if ($current_user['role'] === 'admin' || $current_user['role'] === 'staff') {
$tickets = $pdo->query("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
ORDER BY t.created_at DESC
")->fetchAll(PDO::FETCH_ASSOC);
} else {
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.created_by = ?
ORDER BY t.created_at DESC
");
$stmt->execute([$current_user['id']]);
$tickets = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get departments for dropdown
$departments = $pdo->query("SELECT * FROM departments")->fetchAll(PDO::FETCH_ASSOC);
// Get staff for assignment dropdown
$staff_members = $pdo->query("
SELECT id, CONCAT(first_name, ' ', last_name) as name
FROM users
WHERE role IN ('staff', 'admin')
")->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
die("Error fetching data: " . $e->getMessage());
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title><?= $viewing_ticket ? 'Ticket Details' : 'Ticket System' ?></title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
<style>
/* Your existing CSS styles here */
</style>
</head>
<body>
<div class="helpdesk-container">
<!-- Your existing HTML structure here -->
</div>
<script>
document.addEventListener('DOMContentLoaded', function() {
// AJAX form submissions
document.querySelectorAll('.status-update-form select, .assign-form select').forEach(select => {
select.addEventListener('change', function() {
const form = this.closest('form');
fetch('', {
method: 'POST',
body: new FormData(form)
})
.then(response => {
if (response.ok) {
window.location.reload();
} else {
alert('Error updating ticket');
}
})
.catch(error => {
console.error('Error:', error);
alert('Error updating ticket');
});
});
});
});
</script>
</body>
</html>
<?php
// Start session for custom authentication
session_start();
// Database connection
$host = 'localhost';
$dbname = 'helpdesk';
$username = 'root';
$password = '';
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// Custom security check
if (empty($_SESSION['user_id'])) {
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Get current user data
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$_SESSION['user_id']]);
$current_user = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$current_user) {
session_destroy();
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Base URL for ticket system
$base_url = 'http://localhost/helpdesk/?page_id=70';
// Check if viewing/editing ticket
$viewing_ticket = isset($_GET['ticket_id']);
$editing_ticket = $viewing_ticket && isset($_GET['action']) && $_GET['action'] === 'edit';
$ticket_id = $viewing_ticket ? (int)$_GET['ticket_id'] : 0;
// Handle form submissions
$message = '';
$message_type = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (isset($_POST['create_ticket'])) {
$subject = htmlspecialchars($_POST['subject']);
$description = htmlspecialchars($_POST['description']);
$priority = htmlspecialchars($_POST['priority']);
$department_id = (int)$_POST['department_id'];
try {
// Start transaction
$pdo->beginTransaction();
$ticket_number = 'TKT-' . date('Ymd-His');
$stmt = $pdo->prepare("INSERT INTO tickets
(ticket_number, subject, description, priority, department_id, created_by, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, 'open', NOW(), NOW())");
$stmt->execute([
$ticket_number,
$subject,
$description,
$priority,
$department_id,
$current_user['id']
]);
$ticket_id = $pdo->lastInsertId();
// Get department head as recipient for initial message
$stmt = $pdo->prepare("SELECT head_id FROM departments WHERE id = ?");
$stmt->execute([$department_id]);
$department = $stmt->fetch();
$recipient_id = $department['head_id'] ?? $current_user['id']; // Fallback to current user if no department head
// Add initial message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
$description
]);
// Commit transaction
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error creating ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['update_status'])) {
$ticket_id = (int)$_POST['ticket_id'];
$status = htmlspecialchars($_POST['status']);
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE tickets SET status = ?, updated_at = NOW() WHERE id = ?");
$stmt->execute([$status, $ticket_id]);
// Get the assigned staff for this ticket to set as recipient
$stmt = $pdo->prepare("SELECT assigned_to FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
$recipient_id = $ticket['assigned_to'] ?? $current_user['id']; // Fallback to current user if not assigned
// Add status change message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
"Status changed to: " . ucfirst($status)
]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error updating ticket status: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['assign_ticket'])) {
$ticket_id = (int)$_POST['ticket_id'];
$assign_to = (int)$_POST['assign_to'];
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE tickets SET assigned_to = ?, status = 'assigned', updated_at = NOW() WHERE id = ?");
$stmt->execute([$assign_to, $ticket_id]);
// Add assignment message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$assign_to, // Recipient is the assigned staff
"Ticket assigned to you"
]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error assigning ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['add_message'])) {
$ticket_id = (int)$_POST['ticket_id'];
$message_text = htmlspecialchars($_POST['message']);
try {
$pdo->beginTransaction();
// Get the assigned staff for this ticket to set as recipient
$stmt = $pdo->prepare("SELECT assigned_to FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
$recipient_id = $ticket['assigned_to'] ?? $current_user['id']; // Fallback to current user if not assigned
// Add message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
$message_text
]);
// Update ticket's updated_at
$stmt = $pdo->prepare("UPDATE tickets SET updated_at = NOW() WHERE id = ?");
$stmt->execute([$ticket_id]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error adding message: ' . $e->getMessage();
$message_type = 'error';
}
}
}
// Get ticket details if viewing a ticket
if ($viewing_ticket) {
// Get ticket details
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.id = ?
");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch(PDO::FETCH_ASSOC);
// Check if ticket exists and user has permission
if (!$ticket) {
die("Ticket not found");
}
if ($current_user['role'] === 'user' && $ticket['created_by'] != $current_user['id']) {
die("You don't have permission to view this ticket");
}
// Get ticket messages with correct column names
$stmt = $pdo->prepare("
SELECT m.*,
CONCAT(s.first_name, ' ', s.last_name) as sender_name,
s.role as sender_role,
CONCAT(r.first_name, ' ', r.last_name) as recipient_name
FROM messages m
LEFT JOIN users s ON m.sender_id = s.id
LEFT JOIN users r ON m.recipient_id = r.id
WHERE m.ticket_id = ?
ORDER BY m.created_at ASC
");
$stmt->execute([$ticket_id]);
$messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get all necessary data for ticket list
if (!$viewing_ticket) {
try {
// Get tickets based on role
if ($current_user['role'] === 'admin' || $current_user['role'] === 'staff') {
$tickets = $pdo->query("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
ORDER BY t.created_at DESC
")->fetchAll(PDO::FETCH_ASSOC);
} else {
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.created_by = ?
ORDER BY t.created_at DESC
");
$stmt->execute([$current_user['id']]);
$tickets = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get departments for dropdown
$departments = $pdo->query("SELECT * FROM departments")->fetchAll(PDO::FETCH_ASSOC);
// Get staff for assignment dropdown
$staff_members = $pdo->query("
SELECT id, CONCAT(first_name, ' ', last_name) as name
FROM users
WHERE role IN ('staff', 'admin')
")->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
die("Error fetching data: " . $e->getMessage());
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title><?= $viewing_ticket ? 'Ticket Details' : 'Ticket System' ?></title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
<style>
/* Your existing CSS styles here */
</style>
</head>
<body>
<div class="helpdesk-container">
<!-- Your existing HTML structure here -->
</div>
<script>
document.addEventListener('DOMContentLoaded', function() {
// AJAX form submissions
document.querySelectorAll('.status-update-form select, .assign-form select').forEach(select => {
select.addEventListener('change', function() {
const form = this.closest('form');
fetch('', {
method: 'POST',
body: new FormData(form)
})
.then(response => {
if (response.ok) {
window.location.reload();
} else {
alert('Error updating ticket');
}
})
.catch(error => {
console.error('Error:', error);
alert('Error updating ticket');
});
});
});
});
</script>
</body>
</html>
Key changes made to fix the recipient_id issue:
For ticket creation:
For status updates:
For ticket assignment:
For regular messages:
Added proper error handling:
This ensures that:
The duplicate ticket issue should also be completely resolved since we:
I'll fix the error by ensuring we properly handle the recipient_id for messages. Since your departments table doesn't have a head_id column, we'll need to modify our approach. Here's the corrected code:
<?php
// Start session for custom authentication
session_start();
// Database connection
$host = 'localhost';
$dbname = 'helpdesk';
$username = 'root';
$password = '';
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// Custom security check
if (empty($_SESSION['user_id'])) {
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Get current user data
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$_SESSION['user_id']]);
$current_user = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$current_user) {
session_destroy();
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Base URL for ticket system
$base_url = 'http://localhost/helpdesk/?page_id=70';
// Check if viewing/editing ticket
$viewing_ticket = isset($_GET['ticket_id']);
$editing_ticket = $viewing_ticket && isset($_GET['action']) && $_GET['action'] === 'edit';
$ticket_id = $viewing_ticket ? (int)$_GET['ticket_id'] : 0;
// Handle form submissions
$message = '';
$message_type = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (isset($_POST['create_ticket'])) {
$subject = htmlspecialchars($_POST['subject']);
$description = htmlspecialchars($_POST['description']);
$priority = htmlspecialchars($_POST['priority']);
$department_id = (int)$_POST['department_id'];
try {
// Start transaction
$pdo->beginTransaction();
$ticket_number = 'TKT-' . date('Ymd-His');
$stmt = $pdo->prepare("INSERT INTO tickets
(ticket_number, subject, description, priority, department_id, created_by, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, 'open', NOW(), NOW())");
$stmt->execute([
$ticket_number,
$subject,
$description,
$priority,
$department_id,
$current_user['id']
]);
$ticket_id = $pdo->lastInsertId();
// For initial message, set recipient to admin (or any default user)
// Here we'll get the first admin user as recipient
$stmt = $pdo->prepare("SELECT id FROM users WHERE role = 'admin' LIMIT 1");
$stmt->execute();
$admin = $stmt->fetch();
$recipient_id = $admin['id'] ?? $current_user['id']; // Fallback to current user if no admin found
// Add initial message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
$description
]);
// Commit transaction
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error creating ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['update_status'])) {
$ticket_id = (int)$_POST['ticket_id'];
$status = htmlspecialchars($_POST['status']);
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE tickets SET status = ?, updated_at = NOW() WHERE id = ?");
$stmt->execute([$status, $ticket_id]);
// Get the assigned staff for this ticket to set as recipient
$stmt = $pdo->prepare("SELECT assigned_to FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
$recipient_id = $ticket['assigned_to'] ?? $current_user['id']; // Fallback to current user if not assigned
// Add status change message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
"Status changed to: " . ucfirst($status)
]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error updating ticket status: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['assign_ticket'])) {
$ticket_id = (int)$_POST['ticket_id'];
$assign_to = (int)$_POST['assign_to'];
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE tickets SET assigned_to = ?, status = 'assigned', updated_at = NOW() WHERE id = ?");
$stmt->execute([$assign_to, $ticket_id]);
// Add assignment message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$assign_to, // Recipient is the assigned staff
"Ticket assigned to you"
]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error assigning ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['add_message'])) {
$ticket_id = (int)$_POST['ticket_id'];
$message_text = htmlspecialchars($_POST['message']);
try {
$pdo->beginTransaction();
// Get the assigned staff for this ticket to set as recipient
$stmt = $pdo->prepare("SELECT assigned_to FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
$recipient_id = $ticket['assigned_to'] ?? $current_user['id']; // Fallback to current user if not assigned
// Add message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
$message_text
]);
// Update ticket's updated_at
$stmt = $pdo->prepare("UPDATE tickets SET updated_at = NOW() WHERE id = ?");
$stmt->execute([$ticket_id]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error adding message: ' . $e->getMessage();
$message_type = 'error';
}
}
}
// Get ticket details if viewing a ticket
if ($viewing_ticket) {
// Get ticket details
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.id = ?
");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch(PDO::FETCH_ASSOC);
// Check if ticket exists and user has permission
if (!$ticket) {
die("Ticket not found");
}
if ($current_user['role'] === 'user' && $ticket['created_by'] != $current_user['id']) {
die("You don't have permission to view this ticket");
}
// Get ticket messages with correct column names
$stmt = $pdo->prepare("
SELECT m.*,
CONCAT(s.first_name, ' ', s.last_name) as sender_name,
s.role as sender_role,
CONCAT(r.first_name, ' ', r.last_name) as recipient_name
FROM messages m
LEFT JOIN users s ON m.sender_id = s.id
LEFT JOIN users r ON m.recipient_id = r.id
WHERE m.ticket_id = ?
ORDER BY m.created_at ASC
");
$stmt->execute([$ticket_id]);
$messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get all necessary data for ticket list
if (!$viewing_ticket) {
try {
// Get tickets based on role
if ($current_user['role'] === 'admin' || $current_user['role'] === 'staff') {
$tickets = $pdo->query("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
ORDER BY t.created_at DESC
")->fetchAll(PDO::FETCH_ASSOC);
} else {
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.created_by = ?
ORDER BY t.created_at DESC
");
$stmt->execute([$current_user['id']]);
$tickets = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get departments for dropdown
$departments = $pdo->query("SELECT * FROM departments")->fetchAll(PDO::FETCH_ASSOC);
// Get staff for assignment dropdown
$staff_members = $pdo->query("
SELECT id, CONCAT(first_name, ' ', last_name) as name
FROM users
WHERE role IN ('staff', 'admin')
")->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
die("Error fetching data: " . $e->getMessage());
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title><?= $viewing_ticket ? 'Ticket Details' : 'Ticket System' ?></title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
<style>
/* Your existing CSS styles here */
</style>
</head>
<body>
<div class="helpdesk-container">
<!-- Your existing HTML structure here -->
</div>
<script>
document.addEventListener('DOMContentLoaded', function() {
// AJAX form submissions
document.querySelectorAll('.status-update-form select, .assign-form select').forEach(select => {
select.addEventListener('change', function() {
const form = this.closest('form');
fetch('', {
method: 'POST',
body: new FormData(form)
})
.then(response => {
if (response.ok) {
window.location.reload();
} else {
alert('Error updating ticket');
}
})
.catch(error => {
console.error('Error:', error);
alert('Error updating ticket');
});
});
});
});
</script>
</body>
</html>
<?php
// Start session for custom authentication
session_start();
// Database connection
$host = 'localhost';
$dbname = 'helpdesk';
$username = 'root';
$password = '';
try {
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// Custom security check
if (empty($_SESSION['user_id'])) {
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Get current user data
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$_SESSION['user_id']]);
$current_user = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$current_user) {
session_destroy();
header("Location: http://localhost/helpdesk/?page_id=50");
exit;
}
// Base URL for ticket system
$base_url = 'http://localhost/helpdesk/?page_id=70';
// Check if viewing/editing ticket
$viewing_ticket = isset($_GET['ticket_id']);
$editing_ticket = $viewing_ticket && isset($_GET['action']) && $_GET['action'] === 'edit';
$ticket_id = $viewing_ticket ? (int)$_GET['ticket_id'] : 0;
// Handle form submissions
$message = '';
$message_type = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if (isset($_POST['create_ticket'])) {
$subject = htmlspecialchars($_POST['subject']);
$description = htmlspecialchars($_POST['description']);
$priority = htmlspecialchars($_POST['priority']);
$department_id = (int)$_POST['department_id'];
try {
// Start transaction
$pdo->beginTransaction();
$ticket_number = 'TKT-' . date('Ymd-His');
$stmt = $pdo->prepare("INSERT INTO tickets
(ticket_number, subject, description, priority, department_id, created_by, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, 'open', NOW(), NOW())");
$stmt->execute([
$ticket_number,
$subject,
$description,
$priority,
$department_id,
$current_user['id']
]);
$ticket_id = $pdo->lastInsertId();
// For initial message, set recipient to admin (or any default user)
// Here we'll get the first admin user as recipient
$stmt = $pdo->prepare("SELECT id FROM users WHERE role = 'admin' LIMIT 1");
$stmt->execute();
$admin = $stmt->fetch();
$recipient_id = $admin['id'] ?? $current_user['id']; // Fallback to current user if no admin found
// Add initial message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
$description
]);
// Commit transaction
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error creating ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['update_status'])) {
$ticket_id = (int)$_POST['ticket_id'];
$status = htmlspecialchars($_POST['status']);
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE tickets SET status = ?, updated_at = NOW() WHERE id = ?");
$stmt->execute([$status, $ticket_id]);
// Get the assigned staff for this ticket to set as recipient
$stmt = $pdo->prepare("SELECT assigned_to FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
$recipient_id = $ticket['assigned_to'] ?? $current_user['id']; // Fallback to current user if not assigned
// Add status change message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
"Status changed to: " . ucfirst($status)
]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error updating ticket status: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['assign_ticket'])) {
$ticket_id = (int)$_POST['ticket_id'];
$assign_to = (int)$_POST['assign_to'];
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE tickets SET assigned_to = ?, status = 'assigned', updated_at = NOW() WHERE id = ?");
$stmt->execute([$assign_to, $ticket_id]);
// Add assignment message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$assign_to, // Recipient is the assigned staff
"Ticket assigned to you"
]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error assigning ticket: ' . $e->getMessage();
$message_type = 'error';
}
}
elseif (isset($_POST['add_message'])) {
$ticket_id = (int)$_POST['ticket_id'];
$message_text = htmlspecialchars($_POST['message']);
try {
$pdo->beginTransaction();
// Get the assigned staff for this ticket to set as recipient
$stmt = $pdo->prepare("SELECT assigned_to FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
$recipient_id = $ticket['assigned_to'] ?? $current_user['id']; // Fallback to current user if not assigned
// Add message with valid recipient_id
$stmt = $pdo->prepare("INSERT INTO messages
(ticket_id, sender_id, recipient_id, message, is_read, created_at)
VALUES (?, ?, ?, ?, 0, NOW())");
$stmt->execute([
$ticket_id,
$current_user['id'],
$recipient_id,
$message_text
]);
// Update ticket's updated_at
$stmt = $pdo->prepare("UPDATE tickets SET updated_at = NOW() WHERE id = ?");
$stmt->execute([$ticket_id]);
$pdo->commit();
// Redirect to prevent form resubmission
header("Location: $base_url&ticket_id=$ticket_id");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
$message = 'Error adding message: ' . $e->getMessage();
$message_type = 'error';
}
}
}
// Get ticket details if viewing a ticket
if ($viewing_ticket) {
// Get ticket details
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.id = ?
");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch(PDO::FETCH_ASSOC);
// Check if ticket exists and user has permission
if (!$ticket) {
die("Ticket not found");
}
if ($current_user['role'] === 'user' && $ticket['created_by'] != $current_user['id']) {
die("You don't have permission to view this ticket");
}
// Get ticket messages with correct column names
$stmt = $pdo->prepare("
SELECT m.*,
CONCAT(s.first_name, ' ', s.last_name) as sender_name,
s.role as sender_role,
CONCAT(r.first_name, ' ', r.last_name) as recipient_name
FROM messages m
LEFT JOIN users s ON m.sender_id = s.id
LEFT JOIN users r ON m.recipient_id = r.id
WHERE m.ticket_id = ?
ORDER BY m.created_at ASC
");
$stmt->execute([$ticket_id]);
$messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get all necessary data for ticket list
if (!$viewing_ticket) {
try {
// Get tickets based on role
if ($current_user['role'] === 'admin' || $current_user['role'] === 'staff') {
$tickets = $pdo->query("
SELECT t.*, d.name as department_name,
CONCAT(u.first_name, ' ', u.last_name) as creator_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users u ON t.created_by = u.id
LEFT JOIN users a ON t.assigned_to = a.id
ORDER BY t.created_at DESC
")->fetchAll(PDO::FETCH_ASSOC);
} else {
$stmt = $pdo->prepare("
SELECT t.*, d.name as department_name,
CONCAT(a.first_name, ' ', a.last_name) as assigned_name
FROM tickets t
LEFT JOIN departments d ON t.department_id = d.id
LEFT JOIN users a ON t.assigned_to = a.id
WHERE t.created_by = ?
ORDER BY t.created_at DESC
");
$stmt->execute([$current_user['id']]);
$tickets = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Get departments for dropdown
$departments = $pdo->query("SELECT * FROM departments")->fetchAll(PDO::FETCH_ASSOC);
// Get staff for assignment dropdown
$staff_members = $pdo->query("
SELECT id, CONCAT(first_name, ' ', last_name) as name
FROM users
WHERE role IN ('staff', 'admin')
")->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
die("Error fetching data: " . $e->getMessage());
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title><?= $viewing_ticket ? 'Ticket Details' : 'Ticket System' ?></title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
<style>
/* Your existing CSS styles here */
</style>
</head>
<body>
<div class="helpdesk-container">
<!-- Your existing HTML structure here -->
</div>
<script>
document.addEventListener('DOMContentLoaded', function() {
// AJAX form submissions
document.querySelectorAll('.status-update-form select, .assign-form select').forEach(select => {
select.addEventListener('change', function() {
const form = this.closest('form');
fetch('', {
method: 'POST',
body: new FormData(form)
})
.then(response => {
if (response.ok) {
window.location.reload();
} else {
alert('Error updating ticket');
}
})
.catch(error => {
console.error('Error:', error);
alert('Error updating ticket');
});
});
});
});
</script>
</body>
</html>
Key changes made:
Removed dependency on head_id column:
Improved recipient handling:
Maintained all other functionality:
This solution ensures that:
The duplicate ticket issue is also fully resolved since we:
I'll fix both issues - the undefined "user_name" error and remove the edit button. Here's the corrected code:
<?php
// [Previous code remains the same until the messages display section]
// Get ticket messages with correct column names
$stmt = $pdo->prepare("
SELECT m.*,
CONCAT(s.first_name, ' ', s.last_name) as sender_name,
s.role as sender_role,
CONCAT(r.first_name, ' ', r.last_name) as recipient_name
FROM messages m
LEFT JOIN users s ON m.sender_id = s.id
LEFT JOIN users r ON m.recipient_id = r.id
WHERE m.ticket_id = ?
ORDER BY m.created_at ASC
");
$stmt->execute([$ticket_id]);
$messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
// [Rest of the code remains the same until the actions column in the tickets table]
?>
<!-- In the ticket details view -->
<div class="ticket-messages">
<h3>Conversation</h3>
<?php if (empty($messages)): ?>
<p>No messages yet.</p>
<?php else: ?>
<div class="message-list">
<?php foreach ($messages as $msg): ?>
<div class="message <?= strtolower($msg['sender_role'] ?? '') ?>">
<div class="message-header">
<span class="message-author"><?= htmlspecialchars($msg['sender_name'] ?? 'Unknown') ?></span>
<span class="message-date"><?= date('M j, Y g:i A', strtotime($msg['created_at'])) ?></span>
</div>
<div class="message-content">
<?= nl2br(htmlspecialchars($msg['message'])) ?>
</div>
</div>
<?php endforeach; ?>
</div>
<?php endif; ?>
<!-- Message form remains the same -->
</div>
<!-- In the tickets list table -->
<td>
<a href="<?= $base_url ?>&ticket_id=<?= $ticket['id'] ?>" class="button">View</a>
<?php if ($ticket['created_by'] == $current_user['id'] || $current_user['role'] === 'admin' || $current_user['role'] === 'staff'): ?>
<a href="delete_ticket.php?id=<?= $ticket['id'] ?>" class="button" onclick="return confirm('Are you sure you want to delete this ticket?')">Delete</a>
<?php endif; ?>
</td>
<?php
// [Previous code remains the same until the messages display section]
// Get ticket messages with correct column names
$stmt = $pdo->prepare("
SELECT m.*,
CONCAT(s.first_name, ' ', s.last_name) as sender_name,
s.role as sender_role,
CONCAT(r.first_name, ' ', r.last_name) as recipient_name
FROM messages m
LEFT JOIN users s ON m.sender_id = s.id
LEFT JOIN users r ON m.recipient_id = r.id
WHERE m.ticket_id = ?
ORDER BY m.created_at ASC
");
$stmt->execute([$ticket_id]);
$messages = $stmt->fetchAll(PDO::FETCH_ASSOC);
// [Rest of the code remains the same until the actions column in the tickets table]
?>
<!-- In the ticket details view -->
<div class="ticket-messages">
<h3>Conversation</h3>
<?php if (empty($messages)): ?>
<p>No messages yet.</p>
<?php else: ?>
<div class="message-list">
<?php foreach ($messages as $msg): ?>
<div class="message <?= strtolower($msg['sender_role'] ?? '') ?>">
<div class="message-header">
<span class="message-author"><?= htmlspecialchars($msg['sender_name'] ?? 'Unknown') ?></span>
<span class="message-date"><?= date('M j, Y g:i A', strtotime($msg['created_at'])) ?></span>
</div>
<div class="message-content">
<?= nl2br(htmlspecialchars($msg['message'])) ?>
</div>
</div>
<?php endforeach; ?>
</div>
<?php endif; ?>
<!-- Message form remains the same -->
</div>
<!-- In the tickets list table -->
<td>
<a href="<?= $base_url ?>&ticket_id=<?= $ticket['id'] ?>" class="button">View</a>
<?php if ($ticket['created_by'] == $current_user['id'] || $current_user['role'] === 'admin' || $current_user['role'] === 'staff'): ?>
<a href="delete_ticket.php?id=<?= $ticket['id'] ?>" class="button" onclick="return confirm('Are you sure you want to delete this ticket?')">Delete</a>
<?php endif; ?>
</td>
Key changes made:
Fixed the "user_name" undefined error:
sender_name instead of user_name??)Removed the edit button:
Improved message display:
The changes ensure that:
Make sure your database queries match the column names in your actual database, especially for the messages table joins. The code now uses sender_name and recipient_name which should match your query results.
For the delete functionality, you'll need to create a separate delete_ticket.php file that handles the actual deletion with proper authentication and authorization checks. Here's a basic example:
<?php
// delete_ticket.php
session_start();
require_once 'db_connection.php'; // Your database connection file
if (empty($_SESSION['user_id'])) {
header("Location: login.php");
exit;
}
if (!isset($_GET['id'])) {
header("Location: tickets.php");
exit;
}
$ticket_id = (int)$_GET['id'];
// Verify user has permission to delete
$stmt = $pdo->prepare("SELECT created_by FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
if (!$ticket || ($ticket['created_by'] != $_SESSION['user_id'] && $_SESSION['role'] !== 'admin' && $_SESSION['role'] !== 'staff')) {
die("You don't have permission to delete this ticket");
}
// Delete the ticket
try {
$pdo->beginTransaction();
// First delete messages
$stmt = $pdo->prepare("DELETE FROM messages WHERE ticket_id = ?");
$stmt->execute([$ticket_id]);
// Then delete the ticket
$stmt = $pdo->prepare("DELETE FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$pdo->commit();
header("Location: tickets.php?deleted=1");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
die("Error deleting ticket: " . $e->getMessage());
}
```*_
<?php
// delete_ticket.php
session_start();
require_once 'db_connection.php'; // Your database connection file
if (empty($_SESSION['user_id'])) {
header("Location: login.php");
exit;
}
if (!isset($_GET['id'])) {
header("Location: tickets.php");
exit;
}
$ticket_id = (int)$_GET['id'];
// Verify user has permission to delete
$stmt = $pdo->prepare("SELECT created_by FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
if (!$ticket || ($ticket['created_by'] != $_SESSION['user_id'] && $_SESSION['role'] !== 'admin' && $_SESSION['role'] !== 'staff')) {
die("You don't have permission to delete this ticket");
}
// Delete the ticket
try {
$pdo->beginTransaction();
// First delete messages
$stmt = $pdo->prepare("DELETE FROM messages WHERE ticket_id = ?");
$stmt->execute([$ticket_id]);
// Then delete the ticket
$stmt = $pdo->prepare("DELETE FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$pdo->commit();
header("Location: tickets.php?deleted=1");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
die("Error deleting ticket: " . $e->getMessage());
}
```*_
Yes, when you click the Delete button and confirm the action in the dialog box, it will redirect to the delete_ticket.php file to process the deletion. Here's how the complete flow works:
<a href="delete_ticket.php?id=<?= $ticket['id'] ?>"
class="button"
onclick="return confirm('Are you sure you want to delete this ticket?')">
Delete
</a>
<a href="delete_ticket.php?id=<?= $ticket['id'] ?>"
class="button"
onclick="return confirm('Are you sure you want to delete this ticket?')">
Delete
</a>
confirm() dialog appears asking "Are you sure you want to delete this ticket?"delete_ticket.php with the ticket ID// 1. Checks user is logged in
// 2. Verifies user has permission to delete
// 3. Deletes the ticket and its messages
// 4. Redirects back to tickets.php with success message
header("Location: tickets.php?deleted=1");
// 1. Checks user is logged in
// 2. Verifies user has permission to delete
// 3. Deletes the ticket and its messages
// 4. Redirects back to tickets.php with success message
header("Location: tickets.php?deleted=1");
tickets.php where you can show a success message by adding this near the top:// In tickets.php
if (isset($_GET['deleted'])) {
echo '<div class="notice notice-success">Ticket deleted successfully!</div>';
}
// In tickets.php
if (isset($_GET['deleted'])) {
echo '<div class="notice notice-success">Ticket deleted successfully!</div>';
}
Important security notes about the delete functionality:
Always verify:
Use transactions when deleting related records (messages + ticket)
Never show raw database errors to users - catch exceptions and show friendly messages
Consider adding CSRF protection for production use
The complete delete_ticket.php should look like this:
<?php
session_start();
require_once 'db_connection.php'; // Your database connection file
// 1. Authentication check
if (empty($_SESSION['user_id'])) {
header("Location: login.php");
exit;
}
// 2. Check ticket ID exists
if (!isset($_GET['id']) || !is_numeric($_GET['id'])) {
header("Location: tickets.php");
exit;
}
$ticket_id = (int)$_GET['id'];
// 3. Verify permission
$stmt = $pdo->prepare("SELECT created_by FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
$is_admin = in_array($_SESSION['role'], ['admin', 'staff']);
$is_creator = ($ticket['created_by'] ?? null) == $_SESSION['user_id'];
if (!$ticket || (!$is_creator && !$is_admin)) {
header("Location: tickets.php?error=permission");
exit;
}
// 4. Process deletion
try {
$pdo->beginTransaction();
// Delete messages first (due to foreign key constraint)
$stmt = $pdo->prepare("DELETE FROM messages WHERE ticket_id = ?");
$stmt->execute([$ticket_id]);
// Then delete ticket
$stmt = $pdo->prepare("DELETE FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$pdo->commit();
header("Location: tickets.php?deleted=1");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
error_log("Delete ticket error: " . $e->getMessage());
header("Location: tickets.php?error=delete");
exit;
}
<?php
session_start();
require_once 'db_connection.php'; // Your database connection file
// 1. Authentication check
if (empty($_SESSION['user_id'])) {
header("Location: login.php");
exit;
}
// 2. Check ticket ID exists
if (!isset($_GET['id']) || !is_numeric($_GET['id'])) {
header("Location: tickets.php");
exit;
}
$ticket_id = (int)$_GET['id'];
// 3. Verify permission
$stmt = $pdo->prepare("SELECT created_by FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$ticket = $stmt->fetch();
$is_admin = in_array($_SESSION['role'], ['admin', 'staff']);
$is_creator = ($ticket['created_by'] ?? null) == $_SESSION['user_id'];
if (!$ticket || (!$is_creator && !$is_admin)) {
header("Location: tickets.php?error=permission");
exit;
}
// 4. Process deletion
try {
$pdo->beginTransaction();
// Delete messages first (due to foreign key constraint)
$stmt = $pdo->prepare("DELETE FROM messages WHERE ticket_id = ?");
$stmt->execute([$ticket_id]);
// Then delete ticket
$stmt = $pdo->prepare("DELETE FROM tickets WHERE id = ?");
$stmt->execute([$ticket_id]);
$pdo->commit();
header("Location: tickets.php?deleted=1");
exit;
} catch (PDOException $e) {
$pdo->rollBack();
error_log("Delete ticket error: " . $e->getMessage());
header("Location: tickets.php?error=delete");
exit;
}
Then in your tickets.php, handle the status messages:
// At the top of tickets.php
if (isset($_GET['deleted'])) {
$message = 'Ticket deleted successfully!';
$message_type = 'success';
} elseif (isset($_GET['error'])) {
if ($_GET['error'] === 'permission') {
$message = 'You do not have permission to delete that ticket';
} else {
$message = 'Error deleting ticket';
}
$message_type = 'error';
}
// At the top of tickets.php
if (isset($_GET['deleted'])) {
$message = 'Ticket deleted successfully!';
$message_type = 'success';
} elseif (isset($_GET['error'])) {
if ($_GET['error'] === 'permission') {
$message = 'You do not have permission to delete that ticket';
} else {
$message = 'Error deleting ticket';
}
$message_type = 'error';
}
This provides a complete, secure deletion flow with proper user feedback.