Adding options to datalist from Mysql Table records.

Datalist is added in HTML 5. You can learn basic of datalist in our HTML Section. We will develop a script to populate option ( or value ) of a datalist by taking data from a MySQL database table.

Sample table

Table for Datalist options
We have kept one sample table CITY with city name and country . For this tutorial we will use only city name column to populate our datalist. There are some sample records kept in the table to use in this tutorial. You can download the sql dump of the table along with the script at the end of this page.

Loading data

First we will connect to mysql database by using our config.php file.

require "config.php"; // Database Connection

You can read more on how to use config.php file here.
We will develop the datalist first by connecting it to one text box. Note that we will not add the closing tag of datalist immediately as we will be adding the city list as option by taking the names from table.

echo "<input id="city" list="city1" >
<datalist id="city1" >";

Once database connection is established ,we can go for collecting the data. Here is the query

$sql="select city from city "; // Query to collect records

We will loop through the array and collect all the city list. While collecting we will format the out put and add them as option to the data list.

foreach ($dbo->query($sql) as $row) {
echo "<option value="$row[city]"/>"; // Format for adding options
}

Now we will add the closing tag of the datalist.

echo "</datalist>";

This will populate the datalist with data from a database table.
Here is the demo

Using Ajax

In Second part of this tutorial we will use Ajax and give user the option to populate the datalist after clicking a button. By using Ajax we can add options without refreshing the page. Now we will collect data from another file. In side this file we will keep the PHP code to connect to database and collect the city list from table. Then it will format the output and return it to our main page to display the datalist. Let us start with displaying the datalist ( without options) and the button.

<input id="city" list="city1" >
<datalist id="city1" >
</datalist>

<input type=button onClick=AjaxFunction() value='Add Options'>

Note that with the click of the button we are triggering the function AjaxFunction(). Inside this function we are sending request to the file city-list-ajaxck.php .

function AjaxFunction()
{
var httpxml;
try
{
// Firefox, Opera 8.0+, Safari
httpxml=new XMLHttpRequest();
}
catch (e)
{
// Internet Explorer
try
{
httpxml=new ActiveXObject("Msxml2.XMLHTTP");
}
catch (e)
{
try
{
httpxml=new ActiveXObject("Microsoft.XMLHTTP");
}
catch (e)
{
alert("Your browser does not support AJAX!");
return false;
}
}
}
function stateck()
{
if(httpxml.readyState==4)
{
document.getElementById("city1").innerHTML=httpxml.responseText;
}
}
var url="city-list-ajaxck.php";
url=url+"?sid="+Math.random();
//alert(url);
httpxml.onreadystatechange=stateck;
httpxml.open("GET",url,true);
httpxml.send(null);
}

city-list-ajaxck.php file connects to database and returns the name of the list as formatted output for the city datalist.

require "config.php"; // Database Connection

//// Collect options from table ///
$sql="select city from city "; // Query to collect records
foreach ($dbo->query($sql) as $row) {
echo "<option value="$row[city]"/>"; // Format for adding options
}

This output once received by main file they are added as option to the datalist.

document.getElementById("city1").innerHTML=httpxml.responseText;

The Output of this script is here. Click the button to see how the datalist is populated.



Note that this button will add the exiting options to the datalist, It will not add any new option.

download Datalist Add Options  Script

Subscribe to our mailing list

* indicates required
Subscribe to plus2net
Visitors Rating
Your Rating


Google+

Neha

23-04-2015

Your example is good but i am getting the database connectivity error as I have already changed the credentials of my localhost...
juan

03-04-2017

it works both of them for me, but i can't understand the really function to the ajax one, i can't understand the use for that button "add options", may you explain me please?
subhendu

04-04-2017

The button takes the user click and then only populate the data list with options. While loading of the page data list is not available with the options. User can add the options by clicking the button. Such type of triggers are required when you want to filter the data based on users previous selection or choice. If user has selected Asia as region then you can trigger the datalist to show only cities of Asia
Manjunatha

28-07-2017

Hi,Thank you for your great work
i got your coding ,but i want to select multiple data by using (comma) ,
For Example
if user wants to select many places by using comma (,) he can select multiple places ,please help me with it
subhendu

31-07-2017

It is better to use JQuery UI , Autocomplete . There is a Demo with multiple word search for autocomplete.

Post Comment This is for short comments only. Use the forum for more discussions.




Subscribe to our mailing list

* indicates required
Subscribe to plus2net
HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2018 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer