Autocomplete with PHP, jQuery, MySQL and XML. Today I have new article for PHP. I will tell you about implementation autocomplete for your sites. Data can be located in different sources – directly in the JS code, in the database, and even in the XML file.
Live Demo
[sociallocker]
download in package
[/sociallocker]
Now – download the source files and lets start coding !
Step 1. HTML
Here are HTML layout for our autocomplete example page:
index.html
<!DOCTYPE html>
<html lang="en" >
<head>
<meta charset="utf-8" />
<title>Autocomplete with PHP, jQuery, MySQL and XML | Script Tutorials</title>
<link href="css/jquery.autocomplete.css" rel="stylesheet" type="text/css" />
<link href="css/main.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="js/jquery-1.5.2.min.js"></script>
<script type="text/javascript" src="js/jquery.autocomplete.pack.js"></script>
<script type="text/javascript" src="js/script.js"></script>
</head>
<body>
<div class="container">
<form action="#">
<p><label>Your month:</label> <input id="month" type="text" autocomplete="off"></p>
<p><label>Your year:</label> <input id="year" type="text" autocomplete="off"></p>
<p><label>Your country:</label> <input id="country" type="text" autocomplete="off"></p>
</form>
</div>
<footer>
<h2>Autocomplete with PHP, jQuery, MySQL and XML</h2>
<a href="https://www.script-tutorials.com/autocomplete-with-php-jquery-mysql-and-xml/" class="stuts">Back to original tutorial on <span>Script Tutorials</span></a>
</footer>
</body>
</html>
Step 2. CSS
Now, lets define all used styles:
css/main.css
*{
margin:0;
padding:0;
}
body {
background-repeat:no-repeat;
background-color:#bababa;
background-image: -webkit-radial-gradient(600px 200px, circle, #eee, #bababa 40%);
background-image: -moz-radial-gradient(600px 200px, circle, #eee, #bababa 40%);
background-image: -o-radial-gradient(600px 200px, circle, #eee, #bababa 40%);
background-image: radial-gradient(600px 200px, circle, #eee, #bababa 40%);
color:#fff;
font:14px/1.3 Arial,sans-serif;
min-height:600px;
}
footer {
background-color:#212121;
bottom:0;
box-shadow: 0 -1px 2px #111111;
display:block;
height:70px;
left:0;
position:fixed;
width:100%;
z-index:100;
}
footer h2{
font-size:22px;
font-weight:normal;
left:50%;
margin-left:-400px;
padding:22px 0;
position:absolute;
width:540px;
}
footer a.stuts,a.stuts:visited{
border:none;
text-decoration:none;
color:#fcfcfc;
font-size:14px;
left:50%;
line-height:31px;
margin:23px 0 0 110px;
position:absolute;
top:0;
}
footer .stuts span {
font-size:22px;
font-weight:bold;
margin-left:5px;
}
.container {
border:3px #111 solid;
color:#000;
margin:20px auto;
padding:20px;
position:relative;
text-align:center;
width:300px;
border-radius:15px;
-moz-border-radius:15px;
-webkit-border-radius:15px;
}
.ac_results {
border: solid 1px #E5E5E5;
color:#000;
border-radius:0 0 5px 5px;
-moz-border-radius:0 0 5px 5px;
-webkit-border-radius:0 0 5px 5px;
}
.ac_over {
background-color:#444;
}
form p {
margin-bottom:5px;
text-align:right;
}
form input {
background-color: #FFFFFF;
background: -moz-linear-gradient(top, #FFFFFF, #EEEEEE 1px, #FFFFFF 25px);
background: -webkit-gradient(linear, left top, left 25, from(#FFFFFF), color-stop(4%, #EEEEEE), to(#FFFFFF));
border: solid 1px #E5E5E5;
font-size:14px;
outline: 0;
padding: 9px;
width: 180px;
border-radius:5px;
-moz-border-radius:5px;
-webkit-border-radius:5px;
box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;
-moz-box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;
-webkit-box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;
}
form input:hover, form input:focus {
border-color: #C9C9C9;
box-shadow: rgba(0,0,0, 0.5) 0px 0px 8px;
-moz-box-shadow: rgba(0,0,0, 0.5) 0px 0px 8px;
-webkit-box-shadow: rgba(0,0,0, 0.5) 0px 0px 8px;
}
In our package you can find few more files:
css/jquery.autocomplete.css + css/indicator.gif
Both files I got from autocomplete jquery package (this is default files – don`t need to re-publish it in our article)
Step 3. Javascript
Its time to prepare JS:
js/script.js
$(function(){
$('#month').autocomplete(['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'], {
width: 200,
max: 3
});
$('#year').autocomplete('data.php?mode=xml', {
width: 200,
max: 5
});
$('#country').autocomplete('data.php?mode=sql', {
width: 200,
max: 5
});
});
As you can see – very easy syntax of using Autocomplete. In first case I hardcoded possible values directly in JS code. Second and third cases – through PHP file (using different way of obtaining data – XML and SQL). In package you can find two another JS files:
js/jquery-1.5.2.min.js + js/jquery.autocomplete.pack.js
This is jQuery library itself plus Autocomplete plugin
Step 4. SQL
Now, lets prepare our database – lets add 1 new table:
CREATE TABLE `s85_countries` (
`country_code` varchar(2) NOT NULL,
`country_name` varchar(255) NOT NULL,
PRIMARY KEY (`country_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `s85_countries` (`country_code`, `country_name`) VALUES
('AR', 'Argentina'),
('AU', 'Australia'),
('BR', 'Brazil'),
('CA', 'Canada'),
('CN', 'China'),
('IN', 'India'),
('KZ', 'Kazakhstan'),
('RU', 'Russia'),
('SD', 'Sudan'),
('US', 'United States');
This small table contain several records – list of countries. I took that SQL code from one of our old tutorials.
Step 5. PHP
This step most important – now you will see how we returning data for Autocomplete:
data.php
<?php
if (version_compare(phpversion(), "5.3.0", ">=") == 1)
error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
else
error_reporting(E_ALL & ~E_NOTICE);
require_once('classes/CMySQL.php');
$sParam = $GLOBALS['MySQL']->escape($_GET['q']); // escaping external data
if (! $sParam) exit;
switch ($_GET['mode']) {
case 'xml': // using XML file as source of data
$aValues = $aIndexes = array();
$sFileData = file_get_contents('data.xml'); // reading file content
$oXmlParser = xml_parser_create('UTF-8');
xml_parse_into_struct($oXmlParser, $sFileData, $aValues, $aIndexes);
xml_parser_free( $oXmlParser );
$aTagIndexes = $aIndexes['ITEM'];
if (count($aTagIndexes) <= 0) exit;
foreach($aTagIndexes as $iTagIndex) {
$sValue = $aValues[$iTagIndex]['value'];
if (strpos($sValue, $sParam) !== false) {
echo $sValue . "\n";
}
}
break;
case 'sql': // using database as source of data
$sRequest = "SELECT `country_name` FROM `s85_countries` WHERE `country_name` LIKE '%{$sParam}%' ORDER BY `country_code`";
$aItemInfo = $GLOBALS['MySQL']->getAll($sRequest);
foreach ($aItemInfo as $aValues) {
echo $aValues['country_name'] . "\n";
}
break;
}
We filter the resulting data by incoming parameter $_GET[‘q’] from the active text field (where we started typing something). The result – the script gives all matching records. Another one file which we using (as always):
classes/CMySQL.php
This is our usual class file to work with database (pretty comfortable). In its constructor you will able to set your own database configuration:
$this->sDbName = '_DATABASE_NAME_';
$this->sDbUser = '_DATABASE_USERNAME_';
$this->sDbPass = '_DATABASE_USERPASS_';
Step 6. XML
Here are content of our XML data file:
data.xml
<items>
<item>1991</item>
<item>1990</item>
<item>1991</item>
<item>1992</item>
<item>1993</item>
<item>1994</item>
<item>1995</item>
<item>1996</item>
<item>1997</item>
<item>1998</item>
<item>1999</item>
<item>2000</item>
<item>2001</item>
<item>2002</item>
<item>2003</item>
<item>2004</item>
<item>2005</item>
<item>2006</item>
<item>2007</item>
<item>2008</item>
<item>2009</item>
<item>2010</item>
<item>2011</item>
<item>2012</item>
<item>2013</item>
<item>2014</item>
<item>2015</item>
</items>
Live Demo
Conclusion
As a result, we see the pattern is obvious – the fastest way – when all possible values are hardcoded in JS. In case of XML and SQL – XML is clearly faster. Just because we even don`t need touch our slow database. I hope that you got interesting lesson for today. Good luck in your work!
hi! incredibly easy-to-understand tutorial! congratulations!
I have a question for you guys, is it possible to trigger an event when an option from the list is selected? if so.. which is the event I should use? I’ve tried onselect=”dosomething()” but did not work
thanks
Hernan
Hi hernan,
It is easy to handle Onchange event for ‘select’ elements:
$(‘.target’).change(function() {
// do something
});
Hello , I’m new at this and I have a question . I’ve put this autocomplete( nice job by the way) on a site and works great, but I have a problem and I don’t know how to resolve it. When I got the reusults list , when I select one result to redirect to a specific url . The link exist, but when I click it , all it does is to put the link up in the search box .
Thank you
Hi inlume,
Actually, it works correctly. Why did you suppose that it should do something else besides of select a result? This autocomplete is made to suggest something when we type in usual text input field. If you want to do something else – you should add own event handler to handle with .. changing of this text element (as example). So, you can try to add onchange event handler for this text field in order to handle with it.
Hi, i want to do something like this:
when i click to input box then all results will appear
maybe like this, but it not working.
$(‘#year’).autocomplete(‘data.php?mode=xml’, {
minLength: 0, // mean click to see
width: 200,
max: 5
});
How can i do that
thank you,
Mr. K
Hello linked,
Your question is not clean, what do you mean: ‘when i click to input box’ ?
If you want to handle onclick event, you should add something like that:
$(‘#your_element’).click(function () {
// your onclick custom code
});
You can evoke autocomplete if you need
I have 2 inputs:
I want to send the data of the 2 inputs to a .php file while an input is being typed in or during autocomplete like this:
$(‘#ra’).autocomplete(‘data.php?mode=ra&rb=’+$(‘#rb’).val(), {
width: 200,
max: 5
});
$(‘#rb’).autocomplete(‘data.php?mode=rb&ra=’+$(‘#ra’).val(), {
width: 200,
max: 5
});
But it’s not working, how can I make this work? Thanks.
Hello again,
I think, that your code should work. Just make sure that you use ‘ symbols rather than ‘ or ’
Just check your syntax, and also check how it works in firebug (maybe you have one little JS error somewhere in your code)
Hello Admin,
very nice script , Frankly you’re a professional.
i have question, pls :
i have form including 3 fileds ( item , addition , price ) , so i had apply you’re script on ‘item’ ,
as following :
-data file:
case ‘sql’: // using database as source of data
$sRequest = “SELECT `item` , `price` , `addition` FROM `item` WHERE `item` LIKE ‘%{$sParam}%’ ORDER BY `item` desc”;
$aItemInfo = $GLOBALS[‘MySQL’]->getAll($sRequest);
foreach ($aItemInfo as $aValues) {
echo $aValues[‘item’] . “\n”;
-php file:
What I want is:
When i choose value for ‘item’ automatically i want to get addition and price for each item and put it in 2 fields .
I am waiting you’re reply.
Best Regards
Bassel
Hello Bassel,
There is one appropriate way for you:
1. define three (or two) new public variables (in JS)
2. bind a proper event (maybe autocompleteopen)
3. assign extra values with obtained data
or, another one good idea is to use callback source data. Look at this small example:
$(‘#item’).autocomplete({
source: function(request, response) {
$.ajax({
url: ‘data.php’,
dataType: “json”,
data: {
term : request.term,
customData : $(‘#something’).val()
},
success: function(data) {
response(data);
}
});
},
minLength: 2}, {
});
Thanks for the script
I would like to know that I can do to open the drop-down Enter when you have entered 3 letters
thank you very much
Hello Lucas,
Pay attention to ‘minLength’ param which I used in my previous answer
Sir i got an error when im inputting values in the country text field. “Database query error” it tells sir.
Hi JM, please pay attention that you have to put your own database details in ‘classes/CMySQL.php’ file (in the beginning of the file)
Dear admin:
I’m using XML file as source of data. when I do the search lists all the parameter matches at the beginning or end of the word.
My question: How to search only the beginning of the word, something like this: ‘country_name’ LIKE ‘{$sParam}%’
Please help me. Thanks Admin.
Hi Marvin, the one of methods is to add this param:
source: function( request, response ) {
var matches = $.map( data, function(tag) {
if ( tag.label.toUpperCase().indexOf(request.term.toUpperCase()) == 0 ) {
return tag;
}
});
response(matches);
}
Great Codes! :) Will use it for autocomplete location. But I do have one question, How could I detect the state and country if they are separated by a comma?
$(‘#location’).autocomplete([
‘Abbeville, Alabama, United States of America’,
‘Abernant, Alabama, United States of America’,
‘Adamsville, Alabama, United States of America’,
‘Addison, Alabama, United States of America’], {
width: 200,
max: 3
});
I can search cities but I can’t search the province/country :(
Hi Jiah,
Basically, you need to use 1-level structures, you don’t need to enumerate everything separating by a comma on the same level.
Dear Andrew,
thank you for this great job. Unfortunately I am not able to find the right place to input the “minLength”.
Thank you.
Uwe
Hello Uwe,
The ‘minLength’ param was fixed in the latest version of this plugin (you can try to use version 1.8.16)
Hello Sir,
I’m going to populate the province textbox base on what is the selected country on change like this code below:
$(‘#country’).change(function(){
var country = $(this).val();
$(‘#province’).autocomplete(“autocomplete/data.php?mode=sql&country=”+ country +””, {
width: 220,
max: 5,
});
});
My problem is when i select country multiple times the result is two autocomplete on the province textbox, the first country that i select and the second one. How can i remove the previous result? Please help! Thank you!
Hi MR,
You can try to do something like that:
$(‘#AutocompleteElementID’).data().autocomplete.term = null;
Hi Admin. i’d like to ask you such a question. Input element always wait for the value. you click in dropping down list. But i want to delete this thing! I want to have elements with html and css, so i don’t need to automatic filling value of input by my click on elemnt in dropping down list. I think many people like my question and are waiting for you answer. Thank YOU!
Hi Sergio,
As I can see, in your case you even don’t need to have the autocomplete element. You may just prepare a single HTML/CSS list of necessary elements, and bind the ‘onclick’ event to your list.
I was seraching a long time when i saw your solution , i want to say thank you , it really help me
Hello,
How do I trigger an event when the selection is clicked on. I can’t use the .change method.
Thank you,
Andy
Hi Andy,
When we select a certain value from the popup list of possible values, we can use this event:
select: function( event, ui ) {
alert(ui.item.value);
}
Hey, how can I make the results clickable?
I search around the web but I dont found a complete sript.
Please help :)
Hi Sam,
You will need to try to use another plugins to achieve it. Because current realization allows only basic text results.
Hello Andrew, thank you for your tutorial, I want to see if you can help me, I need autocomplete for my reservation form, I can get the data of the autocomplete, but I need to use a value, example:
Label = Mexico, Value= 1
I need to show “Mexico” in the autocomplete, but when the user click the button submit of the form I need to pass the “value” to another php file.
Can you help me how to do that please??
Thank you
Hello Jonathan,
As I know, this plugin works with ordinary strings, you can not return two values in the same time (title and value). You may try to add the value to the title (and parse it further), like: Mexico_1
Hi Admin, I loved this one. Thanks. But I have a little problem.
when I have a very long suggestion list (suppose in customer field there are more than 1000 names with “a”), is there any way to modify your code to get next,previous something like that with showing 5 suggestions at a time ? thanks.
Hi Shiplu, in case of huge lists, I recommend that you limit the amount of records directly in SQL (e.g. LIMIT 5). And after, if you modify the plugin, you will be able to insert the pagination into it.
Perfectly.
But where is a button “send” in the form ?
And it would be good to add a file to process the form. How to pass the value entered in a php variable?
Hi Alex,
The submit button is not a part of our tutorial, in any case, this is very easy to add the submit button into the form