douzai9405 2019-03-06 09:48
浏览 175

如何使用PHP和AJAX将数据绑定到HTML下拉列表(用于on change事件)

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:


        $smt = $db->prepare("SELECT, 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']);                      
        $data = $smt->fetchAll();

        echo json_encode($data[0]);
        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:

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):

course_field table

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:

drop-down list

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.

![lecturer with field

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. users 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.

enter image description here

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:

      require ("global-include.php");

      //Condition when the submit button is clicked
          //$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"]."',

          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>";
            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>
      <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=",400,600,700,300italic,400italic,600italic">
    <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>

        <!-- 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">

                        <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 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>

                        <!--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>

                        <!--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'>           
                              //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']);

                              while ($row = $field_statement->fetch(PDO::FETCH_ASSOC)) 
                                "<option value='" . $row['field_id'] . "'>" . $row['field_name'] . "</option>";

                        <!--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'>
                              //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']);          
                             // $lecturer_data = $statement->fetchAll();

                              while ($row = $lect_smt->fetch(PDO::FETCH_ASSOC)) 
                                //"<option value='" . $data[0]['username'] ."'>" . $data[0]['name'] . "</option>";
                                echo  "<option value='" . $row['lecturer_id'] ."'>" . $row['name'] . "</option>";

                        <!-- 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><!-- /.input group -->
                          </div><!-- /.form group -->    

                        <!-- 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><!-- /.input group -->
                        </div><!-- /.form group -->

                        <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>

                        <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>

                        <!--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><!-- /.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/"></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 -->
      $(function () 
        //Initialize Select2 Elements

            // alert($(this).val());
            var id = $(this).val();
                method: "POST",
                url: "ajax-lecturer.php",
                data: { field_id: id }

            (function( lect ) 
                var lectObj = JSON.parse(lect);

        //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

        //Date range picker
        //Date range picker with time picker
        $('#reservationtime').daterangepicker({ timePicker: true, timePickerIncrement: 30, format: 'MM/DD/YYYY h:mm A' })
        //Date range as a button
            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"
          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'

        //color picker with addon

          showInputs: false

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. programme

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 field_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:- appointment table

  • 写回答



      相关推荐 更多相似问题


      • ¥15 内存管理的一段代码不是很理解
      • ¥20 打开anaconda时卡在Loading applications无法进入界面
      • ¥15 网页超时时间设置失效
      • ¥15 有关绿色信贷毕业论文的问题
      • ¥30 关于#机器人#的问题,如何解决?
      • ¥15 求MATLAB函数ScalarLayerDisplay的代码
      • ¥15 安卓如何自动执行检测到的NFC标签,无需再点确认
      • ¥15 pyHM库mouse模块的ValueError错误
      • ¥15 python opencv 摄像头 传感器
      • ¥30 eMMC&Android&C&Linux