Home

Loading a Drop Down List Using jQuery

Submitted by newaction on Tue, 2011-12-20 17:21

To load a drop down list (or a simple list) from a database, there
are different ways. One is to embed PHP code in the HTML code, that
loops through the rows in the database and adds <option>
</option> tags.

<select name='mylist' id='mylist' size='1'>
<?php
while($rec = mysql_fetch_assoc($result))
{
    echo "<option>".$rec['name']."</option>";
}
?>
</select>

A better alternative is to dynamically load the list using Ajax.

There are several advantages:

  • In
    the earlier method, the generated HTML page can become too big making
    it too long to load the page. When we load the list dynamically, the
    page loads first and the list loads later. The user gets response
    faster.
  • If there are more than one lists with the same options
    (like, 'country' or 'state' lists), The same options are duplicated in
    all the lists, making a big HTML page that takes too long to load. With a
    dynamically loaded list, the same options could be loaded in all the
    lists at the same time.

And with jQuery, it is quite easy to load a list through Ajax. Here is how.

The jQuery Code

We can use the getJSON() function to load the list data. Here is the code:

function loadlist(selobj,url,nameattr)
{
    $(selobj).empty();
    $.getJSON(url,{},function(data)
    {
        $.each(data, function(i,obj)
        {
            $(selobj).append(
                 $('<option></option>')
                        .val(obj[nameattr])
                        .html(obj[nameattr]));
        });
    });
}

The function can be called like this:

loadlist($('select#country1').get(0),/*the 'select' object*/
 'get-list.php?getlist=country',/*the url of the server-side script*/
 'country'/*The name of the field in the returned list*/
 );

The
first parameter to the loadlist() function is the 'select' object in
the HTML page. Notice how the ID of the select list is used in the
sample code to get the select object. Second parameter is the URL of the
server side script. You can pass extra parameters to the server side
script. It would be a good idea to have a single script to fetch all the
lists- just change the parameters to fetch a different list.

The server-side script must return a valid JSON array of objects.
A sample valid JSON return string would be like this:

[{"ccode":"AF","country":"Afghanistan"},
{"ccode":"AL","country":"Albania"},
{"ccode":"DZ","country":"Algeria"},
{"ccode":"AS","country":"American Samoa"},
{"ccode":"AD","country":"Andorra"},
{"ccode":"AO","country":"Angola"},
{"ccode":"AI","country":"Anguilla"},
{"ccode":"AQ","country":"Antarctica"},
{"ccode":"AG","country":"Antigua and Barbuda"},
{"ccode":"AR","country":"Argentina"},
{"ccode":"AM","country":"Armenia"},
{"ccode":"AW","country":"Aruba"},
{"ccode":"AU","country":"Australia"},
{"ccode":"AT","country":"Austria"}]

Notice
the 'country' field in the JSON object. The third parameter of
loadlist() function is the name of the field to be used as the list
item name in the list.(in this example the 'country' field)

Here is a sample server-side 'list-fetcher' code in PHP:

The PHP code

On the server side, all that we have to do is to fetch the list from the Database, format it in JSON and pass it back.
Here is the code:

if(!empty($_GET['getlist']))
{
    $list = $_GET['getlist'];
    $qry='';
    switch($list)
    {
        case 'country':
        {
            $qry = "select country from countries";
            break;
        }
    }
    /*
    Note: Why not send  the table name itself as the 'getlist'
    param (avoiding the switch above)?
    Because it is dangerous! that will enable anyone browse your database!
    */
    if(empty($qry)){ echo "invalid params! "; exit; }
    $dbconn = mysql_connect('localhost','pmj','p')
            or die("DB login failed!");
    mysql_select_db('testdb', $dbconn)
            or die("Database does not exist! ".mysql_error($dbconn));
    $result = mysql_query($qry,$dbconn)
            or die("Query $qry failed! ".mysql_error($dbconn));
    $rows = array();
    while($rec = mysql_fetch_assoc($result))
    {
        $rows[] = $rec;
    }
    mysql_free_result($result);
    mysql_close($dbconn);
    echo json_encode($rows);

}

Ref: http://www.html-form-guide.com/jquery/drop-down-list-jquery.html