Populate Dropdown on the basis of another dropdown value in PHP using JQuery Ajax

#1

I have a form with dropdown list that is populated with values from Sql Server table. Now i would like to use this selected item in SQL query. The results of this query should be shown in label or text field. So when a user selects item from dropdown menu, results from SQL query are shown at the same time.

I have two dropdown list at the moment in my form. First one gets all values from a column in table in SQL Server. And the second one should get a value from the same table based on a selection in first dropdown list.

When i load ajax.php i get 2 error mesages:
Notice: Undefined index: machinery in C:\wamp64\www\gecko\ajaxfile.php on line 8
Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp64\www\site\ajaxfile.php on line 16

This is my code so far. I have tried to do it with this Ajax script. But i can only get first dropdown to work. The second dropdown(sub_machinery) does not show values, when first dropdown item is selected. The second dropdown should show values from databse table with this query( $machineryID is first dropdown selected item): SELECT MachineID FROM T013 WHERE Machinery=".$machineryID.

Index.php

<!doctype html>
  <?PHP
$server = "server";
$options = array(  "UID" => "user",  "PWD" => "pass",  "Database" => 
"database");
$conn2 = sqlsrv_connect($server, $options);
if ($conn2 === false) die("<pre>".print_r(sqlsrv_errors(), true));
echo " ";
?>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
</head>

<body>
		    <section id="formaT2" class="formaT2 formContent">
	        <div class="row">
	            <div class="col-md-2 col-3 row-color remove-mob"></div>
	            <div class="col-md-5 col-9 bg-img" style="padding-left: 0; 
padding-right: 0;">
	                <h1>Form</h1>
	                <div class="rest-text">
	                   <div class="contactFrm">
		                  <p class="statusMsg <?php echo 
!empty($msgClass)?$msgClass:''; ?>"><?php echo $statusMsg; ?></p>
						   
		                    <form action="connection.php" method="post">
								<div>machinery</div>     
<select id="machinery">
   <option value="0">--Please Select Machinery--</option>
   <?php 
   // Fetch Department
   $sql = "SELECT Machinery FROM T013";
   $machanery_data = sqlsrv_query($conn2,$sql);
   while($row = sqlsrv_fetch_array($machanery_data) ){
      $id = $row['Id'];
      $machinery = $row['Machinery'];
      
      // Option
      echo "<option value='".$id."' >".$machinery."</option>";
   }
   ?>
</select>
<div class="clear"></div>
								<div>Sub Machinery</div>
<select id="sub_machinery">
   <option value="0">- Select -</option>
</select>
								
		                        <input type="submit" name="submit" 
id="submit" class="strelka-send" value="Insert">
		                        <div class="clear"> </div>
		                    </form>
 		                </div> 
 	                </div>
	            </div>
	        </div> 
	    </section>
     	</script>
	  <script type="text/javascript">
	  $(document).ready(function(){

    $("#machinery").change(function(){
        var machinery_id = $(this).val();

        $.ajax({
			url:'ajaxfile.php',
            type: 'post',
            data: {machinery:machinery_id},
            dataType: 'json',
            success:function(response){

                var len = response.length;
 
                $("#sub_machinery").empty();
                for( var i = 0; i<len; i++){
                    var machinery_id = response[i]['machinery_id'];
                    var machinery = response[i]['machinery'];
                    
                    $("#sub_machinery").append("<option 
value='"+machinery_id+"'>"+machinery+"</option>");

                }
            }
        });
    });

});
	  </script>
</body>
</html>

Ajaxfile.php

<?php
$server = "server";
$options = array(  "UID" => "user",  "PWD" => "pass",  
"Database" => "database");
$conn2 = sqlsrv_connect($server, $options);
if ($conn2 === false) die("<pre>".print_r(sqlsrv_errors(), true));
echo " ";

$machineryID = $_POST['machinery'];   // department id

$sql = "SELECT MachineID FROM T013 WHERE Machinery=".$machineryID;

$result = sqlsrv_query($conn2,$sql);

$machinery_arr = array();

while( $row = sqlsrv_fetch_array($result) ){
    $machinery_id = $row['ID'];
    $machinery = $row['MachineID'];

    $machinery_arr[] = array("ID" => $machinery_id, "MachineID" => 
$machinery);
}

// encoding array to json format
echo json_encode($machinery_arr);
?>

UPDATE
Okey so i changed $sql = "SELECT MachineID FROM T013 WHERE Machinery=".$machineryID; to $sql = "SELECT MachineID FROM T013 WHERE Machinery='$machineryID'";
And removed ID field from the machinery dropdown. Now i can see in Google dev tools that the query is called correctly in ajaxfile.php. But the value in sub_machinery dropdown is undefined

SOLVED
changed my code to:

AJAX CALLBACK IN INDEX.PHP:

<script>
    $(document).ready(function(){
        $("#machinery").change(function(){
            $.ajax({
                url:'ajaxfile.php',
                type:'post',
                data:{ machinery:$(this).val()},
                dataType:'json',
                success:function(response){
                    $("#sub_machinery").empty();

                    for( let n in response ){
                        let obj=response[n];
                        $("#sub_machinery").append( new Option( obj.id, obj.id ) );
                    }
                }
            });
        });

    });
</script>

AJAXFILE.PHP:

<?php

     $server = "server";
$options = array(  "UID" => "user",  "PWD" => "pass",  "Database" => "db");
$conn2 = sqlsrv_connect($server, $options);
if ($conn2 === false) die("<pre>".print_r(sqlsrv_errors(), true));
echo " ";

    $id = $_POST['machinery'];
    $sql = "SELECT MachineID FROM T013 WHERE Machinery='$id'";
    $result = sqlsrv_query($conn2,$sql);

    $output=array();

    while( $row = sqlsrv_fetch_array( $result ) ){
        $output[] = array( 'id' => $row['MachineID'] );
    }


    header('Content-Type: application/json');
    exit( json_encode( $output ) );
?>
#2

I would still urge you to use PDO, it does work with SQL Server.

You get a boolean on the query because it fails. If I had to guess I would say it is the concatenated query (which is why you should be using prepared statements by the way).

What is the column type of this field, Machinery? Doesn’t look like it is an integer.

#3

Thanks i will look into that. Machinery column is Varchar type.

#4

That’s what I was expecting. That means the variable has to be surrounded by quotes. It is the same as trying to run it in MSMS like this,

SELECT MachineID FROM T013 WHERE Machinery=abcd123;
1 Like
#5

If i put a variable in quotes like this: $sql = "SELECT ID,MachineID FROM T013 WHERE Machinery='.$machineryID'"; The second dropdown appears to be empty. And if i check the network activity with Google developer, I can see that ajaxfile.php gets fired, but value is []

If i put echo $sql; after my $sql query variable i get this in Google dev tools in ajaxfile.php after i click first dropdown: SELECT ID,MachineID FROM T013 WHERE Machinery='.1'[]

If i put something in query that i am sure will get result from database, i get a “undefined” value in second dropdown. But with Google dev tools i can see that it returns the right query result.

#6
$sql = "SELECT MachineID FROM T013 WHERE Machinery= ?";
$stmt = sqlsrv_prepare($conn2,$sql, [$_POST['machinery']]);
$result = sqlsrv_execute($stmt);
$row = sqlsrv_fetch_array($result);
print_r($row);
1 Like
#7

This link may be useful to you, https://www.php.net/manual/en/function.sqlsrv-prepare.php

#8

Just FYI since I’ve shown you how you should be doing the queries,

This translates to,

SELECT ID,MachineID FROM T013 WHERE Machinery=’.10’