Pulling data from SQL database depending on form input

Question

I am working on a form in which changing one "select" element modifies the values of another "select" element. The values of both the elements come from a MSSQL database. What is the best way to implement code that can accomplish this?

There are two ways that I can think to do it.

Store the table into a javascript variable and make the onchange event of the first element modify the second element.
Send a GET request to the page and reload it, using PHP to modify the second element.

I don't like the first method because storing the database from the PHP side to the javascript side seems kind of hacky and really cumbersome to do. I don't like the second way either, because reloading the page disrupts the user experience and makes him have to scroll down again.

ajax. Use ajax, and there will be no page reload. Try Jquery ajax functions, to make it easier. — sinisake
– sinisake, Commented Jun 20, 2013 at 21:43
OK will do, thanks for the quick answers. Time to learn jquery/ajax! — user2066880
– user2066880, Commented Jun 20, 2013 at 21:50
I provided a stand-alone example that should be a very quick study. Just copy/paste into two files, and run. Instead of doing a MySQL lookup that you would need to modify, I just used if(conditions) to return different HTML based on selection made in first select box. Hope you find it a simple, straight-forward example. — cssyphus
– cssyphus, Commented Jun 20, 2013 at 22:09

jraede · Accepted Answer · 2013-06-20 21:52:41Z

You should use AJAX to pull in data and populate the second select element. In a nutshell, AJAX is simply a separate page request that happens behind the scenes. You can use it to load a simple HTML page or partial and display it in a DOM element, or you can use it to dynamically retrieve structured data.

The best way to do this would be using JSON (JavaScript Object Notation). In this case, you would use Javascript to make an AJAX call to a PHP page, and that PHP page would take an argument in the query string that represents the value of the first select element. With that, you would make a call to your MSSQL database to get all of the corresponding options for the second select, and then echo those out. In turn, the Javascript you use to make the AJAX request can parse the response and interpret it as a JavaScript object literal, allowing you to loop through the results and do what you want with them.

Here's an example (I'm using jQuery, since it makes AJAX really easy).

At the top of your form page:

$(document).ready(function() {
    $('#select1').change(function() {
        var select1val = $(this).val();
        $.getJSON('/path/to/response.php', 'select1=' + select1val, function(response) {
             $('#select2').empty();
             if(response) {

                for(var option in response) {
                    $('<option/>').val(option.value).html(option.label).appendTo($('#select2'));
                }
             }
        });
     });
});

And then your response.php page should look like this:

<?php
$select1 = $_GET['select1'];
// Do validation here, to make sure it's a legitimate value for select1. Never trust the
// user input directly.

// Replace this with whatever code you use to make DB queries.
$options = $mydb->query("SELECT value,label FROM select2_options WHERE select1_value=?", $select1);

echo json_encode($options);

Khawer Zeshan · Accepted Answer · 2013-06-20 21:49:05Z

1

Use Ajax if you don't want to reload the page. Read more about AJAX

$('#select1').change(function() {
    var value = $(this).val();
    var dataString = 'id='+ value;

    if(value != '') 
    {
        $.ajax({
               type: "POST",
               url: "fetchOptionsForSelect2.php",
               data: dataString,
               success: function(html) {
                     $('#select2').html(html);
               }
        });
    } 
    else 
    {
        //reset select2
        $('#select2').html("<option value=''>Select value from select1 first</option>");
    }
});

answered Jun 20, 2013 at 21:49

Khawer Zeshan

9,6568 gold badges43 silver badges63 bronze badges

Comments

cssyphus · Accepted Answer · 2013-06-20 22:11:58Z

Here is a stand-alone example that does what you want. It might look complicated at first, but AJAX via jQuery is quite straight-forward.

This example uses two files:

1) TEST.PHP - contains the javascript/AJAX, and the HTML with the <select> controls
2) PROCESS.PHP - receives data from test.php (via AJAX), runs a MySQL lookup on that data, returns HTML back to TEST.PHP

TEST.PHP

<html>
    <head>
        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>

        <script type="text/javascript">
            $(document).ready(function() {
                $("#workers").change(function(event) {
                    var w = $(this).val();
//alert('Value of w is: ' + w);
//return false;
                    $.ajax({
                        type: "POST",
                        url: "process.php",
                        data: 'worker=' + w,
                        success:function(data){
//alert(data);
                            $('#laDiv').html(data);
                        }
                    }); //END ajax
                });
            }); //END $(document).ready()

        </script>
    </head>
<body>

Worker:  
<select  id="workers">
    <option>Roy</option>
    <option>John</option>
    <option>Dave</option>
</select>

<div id="laDiv"></div>

</body>
</html>

PROCESS.PHP

<?php

$w = $_POST['worker'];

$ret = '
    Fruit Options: 
    <select id="fruitopts" name="Select2">
';

if ($w == 'Roy'){
    $ret .= '
        <option>Apples</option>
        <option>Oranges</option>
        <option>Pears</option>
    ';
}else if ($w == 'John') {
    $ret .= '
        <option>Peaches</option>
        <option>Grapes</option>
        <option>Melons</option>
    ';
}else if ($w == 'Dave') {
    $ret .= '
        <option>Nut</option>
        <option>Jelly</option>
    ';
}

$ret .= '</select>';

echo $ret;

Here's what happens:

a. TEST.PHP - User selects choice from dropdown "workers"
b. change() event fires, gets value of ("w"), and sends that to process.php
c. PROCESS.PHP receives a variable key named w in its $_POST[] array, stores in $w
d. PROCESS.PHP does a MySQL lookup on the selected worker (value of $w)
e. PROCESS.PHP constructs some HTML in a var called $ret, then ECHOs it out
f. TEST.PHP receives the HTML string inside the $.ajax success function
g. TEST.PHP calls the received data data (-1 for originality)
h. TEST.PHP injects the received HTML into the DIV with id="laDiv"

Hope that helps.

Susheel · Accepted Answer · 2013-06-20 22:02:59Z

0

Use http://www.appelsiini.net/projects/chained

<script src="jquery.chained.min.js"></script>

<select id="mark" name="mark">
    <?php
         foreach($select1_opt as $opt)
        {
            echo "<option value=$opt>$opt</option>";
        }
    ?>
    </select>
    <select id="series" name="series">
    <?php
        foreach($select2_opt as $opt)
        {
            echo "<option value=$opt>$opt</option>";
        }
    ?>
</select>

answered Jun 20, 2013 at 22:02

Susheel

1,6791 gold badge12 silver badges14 bronze badges

Collectives™ on Stack Overflow

Pulling data from SQL database depending on form input

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related