I wanted to display the lecturer name according to the course field. One course field can have 2 or more lecturers. Right now, I am selecting the lecturer's name and username from the database in a page named ajax-lecturer.php.
Below are the codes for ajax-lecturer.php:
<?php
require("global-include.php");
if($_POST['field_id'])
{
$smt = $db->prepare("SELECT lecturer.name, lecturer.username FROM lecturer_field
LEFT JOIN course_field ON course_field.field_id = lecturer_field.field
LEFT JOIN users AS lecturer ON lecturer_field.lecturer_id = lecturer.username
WHERE course_field.field_id = :field_id");
$smt->bindParam(':field_id', $_POST['field_id']);
$smt->execute();
$data = $smt->fetchAll();
echo json_encode($data[0]);
}
else
{
echo 'Nothing is found';
}
?>
Below is the lecturer_field table. A field can have more than one lecturer which is why I put it under lecturer_field table:
Below is the course_field table. The field_id in this table is used as the foreign key to the lecturer_field table (field column):
Note that field no. 1 in lecturer_field table have two lecturers bind to it. This means that the lecturer's name and username(used for option value) in the drop-down list should change when a specific course field is selected from the drop down list.Eg:
When the Computer Security course field is selected as the image above, two lecturers name such as below should appear in the drop-down list.
The username from the users table below is a foreign key to the lecturer_field table (the lecturer_id column). The programme column for the student role is linked to the programme table.
The problem that I am facing now is that the lecturer's name is not coming out and there is an undefined variable when I inspected the drop-down list.
The codes for the appointment-form.php is such as below and the AJAX part in this page is supposed to be calling the name and username from the ajax-lecturer.php:
<?php
require ("global-include.php");
//Condition when the submit button is clicked
if(isset($_POST["btnSubmit"])){
try
{
//$appointment_date = date('Y-m-d',strtotime($_POST['appointment_date']));
//$appointment_date = date("Y-m-d H:i:s");
// Inserting the data into database
$insert_query = "INSERT INTO appointment (appointment_type, appointment_date, appointment_time, student_id, lecturer_id, field, venue, remarks, appointment_status)
VALUES ('".$_POST["appointment_type"]."',
'".$_POST["appointment_date"]."',
'".$_POST["appointment_time"]."',
'".$_POST["student_id"]."',
'".$_POST["lecturer_id"]."',
'".$_POST["field_id"]."',
'".$_POST["venue"]."',
'".$_POST["remarks"]."',
'".$_POST["appointment_status"]."'
)";
if (($db->query($insert_query)))
{
echo "<script type= 'text/javascript'>alert('An appointment is successfully made!');";
echo 'window.location= "student-view-appointment.php"';
echo "</script>";
}
else
{
echo "<script type= 'text/javascript'>alert('Error: An appoinment is not successfully made!');";
echo 'window.location= "student-view-appointment.php"';
echo "</script>";
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>STULEC | Appointment Form</title>
<!-- Tell the browser to be responsive to screen width -->
<meta content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no" name="viewport">
<!-- Bootstrap 3.3.7 -->
<link rel="stylesheet" href="bower_components/bootstrap/dist/css/bootstrap.min.css">
<!-- Font Awesome -->
<link rel="stylesheet" href="bower_components/font-awesome/css/font-awesome.min.css">
<!-- Ionicons -->
<link rel="stylesheet" href="/bower_components/Ionicons/css/ionicons.min.css">
<!-- daterange picker -->
<link rel="stylesheet" href="bower_components/bootstrap-daterangepicker/daterangepicker.css">
<!-- bootstrap datepicker -->
<link rel="stylesheet" href="bower_components/bootstrap-datepicker/dist/css/bootstrap-datepicker.min.css">
<!-- iCheck for checkboxes and radio inputs -->
<link rel="stylesheet" href="plugins/iCheck/all.css">
<!-- Bootstrap Color Picker -->
<link rel="stylesheet" href="bower_components/bootstrap-colorpicker/dist/css/bootstrap-colorpicker.min.css">
<!-- Bootstrap time Picker -->
<link rel="stylesheet" href="plugins/timepicker/bootstrap-timepicker.min.css">
<!-- Select2 -->
<link rel="stylesheet" href="bower_components/select2/dist/css/select2.min.css">
<!-- Theme style -->
<link rel="stylesheet" href="dist/css/AdminLTE.min.css">
<!-- AdminLTE Skin -->
<link rel="stylesheet" href="dist/css/skins/skin-blue.min.css">
<!-- Google Font -->
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Source+Sans+Pro:300,400,600,700,300italic,400italic,600italic">
</head>
<body class="hold-transition skin-blue sidebar-mini">
<div class="wrapper">
<!--Include header-->
<?php include 'include/header.php' ?>
<!--Include sidebar-->
<?php include 'include/sidebar.php' ?>
<!-- Content Wrapper. Contains page content -->
<div class="content-wrapper">
<!-- Content Header (Page header) -->
<section class="content-header">
<h1 align='center'>
<u>Appointment Form</u>
</h1>
</section>
<!-- Main content -->
<section class="content">
<div class="box box-default">
<div class="box-body">
<div class="row">
<div class="col-md-12">
<!-- form start -->
<form class="form-horizontal" action="" method="POST">
<!--Name-->
<div class='form-group'>
<label for='Name' class='col-sm-2 control-label'>Name</label>
<div class='col-sm-10'>
<input type='text' class='form-control' name="student_name" id='student_name' value='<?php echo $name ?>' disabled>
<input type='hidden' class='form-control' name="student_id" id='student_id' value='<?=$_SESSION['username'] ?>' />
</div>
</div>
<!--Programme-->
<div class='form-group'>
<label for='Programme' class='col-sm-2 control-label'>Programme</label>
<div class='col-sm-10'>
<input type='text' class='form-control' id='programme' value='<?php echo $programme ?>' disabled>
</div>
</div>
<!--Consultation Type-->
<div class='form-group'>
<label for='ConsultationType' class='col-sm-2 control-label'>Consultation Type</label>
<div class='col-sm-10'>
<select class='form-control' name='appointment_type'>
<option selected='selected' name='assignment_discussion' value='Assignment Discussion'>Assignment Discussion</option>
<option name='advisory_session' value='Advisory Session'>Advisory Session</option>
<option name='fyp_discuss' value='Final Year Project Discussion'>Final Year Project Discussion</option>
</select>
</div>
</div>
<!--Field name-->
<div class='form-group'>
<label for='Course' class='col-sm-2 control-label'>Course Field</label>
<div class='col-sm-10'>
<select class='form-control' name='field_id' id='field_id'>
<?php
//Display field name
$select_field_query = "SELECT field_id, field_name FROM course_field
LEFT JOIN field_programme ON field_programme.field = course_field.field_id
LEFT JOIN users ON users.programme = field_programme.programme
LEFT JOIN programme ON programme.programme_id = field_programme.programme
WHERE field_programme.programme = :programme AND users.username = :student";
$field_statement = $db->prepare($select_field_query);
$field_statement->bindParam(':programme', $_SESSION['programme']);
$field_statement->bindParam(':student', $_SESSION['username']);
$field_statement->execute();
while ($row = $field_statement->fetch(PDO::FETCH_ASSOC))
{
echo
"<option value='" . $row['field_id'] . "'>" . $row['field_name'] . "</option>";
}
?>
</select>
</div>
</div>
<!--Lecturer name-->
<div class='form-group'>
<label for='Lecturer' class='col-sm-2 control-label'>Lecturer</label>
<div class='col-sm-10'>
<select class='form-control' name='lecturer_id' id='lecturer_id'>
<?php
//Displaying lecturer based on course field
$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 = :department
AND field_programme.programme= :programme";
$lect_smt = $db->prepare($select_lecturer_query);
$lect_smt->bindParam(':department', $_SESSION['department']);
$lect_smt->bindParam(':programme', $_SESSION['programme']);
$lect_smt->execute();
// $lecturer_data = $statement->fetchAll();
while ($row = $lect_smt->fetch(PDO::FETCH_ASSOC))
{
//echo
//"<option value='" . $data[0]['username'] ."'>" . $data[0]['name'] . "</option>";
echo "<option value='" . $row['lecturer_id'] ."'>" . $row['name'] . "</option>";
}
?>
</select>
</div>
</div>
<!-- Date -->
<div class='form-group'>
<label for='Date' class='col-sm-2 control-label'>Date</label>
<div class='col-sm-4'>
<div class='input-group date'>
<input type='text' class='form-control pull-right' name='appointment_date' id='datepicker' required>
<div class='input-group-addon'>
<i class='fa fa-calendar'></i>
</div>
</div><!-- /.input group -->
</div><!-- /.form group -->
</div>
<!-- Time -->
<div class='form-group'>
<label for='Time' class='col-sm-2 control-label'>Time</label>
<div class='col-sm-4'>
<div class='input-group time'>
<input type='text' class='form-control timepicker' name='appointment_time' id='timepicker' required>
<div class='input-group-addon'>
<i class='fa fa-clock-o'></i>
</div>
</div><!-- /.input group -->
</div>
</div><!-- /.form group -->
<!--Venue-->
<div class='form-group'>
<label for='Venue' class='col-sm-2 control-label'>Venue</label>
<div class='col-sm-10'>
<select class='form-control' name='venue'>
<option selected='selected' name='in_front_dpmt' value='In front of department'>In front of department</option>
<option name='library' value='Library'>Library</option>
<option name='consultation_room' value='Consultation Room'>Consultation Room</option>
</select>
</div>
</div>
<!--Remarks-->
<div class='form-group'>
<label for='Remarks' class='col-sm-2 control-label'>Remarks</label>
<div class='col-sm-10'>
<input type='text' class='form-control' name='remarks' id='remarks' placeholder='Eg: Related to the Local Area Network question' required>
</div>
</div>
<!--Hidden Appointment Status which is set as Active when form is submitted-->
<input type="hidden" value="Active" name="appointment_status">
<!--Submit button-->
<div class="box-footer">
<button type="submit" class="btn btn-success pull-right" name="btnSubmit" id="btnSubmit">Submit</button>
</div>
</form>
</div><!-- /.box -->
</div><!-- /.col -->
</div><!-- /.col -->
</div><!-- /.row -->
</div><!-- /.box-body -->
</div><!-- /.box -->
</div><!-- ./wrapper -->
<!-- jQuery 3 -->
<script src="bower_components/jquery/dist/jquery.min.js"></script>
<!-- Bootstrap 3.3.7 -->
<script src="bower_components/bootstrap/dist/js/bootstrap.min.js"></script>
<!-- Select2 -->
<script src="bower_components/select2/dist/js/select2.full.min.js"></script>
<!-- InputMask -->
<script src="plugins/input-mask/jquery.inputmask.js"></script>
<script src="plugins/input-mask/jquery.inputmask.date.extensions.js"></script>
<script src="plugins/input-mask/jquery.inputmask.extensions.js"></script>
<!-- date-range-picker -->
<script src="bower_components/moment/min/moment.min.js"></script>
<script src="bower_components/bootstrap-daterangepicker/daterangepicker.js"></script>
<!-- bootstrap datepicker -->
<script src="bower_components/bootstrap-datepicker/dist/js/bootstrap-datepicker.min.js"></script>
<!-- bootstrap color picker -->
<script src="bower_components/bootstrap-colorpicker/dist/js/bootstrap-colorpicker.min.js"></script>
<!-- bootstrap time picker -->
<script src="plugins/timepicker/bootstrap-timepicker.min.js"></script>
<!-- SlimScroll -->
<script src="bower_components/jquery-slimscroll/jquery.slimscroll.min.js"></script>
<!-- iCheck 1.0.1 -->
<script src="plugins/iCheck/icheck.min.js"></script>
<!-- FastClick -->
<script src="bower_components/fastclick/lib/fastclick.js"></script>
<!-- AdminLTE App -->
<script src="dist/js/adminlte.min.js"></script>
<!-- AdminLTE for demo purposes -->
<script src="dist/js/demo.js"></script>
<!-- Page script -->
<!-- Change lecturer when different course name is selected -->
<script>
$(function ()
{
//Initialize Select2 Elements
$('.select2').select2()
$('select[name="field_id"]').change
(
function()
{
// alert($(this).val());
var id = $(this).val();
$.ajax
(
{
method: "POST",
url: "ajax-lecturer.php",
data: { field_id: id }
}
)
.done
(function( lect )
{
//alert(lect);
var lectObj = JSON.parse(lect);
//alert(lectObj);
$('input[name="lecturer_name"]').val(lectObj.name);
$('input[name="lecturer_id"]').val(lectObj.username);
}
);
}
);
//Datemask dd/mm/yyyy
$('#datemask').inputmask('dd/mm/yyyy', { 'placeholder': 'dd/mm/yyyy' })
//Datemask2 mm/dd/yyyy
$('#datemask2').inputmask('mm/dd/yyyy', { 'placeholder': 'mm/dd/yyyy' })
//Money Euro
$('[data-mask]').inputmask()
//Date range picker
$('#reservation').daterangepicker()
//Date range picker with time picker
$('#reservationtime').daterangepicker({ timePicker: true, timePickerIncrement: 30, format: 'MM/DD/YYYY h:mm A' })
//Date range as a button
$('#daterange-btn').daterangepicker(
{
ranges : {
'Today' : [moment(), moment()],
'Yesterday' : [moment().subtract(1, 'days'), moment().subtract(1, 'days')],
'Last 7 Days' : [moment().subtract(6, 'days'), moment()],
'Last 30 Days': [moment().subtract(29, 'days'), moment()],
'This Month' : [moment().startOf('month'), moment().endOf('month')],
'Last Month' : [moment().subtract(1, 'month').startOf('month'), moment().subtract(1, 'month').endOf('month')]
},
startDate: moment().subtract(29, 'days'),
endDate : moment()
},
function (start, end) {
$('#daterange-btn span').html(start.format('MMMM D, YYYY') + ' - ' + end.format('MMMM D, YYYY'))
}
)
//Date picker
$( "#datepicker" ).datepicker({
dateFormat: "yyyy-mm-dd"
});
/*$('#datepicker').datepicker({
autoclose: true
})*/
//iCheck for checkbox and radio inputs
$('input[type="checkbox"].minimal, input[type="radio"].minimal').iCheck({
checkboxClass: 'icheckbox_minimal-blue',
radioClass : 'iradio_minimal-blue'
})
//Red color scheme for iCheck
$('input[type="checkbox"].minimal-red, input[type="radio"].minimal-red').iCheck({
checkboxClass: 'icheckbox_minimal-red',
radioClass : 'iradio_minimal-red'
})
//Flat red color scheme for iCheck
$('input[type="checkbox"].flat-red, input[type="radio"].flat-red').iCheck({
checkboxClass: 'icheckbox_flat-green',
radioClass : 'iradio_flat-green'
})
//Colorpicker
$('.my-colorpicker1').colorpicker()
//color picker with addon
$('.my-colorpicker2').colorpicker()
//Timepicker
$('.timepicker').timepicker({
showInputs: false
})
})
</script>
</body>
</html>
Your help is appreciated. Thank you.
Update: There are 2 more tables that are linked to the course_field table.
Below is the programme table. It is also linked to the users table. The programme_id is a foreign key to the programme column in the users table.
Below is the field_programme table. It combines the field together with the programme. The field column came from the field_id in the course_field table whereas the programme column came from the programme_id in the programme table
Students have their own programme (Eg: UCNT2) . They will make appointment based on the field in the programme. For example, UCNT2 student cannot make appointment for other course fields except for Computer Security and Networking Technologies.
When the appointment is made, it will go in the appointment table. Here is a sample of the records that will be in the appointment table:-
