Home » Tutorials

Autocomplete with PHP, jQuery, MySQL and XML

Tutorials
Author Reading 13 min Views 55 Published by Modified by

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.

Contents
  1. Step 1. HTML
  2. index.html
  3. Step 2. CSS
  4. css/main.css
  5. css/jquery.autocomplete.css + css/indicator.gif
  6. Step 3. Javascript
  7. js/script.js
  8. js/jquery-1.5.2.min.js + js/jquery.autocomplete.pack.js
  9. Step 4. SQL
  10. Step 5. PHP
  11. data.php
  12. classes/CMySQL.php
  13. Step 6. XML
  14. data.xml
  15. Conclusion
Live Demo
download in package

Now – download the source files and lets start coding !

Step 1. HTML

Here are HTML layout for our autocomplete example page:

index.html

01 <!DOCTYPE html>
02 <html lang="en" >
03     <head>
04         <meta charset="utf-8" />
05         <title>Autocomplete with PHP, jQuery, MySQL and XML | Script Tutorials</title>
06
07         <link href="css/jquery.autocomplete.css" rel="stylesheet" type="text/css" />
08         <link href="css/main.css" rel="stylesheet" type="text/css" />
09         <script type="text/javascript" src="js/jquery-1.5.2.min.js"></script>
10         <script type="text/javascript" src="js/jquery.autocomplete.pack.js"></script>
11         <script type="text/javascript" src="js/script.js"></script>
12     </head>
13     <body>
14         <div class="container">
15             <form action="#">
16                 <p><label>Your month:</label> <input id="month" type="text" autocomplete="off"></p>
17                 <p><label>Your year:</label> <input id="year" type="text" autocomplete="off"></p>
18                 <p><label>Your country:</label> <input id="country" type="text" autocomplete="off"></p>
19             </form>
20         </div>
21         <footer>
22             <h2>Autocomplete with PHP, jQuery, MySQL and XML</h2>
23             <a href="http://www.script-tutorials.com/autocomplete-with-php-jquery-mysql-and-xml/" class="stuts">Back to original tutorial on <span>Script Tutorials</span></a>
24         </footer>
25     </body>
26 </html>

Step 2. CSS

Now, lets define all used styles:

css/main.css

