How to develop Projects(OSMS) Using PHP and Mysql
Part 11
7️⃣ Online Service Management System
✅ Module 3: Assign Technician to Request
Purpose:
Allows admin to assign available technicians to customer service requests.
Functionalities:
- ✅ Assign technician to request
- ✅ View assigned tasks
- ✅ Reassign or update technician
Project Structure:
│── /assignments/
│ ├── assign_technician.php # Assign a technician to request
│ ├── assignment_list.php # List all assignments
│ ├── update_assignment.php # Update/reassign technician
Database Table:
CREATE TABLE technician_assignments (
id INT AUTO_INCREMENT PRIMARY KEY,
request_id INT,
technician_id INT,
assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (request_id) REFERENCES service_requests(id),
FOREIGN KEY (technician_id) REFERENCES technicians(id)
);
1️⃣ assign_technician.php
This page assigns a
technician to a specific service request.
<?php
$conn = new mysqli("localhost",
"root", "", "service_db");
if ($conn->connect_error)
{
die("Connection failed: " . $conn->connect_error);
}
if(isset($_POST['assign']))
{
$request_id = $_POST['request_id'];
$technician_id = $_POST['technician_id'];
$sql = "INSERT INTO
technician_assignments (request_id, technician_id) VALUES ('$request_id', '$technician_id')";
if ($conn->query($sql) === TRUE) {
echo "Technician Assigned
Successfully!";
} else {
echo "Error: " . $conn->error;
}
}
?>
<h2>Assign
Technician to Request</h2>
<form method="POST">
<label>Request ID:</label>
<input type="number" name="request_id"
required><br><br>
<label>Select
Technician:</label>
<select name="technician_id"
required>
<?php
$result
= $conn->query("SELECT id, name FROM technicians");
while ($row = $result->fetch_assoc())
{
echo "<option value='{$row['id']}'>{$row['name']}</option>";
}
?>
</select><br><br>
<button type="submit" name="assign">Assign
Technician</button>
</form>
2️⃣ assignment_list.php
This page lists all
current technician assignments.
<?php
$conn = new mysqli("localhost",
"root", "", "service_db");
if ($conn->connect_error)
{
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT
ta.id, sr.description as request_desc, t.name as technician_name,
ta.assigned_at
FROM technician_assignments ta
JOIN service_requests sr ON
ta.request_id = sr.id
JOIN technicians t ON ta.technician_id
= t.id";
$result = $conn->query($sql);
?>
<h2>Assigned
Technicians</h2>
<table border="1"
cellpadding="8">
<tr>
<th>ID</th>
<th>Request</th>
<th>Technician</th>
<th>Assigned At</th>
</tr>
<?php while($row = $result->fetch_assoc()):
?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= $row['request_desc'] ?></td>
<td><?= $row['technician_name']
?></td>
<td><?= $row['assigned_at'] ?></td>
</tr>
<?php endwhile; ?>
</table>
3️⃣ update_assignment.php
This page allows you to
reassign a technician to a request.
<?php
$conn = new mysqli("localhost",
"root", "", "service_db");
if ($conn->connect_error)
{
die("Connection failed: " . $conn->connect_error);
}
if(isset($_POST['update']))
{
$assignment_id = $_POST['assignment_id'];
$technician_id = $_POST['technician_id'];
$sql = "UPDATE technician_assignments
SET technician_id = '$technician_id', assigned_at = CURRENT_TIMESTAMP WHERE id
= '$assignment_id'";
if ($conn->query($sql) === TRUE) {
echo "Assignment Updated
Successfully!";
} else {
echo "Error: " . $conn->error;
}
}
?>
<h2>Update
Technician Assignment</h2>
<form method="POST">
<label>Assignment ID:</label>
<input type="number" name="assignment_id"
required><br><br>
<label>Select New
Technician:</label>
<select name="technician_id"
required>
<?php
$result = $conn->query("SELECT
id, name FROM technicians");
while ($row = $result->fetch_assoc())
{
echo "<option value='{$row['id']}'>{$row['name']}</option>";
}
?>
</select><br><br>
<button type="submit" name="update">Update
Assignment</button>
</form>