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);
}
(Note: you can get the handy SQL code to create a country list here: Country list formatted for MySQL import)