Question posted in
You can find the official documentation here.

php ajax dependent dropdown not loading data from table – Jquery ajax

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;

?>
Tags:

2

Answers


  1. 0

    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 ) );
    }
?>
    Login or Signup to reply.
  2. 0

    I have try this using php pdo.

    first i have create a 3 files.

    1. db.php

    2. htmlDropdown.php

    3. 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
}

?>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search