001 *{
002     margin:0;
003     padding:0;
004 }
005
006 body {
007     background-repeat:no-repeat;
008     background-color:#bababa;
009     background-image: -webkit-radial-gradient(600px 200px, circle, #eee, #bababa 40%);
010     background-image: -moz-radial-gradient(600px 200px, circle, #eee, #bababa 40%);
011     background-image: -o-radial-gradient(600px 200px, circle, #eee, #bababa 40%);
012     background-image: radial-gradient(600px 200px, circle, #eee, #bababa 40%);
013     color:#fff;
014     font:14px/1.3 Arial,sans-serif;
015     min-height:600px;
016 }
017
018 footer {
019     background-color:#212121;
020     bottom:0;
021     box-shadow: 0 -1px 2px #111111;
022     display:block;
023     height:70px;
024     left:0;
025     position:fixed;
026     width:100%;
027     z-index:100;
028 }
029
030 footer h2{
031     font-size:22px;
032     font-weight:normal;
033     left:50%;
034     margin-left:-400px;
035     padding:22px 0;
036     position:absolute;
037     width:540px;
038 }
039
040 footer a.stuts,a.stuts:visited{
041     border:none;
042     text-decoration:none;
043     color:#fcfcfc;
044     font-size:14px;
045     left:50%;
046     line-height:31px;
047     margin:23px 0 0 110px;
048     position:absolute;
049     top:0;
050 }
051
052 footer .stuts span {
053     font-size:22px;
054     font-weight:bold;
055     margin-left:5px;
056 }
057
058 .container {
059     border:3px #111 solid;
060     color:#000;
061     margin:20px auto;
062     padding:20px;
063     position:relative;
064     text-align:center;
065     width:300px;
066
067     border-radius:15px;
068     -moz-border-radius:15px;
069     -webkit-border-radius:15px;
070 }
071
072 .ac_results  {
073     border: solid 1px #E5E5E5;
074     color:#000;
075
076     border-radius:0 0 5px 5px;
077     -moz-border-radius:0 0 5px 5px;
078     -webkit-border-radius:0 0 5px 5px;
079 }
080 .ac_over {
081     background-color:#444;
082 }
083
084 form p {
085     margin-bottom:5px;
086     text-align:right;
087 }
088
089 form input {
090     background-color: #FFFFFF;
091     background: -moz-linear-gradient(top, #FFFFFF, #EEEEEE 1px, #FFFFFF 25px);
092     background: -webkit-gradient(linear, left top, left 25, from(#FFFFFF), color-stop(4%, #EEEEEE), to(#FFFFFF));
093     border: solid 1px #E5E5E5;
094     font-size:14px;
095     outline: 0;
096     padding: 9px;
097     width: 180px;
098
099     border-radius:5px;
100     -moz-border-radius:5px;
101     -webkit-border-radius:5px;
102
103     box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;
104     -moz-box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;
105     -webkit-box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;
106 }
107
108 form  input:hover, form  input:focus {
109     border-color: #C9C9C9;
110
111     box-shadow: rgba(0,0,0, 0.5) 0px 0px 8px;
112     -moz-box-shadow: rgba(0,0,0, 0.5) 0px 0px 8px;
113     -webkit-box-shadow: rgba(0,0,0, 0.5) 0px 0px 8px;
114 }

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

01 $(function(){
02
03     $('#month').autocomplete(['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'], {
04         width: 200,
05         max: 3
06     });
07
08     $('#year').autocomplete('data.php?mode=xml', {
09         width: 200,
10         max: 5
11     });
12
13     $('#country').autocomplete('data.php?mode=sql', {
14         width: 200,
15         max: 5
16     });
17
18 });

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:

01 CREATE TABLE `s85_countries` (
02   `country_code` varchar(2) NOT NULL,
03   `country_name` varchar(255) NOT NULL,
04   PRIMARY KEY  (`country_code`)
05 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
06
07 INSERT INTO `s85_countries` (`country_code`, `country_name`) VALUES
08 ('AR', 'Argentina'),
09 ('AU', 'Australia'),
10 ('BR', 'Brazil'),
11 ('CA', 'Canada'),
12 ('CN', 'China'),
13 ('IN', 'India'),
14 ('KZ', 'Kazakhstan'),
15 ('RU', 'Russia'),
16 ('SD', 'Sudan'),
17 ('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

01 <?php
02
03 if (version_compare(phpversion(), "5.3.0", ">=")  == 1)
04   error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
05 else
06   error_reporting(E_ALL & ~E_NOTICE);
07
08 require_once('classes/CMySQL.php');
09
10 $sParam = $GLOBALS['MySQL']->escape($_GET['q']); // escaping external data
11 if (! $sParam) exit;
12
13 switch ($_GET['mode']) {
14     case 'xml': // using XML file as source of data
15         $aValues = $aIndexes = array();
16         $sFileData = file_get_contents('data.xml'); // reading file content
17         $oXmlParser = xml_parser_create('UTF-8');
18         xml_parse_into_struct($oXmlParser, $sFileData, $aValues, $aIndexes);
19         xml_parser_free( $oXmlParser );
20
21         $aTagIndexes = $aIndexes['ITEM'];
22         if (count($aTagIndexes) <= 0) exit;
23         foreach($aTagIndexes as $iTagIndex) {
24             $sValue = $aValues[$iTagIndex]['value'];
25             if (strpos($sValue, $sParam) !== false) {
26                 echo $sValue . "\n";
27             }
28         }
29         break;
30     case 'sql': // using database as source of data
31         $sRequest = "SELECT `country_name` FROM `s85_countries` WHERE `country_name` LIKE '%{$sParam}%' ORDER BY `country_code`";
32         $aItemInfo = $GLOBALS['MySQL']->getAll($sRequest);
33         foreach ($aItemInfo as $aValues) {
34             echo $aValues['country_name'] . "\n";
35         }
36         break;
37 }

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:

1 $this->sDbName = '_DATABASE_NAME_';
2 $this->sDbUser = '_DATABASE_USERNAME_';
3 $this->sDbPass = '_DATABASE_USERPASS_';

Step 6. XML

Here are content of our XML data file:

data.xml

01 <!--?xml version="1.0"?-->
02 <items>
03     <item>1991</item>
04     <item>1990</item>
05     <item>1991</item>
06     <item>1992</item>
07     <item>1993</item>
08     <item>1994</item>
09     <item>1995</item>
10     <item>1996</item>
11     <item>1997</item>
12     <item>1998</item>
13     <item>1999</item>
14     <item>2000</item>
15     <item>2001</item>
16     <item>2002</item>
17     <item>2003</item>
18     <item>2004</item>
19     <item>2005</item>
20     <item>2006</item>
21     <item>2007</item>
22     <item>2008</item>
23     <item>2009</item>
24     <item>2010</item>
25     <item>2011</item>
26     <item>2012</item>
27     <item>2013</item>
28     <item>2014</item>
29     <item>2015</item>
30 </items>
Live Demo
download in archive

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!

Rate article
© 2025 Script Tutorials