How to define variable of drop down value to use in another drop down - php

Question

I've been looking at a dozen or more php mysql jquery ajax tutorial sites/plugins but all of them seem to be hard coding the values or value sets into ajax/jquery/json rather than just link the drop downs as I require. I also checked stackoverflow whether there was anything, but the examples were all hardcoded.

Desired workflow

1. Select value in drop down 1
2. define a variable in drop down 2 or in mysql select that takes value from drop down 1 and limit the options or change the options as required

Relevant Database Structure

Table : price_change 
ID | price_word  | amount     | max_
3  | 10 Lacs     | 1000000    | 0
4  | 25 Lacs     | 2500000    | 0
1  | >10 Lacs    | 999999     | 0
2  | 1 Crore     | 10000000   | 1
11 | 10 Crores   | 100000000  | 1
10 | 5 Crores    | 50000000   | 1
7  | 1.25 Crores | 12500000   | 2
8  | 1.5 Crores  | 15000000   | 2
9  | 2 Crores    | 20000000   | 2
5  | 50 Lacs     | 5000000    | 2
6  | 75 Lacs     | 7500000    | 2

I have a prepared statement that selects the data and displays results as per the query

function db_select($query) {
    $rows = array();
    $result = db_query($query);

    // If query failed, return `false`
    if($result === false) {
        return false;
    }

    // If query was successful, retrieve all the rows into an array
    while ($row = mysqli_fetch_assoc($result)) {
        $rows[] = $row;
    }
    return $rows;
}

Current code for generating values for drop down 1

<select name="minprice" id="minprice" >
    <?php
        $rows = db_select("SELECT amount,price_word FROM price_change where max_<>'1' ");
        foreach($rows as $row){
            echo "<option value='".$row['amount']."'>".$row['price_word']."</option>";
        }
    ?>
</select>

Current code for generating values for drop down 2

<select name="maxprice" id="maxprice">
    <?php
        $rows = db_select("SELECT amount,price_word FROM price_change where max_<>'0' ");
        foreach($rows as $row){
            echo "<option value='".$row['amount']."'>".$row['price_word']."</option>";
        }
    ?>
</select>

In order for drop down 2 to always be more than drop down 1, I need to define a variable that can be used to ensure this. so ideally it would be like this SELECT amount,price_word FROM price_change where max_<>'0' AND amount>=$dropdown1

I know this will involve some ajax/jquery but I want to ensure that the code is reusable so I don't hard code the values or select elements.

Can someone please point me in the right direction as I have been trying to do this for hours

I hope there is enough information to assist me in a solution, but if I missed something and if there is a better way to do this, please let me know.

EDIT - This works - http://www.plus2net.com/php_tutorial/dd.php but the only problem is that it runs a simulation of page refresh with new url which I don't want. I basically want the URL to be the same. But this is a step in the right direction as it doesn't hard code the values or selects or anything

Answer 1

You should focus on your code indentation :)

I believe related select is what you are looking for and here is how it can be done.

If what you want is to filter the value of drop-down 2 based on the selection of a value from drop-down 1, we can go about like this.

//Drop-down1
<select name="minprice" id="minprice" >
    <?php
        $rows = db_select("SELECT amount,price_word FROM price_change where max_<>'1' ");
        foreach($rows as $row){
            echo "<option value='".$row['amount']."'>".$row['price_word']."</option>";
        }
    ?>
</select>

//Drop-down2
<select name="maxprice" id="maxprice">
    <?php
        $rows = db_select("SELECT amount,price_word FROM price_change where max_<>'0' ");
        foreach($rows as $row){
            echo "<option value='".$row['amount']."'>".$row['price_word']."</option>";
        }
    ?>
</select>

At this point we require javascript/ajax to do the job for us, what we'll do is.

• Capture "on-change" trigger from the first select element.
• Send an ajax request along with the selected value from first element.
• On receiving the response, re-populate drop-down 2 with the new data.

Although, ajax can be implemented using plain JavaScript, I prefer jQuery to do the job, hence make sure in the same page, you have included the jQuery library, otherwise the code won't work.

<script type="text/javascript">
    //Execute this when DOM is loaded.
    $(document).ready(function(){
        //Trigger an on-change even from the first drop-down.
        $('#minprice').on('change', function() {
             //Fetch the current selected value.
             var value = $(this).val();
             //Send an ajax request with the selected value.
             $.ajax({
                 url: 'ajax.php',
                 data: 'minprice='+value,
                 method: 'POST',
                 success: function(response) {
                     //Received response from the script, time to re-populate drop-down2 with the new content received from the script.
                     //Step1- Remove all existing options from drop-down 2
                     $('#maxprice').find('option').remove();
                     //Step2 - Populate drop-down 2 with received content using the loop.
                     $.each(response, function(index, value)) {
                         $('#maxprice').append('<option value="'+index+'">'+value+'</option>');
                     }
                 }
             });
        });
    });
</script>

Lastly, you need to create a file named ajax.php, so you can receive and send the server content, ajax.php can be something similar to this.

//Filename: ajax.php
//Store the received value in a variable.
$minprice = !empty($_POST['minprice']) ? (int)$_POST['minprice'] : die('empty minprice');
//Initialize an empty array to return.
$result = array();

//Run the database query with the minprice value and assign retrieved value to $result, in the end echo $result in this file, like the following.

//Make sure the array returned is in following format.
$result = array(
    '1000000' => '10 Lacs',
    '2500000' => '25 Lacs',
);
echo $result.

PS: This code is not tested, but hoping it should run.

Hope this helps.