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 | Dev School</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://dev-school.net/autocomplete-with-php-jquery-mysql-and-xml/" class="stuts">Back to original tutorial on <span>Dev School</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!