Monday, May 20, 2013

Auto Suggestion with PHP, jQuery, MySQL and XML

12:58 AM  Roby@ahm.united  No comments

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.

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
006body {
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
018footer {
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
030footer 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
040footer 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
052footer .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
084form p {
085    margin-bottom:5px;
086    text-align:right;
087}
088
089form 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
108form  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:
01CREATE 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
07INSERT 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
03if (version_compare(phpversion(), "5.3.0", ">=")  == 1)
04  error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);
05else
06  error_reporting(E_ALL & ~E_NOTICE);
07
08require_once('classes/CMySQL.php');
09
10$sParam = $GLOBALS['MySQL']->escape($_GET['q']); // escaping external data
11if (! $sParam) exit;
12
13switch ($_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

view sourceprint?
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>

Posted in: ,

0 comments:

Post a Comment

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More