Update your while loop
$select_field_query = "Select `lecturer_field`.`lecturer_id` ,
`user`.`name` FROM ((`user` JOIN `lecturer_field`
ON `user`.`username` = `lecturer_field`.`lecturer_id`)
JOIN `course_field` ON `lecturer_field`.`field` = `course_field`.`field_id`)
WHERE `lecturer_field`.`field`= :programme"
while ($row = $lect_smt->fetch(PDO::FETCH_ASSOC))
{
echo "<option value='" . $row['lecturer_id'] ."'>" . $row['name'] . "</option>";
}
//add script when you change field it will send ajax call and fetch lecturer as your requirements
<script>
$('#field_id').on('change',function(){
var programme = $(this).val();
var department = "<?php echo $_SESSION['department'] ;?>";
$.ajax({
type:"GET",
url:"ajax.php",
data:{programme:programme,department:department}, // multiple data sent using ajax
success: function (html) {
$('#lecturer_id').html(html);
}
});
});
</script>
ajax.php
<?php
$servername = "localhost";
$username = "root";
$password = "mysql";
try {
$db = new PDO("mysql:host=$servername;dbname=stulec",
$username, $password);
// set the PDO error mode to exception
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// echo "Connected successfully";
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
if(isset($_GET['programme']) && isset($_GET['department']) )
{
$programme = $_GET['programme'];
$depart = $_GET['department'];
$output = '';
$select_lecturer_query = "SELECT name, lecturer_id from lecturer_field
LEFT JOIN users ON users.username = lecturer_field.lecturer_id
LEFT JOIN field_programme ON field_programme.field = lecturer_field.field
WHERE users.role = 'Lecturer' AND users.department = '$depart'
AND field_programme.programme= '$programme'";
$lect_smt = $db->prepare($select_lecturer_query);
$lect_smt->execute();
while ($row = $lect_smt->fetch(PDO::FETCH_ASSOC))
{
$output .= "<option value='" . $row['lecturer_id'] ."'>" . $row['name'] . "</option>";
}
echo $output;
}
?>
