How to autopopulate a drodown based on the selection in another dropdown in php?

I have read a few similar questions but didn’t find the solution. I am trying to fetch a dropdown based on the selection of another dropdown. The first dropdown is school names, which upon selection should fetch the users under that particular school.

There are two tables in the database. The first one has school name column named schoolname and another table called person has a column named school which is the foreign key and has first name and last name too which should be fetched upon selection.

I am refering to this tutorial (https://makitweb.com/how-to-autopopulate-dropdown-with-ajax-pdo-and-php/)

I tried the below code:

queries.php

class Queries {
public static function getSchool() {   
    $dbUser = "xxx";
    $dbPass = "xxxx";
    $dbConn = "(DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)(HOST=xxxx)(PORT=1521))(CONNECT_DATA=(SID=xxxx)))";
    $conn = oci_connect($dbUser, $dbPass, $dbConn);
    $sql = oci_parse($conn,"SELECT  a.School,  a.SchoolName FROM SchoolName a WHERE a.FormDisplay = 'Y' ORDER BY a.SchoolName");
        return $sql;   
}  
}

form.php

<?php
require_once './functions/queries.php';
$getschool = new Queries();
?>

<div class="container">
<div class="panel panel-default">
    <div class="panel-body">
        <div id="addroles" class="hide" role="alert">
            <button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">&times;</span></button>
            <div id="resultRoleContent"></div>
        </div>  
             <form class="cmxform" action ='functions/processform.php' id="Form1" method="post">
           <legend> Form</legend>
            <label for="addname">Please Select School</label>
           <select class="form-control" name="school" id="school">
                <?php
                $nameslist = $getschool->getSchool();
                oci_execute($nameslist, OCI_DEFAULT);
                while ($row = oci_fetch_array($nameslist, OCI_ASSOC+OCI_RETURN_NULLS)) {
                    echo '<option value="' . $row['SCHOOLNAME'] . '">' . $row['SCHOOLNAME']. '</option>';
                }
                ?>
           </select>
            <label for="addname">Please Select Name</label>
           <select class="form-control" name="names" id="names">
                <?php
                
              
                ?>
           </select>
        </form>

    </div>
   </div>       
</div>

<script>
$(document).ready(function(){
 $('#school').change(function(){
 var schoolname = $(this).val();
 $('#names').find('option').not(':first').remove();

 // AJAX request
 $.ajax({
   url: 'getUsers.php',
   type: 'post',
   data: {request: 1, primaryschool: schoolpropername},
   dataType: 'json',
   success: function(response){
     var len = response.length;
     for( var i = 0; i<len; i++){
       var id = response[i]['primaryschool'];
       var firstname = response[i]['firstName'];
       var lastname = response[i]['lastName'];

       $("#names").append("<option value='"+id+"'>"+firstname+"</option>");

     }
   }
 });

  });
  });
</script>

getUsers.php

<?php
$dbUser = "xxxx";
$dbPass = "xxxx";
$dbConn = "(DESCRIPTION = (ADDRESS = (PROTOCOL=TCP)(HOST=xxxx)(PORT=1521))(CONNECT_DATA=(SID=xxxx)))";
$conn = oci_connect($dbUser, $dbPass, $dbConn);
$request = 0;
if(isset($_POST['request'])){
   $request = $_POST['request'];
}
if($request == 1){
   $schoolname = $_POST['school'];
   $sql =oci_parse($conn,"SELECT * FROM person ");
   oci_bind_by_name($sql, ':primaryschool', $schoolname);
   $result = oci_execute($sql);
   $response = array();
   foreach($result as $row){
       $response[] = array(
    "firstname" => $row['firstname']
      );
   }
   echo json_encode($response);
    exit;
 }

In the above code I am getting the following three errors:

PHP Notice: Undefined index: school in getusers.php 2.PHP Warning: oci_bind_by_name(): ORA-01036: illegal variable name/number in getusers.php 3.PHP Warning: oci_bind_by_name(): ORA-01036: illegal variable name/number in getusers.php

Answer

Undefined error occurred because school value is not getting in the $_POST array. Print the $_post array and check in which index you are getting school value.
You haven’t mentioned column name in WHERE clause in your query to fetch the details of particular school. So you are getting that warning. Your query should be like as below.

Assuming column name as schoolname in your table.

$sql =oci_parse($conn,"SELECT * FROM person WHERE  schoolname = :primaryschool");
oci_bind_by_name($sql, ':primaryschool', $schoolname);

Advertisement

Answer