Asked  7 Months ago    Answers:  3   Viewed   24 times

I got a database table called category as shown:

enter image description here

I am trying to do a dynamic drop down box and the index script is shown as:

<?php

try {

$objDb = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$objDb->exec('SET CHARACTER SET utf8');

$sql = "SELECT * 
        FROM `category`
        WHERE `master` = 0";
$statement = $objDb->query($sql);
$list = $statement->fetchAll(PDO::FETCH_ASSOC);

    } catch(PDOException $e) {
echo 'There was a problem';
    }

    ?>
    <!DOCTYPE HTML>
   <html lang="en">
   <head>
<meta charset="utf-8" />
<title>Dependable dropdown menu</title>
<meta name="description" content="Dependable dropdown menu" />
<meta name="keywords" content="Dependable dropdown menu" />
<link href="/css/core.css" rel="stylesheet" type="text/css" />
<!--[if lt IE 9]>
<script src="http://html5shiv.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->
<script src="/js/jquery-1.6.4.min.js" type="text/javascript"></script>
  <script src="/js/core.js" type="text/javascript"></script>
  </head>
  <body>

  <div id="wrapper">

<form action="" method="post">

    <select name="gender" id="gender" class="update">
        <option value="">Select one</option>
        <?php if (!empty($list)) { ?>
            <?php foreach($list as $row) { ?>
                <option value="<?php echo $row['id']; ?>">
                    <?php echo $row['name']; ?>
                </option>
            <?php } ?>
        <?php } ?>
    </select>

    <select name="category" id="category" class="update"
        disabled="disabled">
        <option value="">----</option>
    </select>

    <select name="colour" id="colour" class="update"
        disabled="disabled">
        <option value="">----</option>
    </select>       
</form>
</div>
</body>
</html>

The update.php is shown as:

<?php
if (!empty($_GET['id']) && !empty($_GET['value'])) {

$id = $_GET['id'];
$value = $_GET['value'];

try {

    $objDb = new PDO('mysql:host=localhost;dbname=test', 'root', '');
    $objDb->exec('SET CHARACTER SET utf8');

    $sql = "SELECT * 
            FROM `category`
            WHERE `master` = ?";
    $statement = $objDb->prepare($sql);
    $statement->execute(array($value));
    $list = $statement->fetchAll(PDO::FETCH_ASSOC);

    if (!empty($list)) {

        $out = array('<option value="">Select one</option>');

        foreach($list as $row) {
            $out[] = '<option        
value="'.$row['id'].'">'.$row['name'].'</option>';
        }

        echo json_encode(array('error' => false, 'list' => implode('', 
$out)));

    } else {
        echo json_encode(array('error' => true));
    }

} catch(PDOException $e) {
    echo json_encode(array('error' => true));
}

} else {
echo json_encode(array('error' => true));
}

The 2nd drop down box is not showing the values dependent on the 1st drop down box as shown:

enter image description here

Can someone help me please.

 Answers

48

Here is an example that will do what you want. Essentially, you can use jQuery / AJAX to accomplish this.

I updated my example code to match your server login / table / field names, so if you copy/paste these two examples into files (call them tester.php and another_php_file.php) then you should have a fully working example to play with.

I modified my example below to create a second drop-down box, populated with the values found. If you follow the logic line by line, you will see it is actually quite simple. I left in several commented lines that, if uncommented (one at a time) will show you what the script is doing at each stage.

FILE 1 -- TESTER.PHP

<html>
    <head>
        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
        <script type="text/javascript">
            $(function() {
//alert('Document is ready');

                $('#stSelect').change(function() {
                    var sel_stud = $(this).val();
//alert('You picked: ' + sel_stud);

                    $.ajax({
                        type: "POST",
                        url: "another_php_file.php",
                        data: 'theOption=' + sel_stud,
                        success: function(whatigot) {
//alert('Server-side response: ' + whatigot);
                            $('#LaDIV').html(whatigot);
                            $('#theButton').click(function() {
                                alert('You clicked the button');
                            });
                        } //END success fn
                    }); //END $.ajax
                }); //END dropdown change event
            }); //END document.ready
        </script>
    </head>
<body>

    <select name="students" id="stSelect">
        <option value="">Please Select</option>
        <option value="John">John Doe</option>
        <option value="Mike">Mike Williams</option>
        <option value="Chris">Chris Edwards</option>
    </select>
    <div id="LaDIV"></div>

</body>
</html>

FILE 2 - another_php_file.php

<?php

//Login to database (usually this is stored in a separate php file and included in each file where required)
    $server = 'localhost'; //localhost is the usual name of the server if apache/Linux.
    $login = 'root';
    $pword = '';
    $dbname = 'test';
    mysql_connect($server,$login,$pword) or die($connect_error); //or die(mysql_error());
    mysql_select_db($dbname) or die($connect_error);

