douzai9405
douzai9405
2019-03-06 09:48

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

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

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:

    <?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. 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 duancha1065 2年前
    $("#field_id").change(function () {
        $.ajax({
            type: 'POST',
            url: 'ajax-lecturer.php',
            data: { 'field_id' : $('#field_id option:selected').val() },
            success: function(data) {
                $('#lecturer_id').empty();
                var lect = JSON.parse(data);
                $.each(lect, function( index, lectObj ) {
                    $('#lecturer_id').append("<option value='"+lectObj.username+"'>"+lectObj.name+"</option>");
                });
            },
            error: function(data) {
                alert(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.

    点赞 评论 复制链接分享
  • dse55384 dse55384 2年前

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

    ?>

    点赞 评论 复制链接分享