How to develop Projects(OSMS) Using PHP and Mysql
Part 9
7️⃣ Online Service Management System
Module 1: Add Technician Details
Purpose: Allows admins to add and manage technician details.
Project Structure:
│── /technicians/
│ ├── add_technician.php # Add technician
│ ├── edit_technician.php # Edit technician details
│ ├── delete_technician.php # Delete technician
│ ├── technician_list.php # List all technicians
Database Table:
CREATE TABLE technicians (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
specialization VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
✅ add_technician.php
(Add Technician)
<?php
// Include database connection
include('../config/db.php');
if ($_SERVER["REQUEST_METHOD"] == "POST")
{
$name = trim($_POST['name']);
$email = trim($_POST['email']);
$specialization
= trim($_POST['specialization']);
if (!empty($name)
&& !empty($email)) {
$sql = "INSERT
INTO technicians (name, email, specialization) VALUES ('$name', '$email', '$specialization')";
if (mysqli_query($conn,
$sql)) {
echo
"<script>alert('Technician added successfully!');
window.location.href='technician_list.php';</script>";
} else {
echo
"Error: " . mysqli_error($conn);
}
} else {
echo "Name
and Email are required!";
}
}
mysqli_close($conn);
?>
<!DOCTYPE html>
<html>
<head>
<title>Add Technician</title>
</head>
<body>
<h2>Add Technician</h2>
<form
method="POST">
<label>Name:</label>
<input type="text" name="name"
required><br><br>
<label>Email:</label>
<input type="email" name="email"
required><br><br>
<label>Specialization:</label>
<input type="text" name="specialization"><br><br>
<input type="submit" value="Add Technician">
</form>
</body>
</html>
✅ edit_technician.php
(Edit Technician)
<?php
include('../config/db.php');
$id = $_GET['id'] ?? null;
if (!$id || !is_numeric($id)) {
die("Invalid
technician ID!");
}
$sql = "SELECT * FROM technicians WHERE id = $id";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) !== 1) {
die("Technician
not found!");
}
$technician = mysqli_fetch_assoc($result);
if ($_SERVER["REQUEST_METHOD"] == "POST")
{
$name = trim($_POST['name']);
$email = trim($_POST['email']);
$specialization
= trim($_POST['specialization']);
if (!empty($name)
&& !empty($email)) {
$sql = "UPDATE
technicians SET name='$name', email='$email', specialization='$specialization'
WHERE id=$id";
if (mysqli_query($conn,
$sql)) {
echo
"<script>alert('Technician updated successfully!');
window.location.href='technician_list.php';</script>";
} else {
echo
"Error: " . mysqli_error($conn);
}
} else {
echo "Name
and Email are required!";
}
}
mysqli_close($conn);
?>
<!DOCTYPE html>
<html>
<head>
<title>Edit Technician</title>
</head>
<body>
<h2>Edit Technician</h2>
<form
method="POST">
<label>Name:</label>
<input type="text" name="name" value="<?=
htmlspecialchars($technician['name']) ?>"
required><br><br>
<label>Email:</label>
<input
type="email" name="email" value="<?=
htmlspecialchars($technician['email']) ?>"
required><br><br>
<label>Specialization:</label>
<input type="text" name="specialization" value="<?=
htmlspecialchars($technician['specialization'])
?>"><br><br>
<input type="submit" value="Update Technician">
</form>
</body>
</html>
✅ delete_technician.php
(Delete Technician)
<?php
include('../config/db.php');
$id = $_GET['id'] ?? null;
if (!$id || !is_numeric($id)) {
die("Invalid
technician ID!");
}
$sql = "DELETE FROM technicians WHERE id = $id";
if (mysqli_query($conn, $sql)) {
echo "<script>alert('Technician
deleted successfully!');
window.location.href='technician_list.php';</script>";
} else {
echo "Error:
" . mysqli_error($conn);
}
mysqli_close($conn);
?>
✅ technician_list.php
(Technician List)
<?php
include('../config/db.php');
$sql = "SELECT * FROM technicians ORDER BY id
DESC";
$result = mysqli_query($conn, $sql);
?>
<!DOCTYPE html>
<html>
<head>
<title>Technician List</title>
<style>
table {
width: 100%;
border-collapse: collapse;
}
table,
th, td {
border: 1px solid black;
}
th, td {
padding: 10px;
text-align: left;
}
th {
background-color: #f2f2f2;
}
.action-links a {
margin-right: 10px;
text-decoration: none;
color: blue;
}
</style>
</head>
<body>
<h2>Technician
List</h2>
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Specialization</th>
<th>Created At</th>
<th>Actions</th>
</tr>
<?php
if (mysqli_num_rows($result) > 0): ?>
<?php
while ($row = mysqli_fetch_assoc($result)): ?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= htmlspecialchars($row['name']) ?></td>
<td><?= htmlspecialchars($row['email']) ?></td>
<td><?= htmlspecialchars($row['specialization']) ?></td>
<td><?= $row['created_at'] ?></td>
<td class='action-links'>
<a href='edit_technician.php?id=<?= $row['id'] ?>'>Edit</a>
<a href='delete_technician.php?id=<?= $row['id'] ?>' onclick='return
confirm("Are you sure?")'>Delete</a>
</td>
</tr>
<?php endwhile; ?>
<?php
else: ?>
<tr><td
colspan='6'>No technicians found</td></tr>
<?php
endif; ?>
<?php
mysqli_close($conn); ?>
</table>
<br>
<a href="add_technician.php">Add
New Technician</a>
</body>
</html>
🔹
Improvements Made:
1.
Pure mysqli_query() method:
No prepared statements are used.
2.
Security:
o Used
htmlspecialchars() to prevent XSS.
o Checked
if id is numeric before SQL queries.
3.
Better Redirects:
o Used
JavaScript window.location.href for navigation.
4.
Optimized SQL Queries:
o ORDER
BY id DESC in listing for the latest records first.
o mysqli_num_rows()
properly used before fetching data.
5.
Error Handling:
o Used
mysqli_error() for debugging.