Ticketing System with PHP and MySQL
Certainly! Let’s break down the steps to create a Ticketing System using PHP and MySQL:
1. Requirements:
– Basic understanding of PHP, MySQL, HTML, CSS.
– PHP development environment (e.g., XAMPP, WAMP, MAMP).
– Text editor or IDE for code editing.
2. What You Will Learn in this Tutorial:
-Setting up a MySQL database.
– Creating tables for tickets and users.
– Designing the user interface with CSS.
– Implementing PHP functions for ticket management.
– Creating pages for home, creating ticket, and viewing ticket.
3. File Structure & Setup:
Ensure you have the following files and directories set up:
project/
│
├── css/ (directory for CSS stylesheets)
│ └── style.css (CSS stylesheet)
│
├── includes/ (directory for PHP includes)
│ ├── db.php (PHP file for database connection)
│ └── functions.php (PHP file for ticketing system functions)
│
├── create_ticket.php (PHP file for creating tickets)
├── view_ticket.php (PHP file for viewing tickets)
└── index.php (Home page)
4. Creating the Database and Setting up Tables:
Use MySQL to create a database named `ticketing_system` and define tables for tickets and users.
CREATE DATABASE ticketing_system;
USE ticketing_system;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
password VARCHAR(255) NOT NULL
);
CREATE TABLE tickets (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
description TEXT,
status ENUM('open', 'closed') DEFAULT 'open',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
user_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
5. Designing the Ticketing System with CSS3:
Create a stylesheet (`style.css`) to style the user interface.
body {
font-family: Arial, sans-serif;
}
.container {
width: 80%;
margin: auto;
}
.ticket {
margin-bottom: 20px;
padding: 10px;
border: 1px solid #ccc;
}
.ticket h3 {
margin-top: 0;
}
.btn {
display: inline-block;
padding: 8px 16px;
background-color: #007bff;
color: #fff;
text-decoration: none;
border: none;
border-radius: 4px;
cursor: pointer;
}
.btn:hover {
background-color: #0056b3;
}
6. Creating the Functions File:
Define functions for interacting with the database and managing tickets in `functions.php`.
<?php
// includes/functions.php
// Function to connect to the database
function db_connect() {
$host = 'localhost';
$username = 'your_username';
$password = 'your_password';
$database = 'ticketing_system';
$conn = new mysqli($host, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
return $conn;
}
// Other functions for ticket management
// (e.g., createTicket(), getTickets(), updateTicket(), etc.)
?>
7. Creating the Home Page (`index.php`):
Create a homepage where users can see a list of tickets.
<?php
// index.php
include 'includes/functions.php';
// Fetch tickets from the database
$conn = db_connect();
$sql = "SELECT * FROM tickets";
$result = $conn->query($sql);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Ticketing System</title>
<link rel="stylesheet" href="css/style.css">
</head>
<body>
<div class="container">
<h1>Ticketing System</h1>
<a href="create_ticket.php" class="btn">Create Ticket</a>
<?php
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "<div class='ticket'>";
echo "<h3>" . $row['title'] . "</h3>";
echo "<p>Status: " . $row['status'] . "</p>";
// Add more ticket details as needed
echo "</div>";
}
} else {
echo "No tickets found.";
}
?>
</div>
</body>
</html>
8. Creating the Create Ticket Page (`create_ticket.php`):
Create a page where users can create new tickets.
<?php
// create_ticket.php
include 'includes/functions.php';
// Handle form submission to create ticket
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$title = $_POST['title'];
$description = $_POST['description'];
$user_id = 1; // Replace with logged-in user ID
$conn = db_connect();
// Call function to create ticket in the database
// createTicket($title, $description, $user_id);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Create Ticket</title>
<link rel="stylesheet" href="css/style.css">
</head>
<body>
<div class="container">
<h1>Create Ticket</h1>
<form action="" method="post">
<label for="title">Title:</label><br>
<input type="text" id="title" name="title" required><br>
<label for="description">Description:</label><br>
<textarea id="description" name="description" required></textarea><br>
<button type="submit" class="btn">Submit</button>
</form>
</div>
</body>
</html>
9. Creating the View Ticket Page (`view_ticket.php`):
Create a page where users can view details of a specific ticket.
<?php
// view_ticket.php
include 'includes/functions.php';
// Fetch ticket details based on ID from the database
$conn = db_connect();
$ticket_id = $_GET['id']; // Assume the ticket ID is passed via URL parameter
$sql = "SELECT * FROM tickets WHERE id = $ticket_id";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
$row = $result->fetch_assoc();
$title = $row['title'];
$description = $row['description'];
$status = $row['status'];
// Add more ticket details as needed
} else {
echo "Ticket not found.";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>View Ticket</title
>
<link rel="stylesheet" href="css/style.css">
</head>
<body>
<div class="container">
<h1>View Ticket</h1>
<div class="ticket">
<h3><?php echo $title; ?></h3>
<p>Status: <?php echo $status; ?></p>
<p>Description: <?php echo $description; ?></p>
<!-- Add more ticket details as needed -->
</div>
</div>
</body>
</html>
That’s it! You’ve now created a basic Ticketing System using PHP and MySQL. Users can create tickets, view a list of tickets, and view details of individual tickets. You can further enhance the system by adding features like user authentication, ticket assignment, and ticket status updates.
About The Author