//Get value posted in by ajax
    $selStudent = $_POST['theOption'];
    //die('You sent: ' . $selStudent);

//Run DB query
    $query = "SELECT * FROM `category` WHERE `master` = 0";
    $result = mysql_query($query) or die('Fn another_php_file.php ERROR: ' . mysql_error());
    $num_rows_returned = mysql_num_rows($result);
    //die('Query returned ' . $num_rows_returned . ' rows.');

//Prepare response html markup
    $r = '  
            <h1>Found in Database:</h1>
            <select>
    ';

//Parse mysql results and create response string. Response can be an html table, a full page, or just a few characters
    if ($num_rows_returned > 0) {
        while ($row = mysql_fetch_assoc($result)) {
            $r = $r . '<option value="' .$row['id']. '">' . $row['name'] . '</option>';
        }
    } else {
        $r = '<p>No student by that name on staff</p>';
    }

//Add this extra button for fun
    $r = $r . '</select><button id="theButton">Click Me</button>';

//The response echoed below will be inserted into the 
    echo $r;

To answer your question in the comment: "How do you make the 2nd drop down box populate fields that are only relevant to a selected option from the 1st drop down box?"

A. Inside the .change event for the first dropdown, you read the value of the first dropdown box:

$('#dropdown_id').change(function() {
var dd1 = $('#dropdown_id').val();
}

B. In your AJAX code for the above .change() event, you include that variable in the data you are sending to the 2nd .PHP file (in our case, "another_php_file.php")

C. You use that passed-in variable in your mysql query, thereby limiting your results. These results are then passed back to the AJAX function and you can access them in the success: portion of the AJAX function

D. In that success function, you inject code into the DOM with the revised SELECT values.

That is what I am doing in the example posted above:

  1. The user chooses a student name, which fires the jQuery .change() selector

  2. Here is the line where it grabs the option selected by the user:

    var sel_stud = $(this).val();

  3. This value is sent to another_php_file.php, via this line of the AJAX code:

    data: 'theOption=' + sel_stud,

  4. The receiving file another_php_file.php receives the user's selection here:

    $selStudent = $_POST['theOption'];

  5. Var $selStudent (the user's selection posted in via AJAX) is used in the mysql search:

    $query = " SELECT * FROM `category` WHERE `master` = 0 AND `name` = '$selStudent' ";

    (When changing the example to suit your database, the reference to $selStudent was removed. But this (here, above) is how you would use it).

  6. We now build a new <SELECT> code block, storing the HTML in a variable called $r. When the HTML is fully built, I return the customized code back to the AJAX routine simply by echoing it back:

    echo $r;

  7. The received data (the customized <SELECT> code block) is available to us inside the AJAX success: function() {//code block}, and I can inject it into the DOM here:

    $('#LaDIV').html(whatigot);

And voila, you now see a second dropdown control customized with values specific to the choice from the first dropdown control.

Works like a non-Microsoft browser.

Wednesday, March 31, 2021
 
Bono
answered 7 Months ago
45

Please go through this link dependent dropdown using jquery ajax

How he maintained the relations among the entities

let me explain you if you want countries based on tour then you need to relate the country table with tour table as you have shown the country table in image it contains only two columns countries_id and countries_name you have to add one more column that is tour_type_id when you select any tour you will get the tour_type_id then your query should be

SELECT * FROM `countries`  where `tour_type_id` = 1 //this is the id you will get from the tour_type select box

and this will populate the related countries same case for the destination related this table with country_id

Hope it makes sense

Wednesday, March 31, 2021
 
kwhohasamullet
answered 7 Months ago
25

Your identifier in your Javascript is #tour_type when your id is #tourtype.

If the syntax is correct and your SQL results are correct too, it should work.

EDIT: some of your JS isn't right.

data: "&id=" + id + "&get_countries=1",

should be

data: {id: id, get_countries: 1},

You should also put a debug on your ajax call by adding

error: function () { alert("ajax failed"); }

after your success callback

full sources now:

$('#tourtype').change(function() {
    var id=$(this).val();
    $.ajax
    ({
        type: "POST",
        url:"jurassicbase5/admin/ajax.php",
        data: {id: id, get_countries: 1},
        success: function(html){
        $("#country").empty();
        $("#country").append(html);
        },
        error: function () { alert("ajax failed"); }

    });
});

$('#country').change(function() {
    var id=$(this).val();
    $.ajax
    ({
        type: "POST",
        url: "jurassicbase5/admin/ajax.php",
        data: {id: id, get_destination: 1},
        success: function(html)
        {
            $("#destination").empty();
            $("#destination").append(html);
        },
        error: function () { alert("ajax failed"); }
    });
});
Saturday, May 29, 2021
 
Avicinnian
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :