In this tutorial, we will show you how to implement dependent dropdowns for selecting Professor, Department, and Course using jQuery, Ajax, PHP, and MySQL. The Department dropdown will be dependent on the selected Professor, and the Course dropdown will be dependent on the selected Department.
When a Professor is changed, the corresponding Department and Course will be retrieved from the backend without reloading the page, utilizing jQuery Ajax.
In this example, we will integrate dependent dropdowns for Professor, Department, and Course using MySQL.
Initially, all Professors will be listed in the Professor dropdown. Upon selecting a Professor, the corresponding Department will be fetched from the MySQL database and displayed in the Department dropdown. Subsequently, when a Department is selected, the relevant Courses will be retrieved from the MySQL database and populated in the Course dropdown.
Professor Table holds information about professors and Department Table stores information about departments,
ProfessorId, column stores the ID of the professor who heads the department. It establishes a foreign key relationship with the Id column of the Professor table.
Course Table contains information about courses,DepartmentId column stores the ID of the department to which the course belongs.
It establishes a foreign key relationship with the Id column of the Department table.
CREATE TABLE Professor (
Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL
);
CREATE TABLE Department (
Id INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL,
ProfessorId INT,
FOREIGN KEY (ProfessorId) REFERENCES Professor(Id)
);
CREATE TABLE Course (
Id INT PRIMARY KEY AUTO_INCREMENT,
CourseName VARCHAR(255) NOT NULL,
DepartmentId INT,
FOREIGN KEY (DepartmentId) REFERENCES Department(Id)
);
<?php
//you have established a MySQL database connection
// Function to fetch all professors from the database
function getAllProfessors($connection) {
$query = "SELECT * FROM Professor";
$result = mysqli_query($connection, $query);
$professors = array();
while ($row = mysqli_fetch_assoc($result)) {
$professors[] = $row;
}
return $professors;
}
// Function to fetch departments based on selected professor
function getDepartmentsByProfessor($connection, $professorId) {
$query = "SELECT * FROM Department WHERE ProfessorId = $professorId";
$result = mysqli_query($connection, $query);
$departments = array();
while ($row = mysqli_fetch_assoc($result)) {
$departments[] = $row;
}
return $departments;
}
// Function to fetch courses based on selected department
function getCoursesByDepartment($connection, $departmentId) {
$query = "SELECT * FROM Course WHERE DepartmentId = $departmentId";
$result = mysqli_query($connection, $query);
$courses = array();
while ($row = mysqli_fetch_assoc($result)) {
$courses[] = $row;
}
return $courses;
}
// Main code
$connection = mysqli_connect("localhost", "username", "password", "database_name");
if (!$connection) {
die("Connection failed: " . mysqli_connect_error());
}
// Check if a professor is selected
if (isset($_POST['professor_id'])) {
$professorId = $_POST['professor_id'];
$departments = getDepartmentsByProfessor($connection, $professorId);
echo json_encode($departments);
exit;
}
// Check if a department is selected
if (isset($_POST['department_id'])) {
$departmentId = $_POST['department_id'];
$courses = getCoursesByDepartment($connection, $departmentId);
echo json_encode($courses);
exit;
}
// If no selection is made, initially load all professors
$professors = getAllProfessors($connection);
// Close the connection
mysqli_close($connection);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Cascading Dropdown List</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
</head>
<body>
<select id="professor">
<option value="">Select Professor</option>
<?php
// PHP code to populate the Professor dropdown
$professors = getAllProfessors($connection);
foreach ($professors as $professor) {
echo "<option value='" . $professor['Id'] . "'>" . $professor['Name'] . "</option>";
}
?>
</select>
<select id="department">
<option value="">Select Department</option>
</select>
<select id="course">
<option value="">Select Course</option>
</select>
<script>
$(document).ready(function(){
$('#professor').change(function(){
var professorId = $(this).val();
if(professorId != ''){
$.ajax({
url: 'Professor.php'
type: 'post',
data: {professor_id: professorId},
dataType: 'json',
success:function(response){
var len = response.length;
$("#department").empty();
$("#department").append("<option value=''>Select Department</option>");
for( var i = 0; i<len; i++){
var id = response[i]['Id'];
var name = response[i]['Name'];
$("#department").append("<option value='"+id+"'>"+name+"</option>");
}
}
});
} else {
$("#department").empty();
$("#course").empty();
}
});
$('#department').change(function(){
var departmentId = $(this).val();
if(departmentId != ''){
$.ajax({
url: 'Professor.php',
type: 'post',
data: {department_id: departmentId},
dataType: 'json',
success:function(response){
var len = response.length;
$("#course").empty();
$("#course").append("<option value=''>Select Course</option>");
for( var i = 0; i<len; i++){
var id = response[i]['Id'];
var name = response[i]['CourseName'];
$("#course").append("<option value='"+id+"'>"+name+"</option>");
}
}
});
} else {
$("#course").empty();
}
});
});
</script>
</body>
</html>