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.

Displaying Matching words by using Autocomplete while entering text Datalist Basic

download Datalist Add Options  Script
Ajax Json XML
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com







    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?
    smo1234

    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
    smo1234

    31-07-2017

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

    03-05-2018

    How can I populate the field names in the same manner, and when the a field name is chosen, its content is displayed on the page?
    smo1234

    21-08-2018

    The way we have collected city names, the same way you can collect field names to connect to datalist. You can collect field names from a given table like this.

    You can use change or input event to trigger your back end script to populate data of the field.

    Post your comments , suggestion , error , requirements etc here





    PHP video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer