php ajax dependent dropdown not loading data from table

Question

I am trying to create a dependent dropdown using php and ajax. What I am expecting is when the 'Make' of car is selected the relevant car models should automatically load on the 'Model' dropdown. I have manged to do the preloading of 'Make' of cars. But the 'Model' dropdown remains empty. I have used a single tale and in sql statement used (select model where make= selected make). here is my code

php

<form method="GET">
                            <div class="form-group">
                                <select class="form-control" name="make" id="make">
                                    <option value="" disabled selected>--Select Make--</option>
                                        <?php
                                            $stmt=$pdo->query("SELECT DISTINCT make FROM cars  WHERE cartype='general' ");
                                        while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
                                        ?>
                                        <option value="<?= $row['make']; ?>"> <?= $row['make']; ?></option>
                                        
                                        <?php  } ?>
                                </select>
                            </div>
                            <div class="form-group">
                                <select class="form-control" name="model" id="model">
                                    <option value="" disabled selected>--Select Model--</option>
                                </select>
                            </div>
.......
....
.....

script

<script type="text/javascript">
        $(document).ready( function () {
           // alert("Hello");
        $(#make).change(function(){
            var make = $(this).val();
            $.ajax({
                url:"filter_action.php",
                method:"POST",
                data:{Make:make},
                success: function(data){
                    $("#model").html(data);
            });
        });
    });
    
</script>

filter_action.php

<?php 
    include('db_config2.php');
    $output='';
    $stmt=$pdo->query("SELECT DISTINCT model FROM cars  WHERE cartype='general' AND make= '".$_POST['Make']."'");
    $output .='<option value="" disabled selected>--Select Model--</option>';
        while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
            $output .='<option value="'.$row["model"].'">'.$row["model"].'</option>'    ;
        }
        echo $output;

?>

This `$(#make)` should in quotes i.e :`$("#make")` . Also, check browser console is there any other errors ? — Swati, Dec 20 '20 at 13:54
You have made your SQL vulnerable to injection attacks by directly embedding user supplied input data — Professor Abronsius, Dec 20 '20 at 13:55
@Swati thanks. yes I have changed it to $("#make") but the result is still the same — S.N.Alam, Dec 20 '20 at 14:08
@KUMAR no, the 'make' dropdown is working but the 'model' dropdown is still empty — S.N.Alam, Dec 20 '20 at 14:15
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Dec 20 '20 at 15:47

score 0 · Answer 1 · answered Dec 20 '20 at 14:13

There appeared to be a couple of mistakes in the Javascript that would have been obvious in the developer console and your PHP had left the mySQL server vulnerable to sql injection attacks.

<script>
    $(document).ready( function () {
    
        // The string should be within quotes here
        $('#make').change(function(e){
            var make = $(this).val();
            
            $.ajax({
                url:"filter_action.php",
                method:"POST",
                data:{'Make':make},
                success: function(data){
                    $("#model").html(data);
                };//this needed to be closed
            });
        });
    });
</script>

The direct use of user supplied data within the sql opened your db to sql injection attacks. To mitigate this you need to adopt "Prepared Statements" - as you are using PDO anyway this should be a matter of course.

<?php
    if( $_SERVER['REQUEST_METHOD']=='POST' && !empty( $_POST['Make'] ) ){
    
        # The placeholders and associated values to be used when executing the sql cmd
        $args=array(
            ':type'     =>  'general',  # this could also be dynamic!
            ':make'     =>  $_POST['Make']
        );
        # Prepare the sql with suitable placeholders
        $sql='select distinct `model` from `cars`  where `cartype`=:type and `make`=:make';
        $stmt=$pdo->prepare( $sql );
        
        # commit the query
        $stmt->execute( $args );
        
        # Fetch the results and populate output variable
        $data=array('<option disabled selected hidden>--Select Model--');
        while( $rs=$stmt->fetch(PDO::FETCH_OBJ) )$data[]=sprintf('<option value="%1$s">%1$s', $rs->model );
        
        # send it to ajax callback
        exit( implode( PHP_EOL,$data ) );
    }
?>

thanks a lot for your time and help. unfortunately it did not work. i appreciate you suggestion for prepared stmt to prevent sql injection. basically I copied your code but, still no result. — S.N.Alam, Dec 20 '20 at 16:54
can you open the dev console and report any errors observed? — Professor Abronsius, Dec 20 '20 at 19:00
I am not sure if you would like the idea. I am coding on cpanel, not on local server. — S.N.Alam, Dec 20 '20 at 19:04
? NFI - never seen cPanel. Was just wondering if there were any js errors — Professor Abronsius, Dec 20 '20 at 19:18

score 0 · Answer 2 · answered Dec 21 '20 at 06:19

I have try this using php pdo.

first i have create a 3 files.

db.php
htmlDropdown.php
modelAjax.php

here, db.php file can contain my database connection code. and htmlDropdown.php file contain my dropdown for car and models. and modelAjax.php file contain ajax to fetch all models.

db.php

<?php

$host_name = 'localhost';
$user_name = 'root';
$password = '';
$db_name = 'stackoverflow';

$conn = new PDO("mysql:host=$host_name; dbname=$db_name;", $user_name, $password);

?>

htmlDropdown.php

<?php include "db.php"; ?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Cars</title>
    <!-- jQuery cdn link -->
    <script src="https://code.jquery.com/jquery-3.5.1.js" integrity="sha256-QWo7LDvxbWT2tbbQ97B53yJnYU3WhH/C8ycbRAkjPDc=" crossorigin="anonymous"></script>
    <!-- Ajax cdn link -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-ajaxy/1.6.1/scripts/jquery.ajaxy.min.js" integrity="sha512-bztGAvCE/3+a1Oh0gUro7BHukf6v7zpzrAb3ReWAVrt+bVNNphcl2tDTKCBr5zk7iEDmQ2Bv401fX3jeVXGIcA==" crossorigin="anonymous"></script>
</head>
<body>
    <?php
        $car_sql = 'SELECT car_name FROM cars';  //select all cars query
        $cars_statement = $conn->prepare($car_sql);
        $cars_statement->execute();
    ?>
    <select name="car" id="car">
        <option value="">Cars</option>
        <?php
            while ($cars = $cars_statement->fetch()) { // fetch all cars data
            ?>
                <option value="<?php echo $cars['car_name']; ?>"><?php echo $cars['car_name']; ?></option>
            <?php
            }
        ?>
    </select><br><br>

    <select name="model" id="model">
        <option value="">Model</option>
    </select>
</body>
</html>
<script>

    $(document).ready(function () {

        $('#car').on("change", function () {

            let car = $(this).val(); // car value

            $.post("http://local.stackoverflowanswer1/cars/modelAjax.php", { car_name : car }, function (data, status) { // ajax post send car name in modelAjax.php file
                
                let datas = JSON.parse(data); // convert string to json object

                let options = '';

                options = '<option>Model</option>';

                $.each(datas.model, function (key, value) {
                    options += "<option>"+value.modal_name+"</option>";
                });

                $('#model').html(options);

            });
        });
    });
</script>

modelAjax.php

<?php

include "db.php";

if ($_POST['car_name']) 
{
    $car_id_sql = "SELECT id FROM cars WHERE car_name LIKE ?"; // get id from given car name
    $id_statement = $conn->prepare($car_id_sql);
    $id_statement->execute([$_POST['car_name']]);

    $id = $id_statement->fetch();

    $model_sql = "SELECT modal_name FROM models WHERE car_id = ?"; // get model name from given id
    $model_statement = $conn->prepare($model_sql);
    $model_statement->execute([$id['id']]);

    $models = $model_statement->fetchAll();

    echo json_encode(["model" => $models]); // i have a conver array to json object
}

?>

thanks a lot. I really appreciate your time and effort. What I needed is to keep make and model in the same table and use 'make' instead of 'id'. something like (select model from cars where make=selected make). can you help please? — S.N.Alam, Dec 21 '20 at 10:12

php ajax dependent dropdown not loading data from table

2 Answers2