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

如何使用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

  • 写回答

2条回答 默认 最新

  • duancha1065 2019-03-07 15:04
    $("#field_id").change(function () {
            type: 'POST',
            url: 'ajax-lecturer.php',
            data: { 'field_id' : $('#field_id option:selected').val() },
            success: function(data) {
                var lect = JSON.parse(data);
                $.each(lect, function( index, lectObj ) {
                    $('#lecturer_id').append("<option value='"+lectObj.username+"'>""</option>");
            error: function(data) {

    Try adding the above code in your javascript section.

    Note that the returned JSON data has to have both the username and name property from your query.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?



  • ¥15 华为ensp使用基本ACL限制公司网络访问
  • ¥15 帮我做下照片上的PLC题
  • ¥15 labview2022 使用modbus报缺少依赖?
  • ¥15 谷歌地图是不是不开通结算功能,api会使用不了哦
  • ¥15 unity腾讯云对象存储机型适配
  • ¥15 求全国交通咨询模拟代码,要求如下,可以完全在dev c++运行
  • ¥15 根据要求修改程序编码
  • ¥15 用 Python 做一个用 Excel 表导入的答题系统
  • ¥15 使用微信开发者工具实现一个“婚博会”小程序
  • ¥15 ros的rviz仿真机器人