Autocomplete with PHP, jQuery, MySQL and XML

Autocomplete with PHP, jQuery, MySQL and XML

88 718445
Autocomplete with PHP, jQuery, MySQL and XML
Autocomplete with PHP, jQuery, MySQL and XML

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!

SIMILAR ARTICLES

Bootstrap 3 slider

0 51710
Design Patterns in PHP

0 107365

88 COMMENTS

  1. how can i add additional sql or xml fields
    example i want to include city option and save its data in mysql. pls help :)

    • 2 techirsh
      I can suggest you to work with SQL in this case. Just because in case of cities – our result JS file can become very big (amount of cities much more than total amount of countries). So, firstly – you should have your table with cities. After – add one more field to HTML (for cities). After – prepare JS to require information about cities using your added field (at previous step). Finally – perform search (via PHP) and passing results back to JS.

  2. Dear Sir,

    I am new in web applications.
    Its my pleasure to use your auto-complete script in my recent project.

    But i would like to modify as per requirement but cant able to do so.

    For first word your script shows the auto-suggestions but I want to show suggestions for second word and further words..
    May these words are comma / space / semicolon separated

    ie: php, mysql, jquery

    Is it possible to modify this script in such a way?

    Thanks in advance

    Mr. Ravindra

    • Hello Ravindra,
      Yes, this is possible. As solution – you can modify PHP.
      You can use ‘explode’ function to split a string by string. Then – perform search by second word.
      And, perform exit in case if you haven’t second word.

  3. Thanks For your quick reply,

    As per your suggestion i have modified the php file as below,

    $inputtext = escape($_GET[‘q’]);
    $textarray=explode(‘,’,$inputtext);
    $sParam=trim($textarray[sizeof($textarray)-1]);
    if (! $sParam) exit;

    by using this code i can get the suggestion for second word but when i select the suggestion for second word,the first word get removed and only second word appear in input field.

    i tried to make changes in .js file but can’t get success. its a minified version of .js

    How we can keep the first word & comma & then insert second word in input.

    Thanks again
    Mr. Ravindra

    • Hello Ravindra again, sorry for my late response, but I have found solution:

      http://jqueryui.com/demos/autocomplete/#multiple
      check here initialization of autocomplete – its all what you need !

  4. Dear Sir,

    For auto suggestion for multiple words separated by comma. I would like to modify your multi-functional script for multiple words .

    For comma separated words, Pl see the demo Provided no following link…
    http://www.devbridge.com/projects/autocomplete/jquery/
    Limitation of this script is that it cant support array values and xml file.

  5. I spent a good bit of time implementing this solution and I love it. However, I also need to return and id field from the database as well as the country or name of the item. Can you tell me how I could return another field (like an id) in table to use when submitting form instead of the input text. I still want to display the input text selection but use another field when submitting form. Any help is greatly appreciated.

    • Hi Danny,
      As I see, if you would like to return suggestion line which consist of several details, you have to prepare this output.
      Pay attention to data.php. I am extracting here only single field value, right? So, just add several more necessary fields here (in SQL).
      And after, where we draw results (echo) – draw long line with multiple values :-)

  6. Hello!
    I’m kinda new at this and i have a doubt, hope you can help me with it, i try using the sql mode and it works out fine, but when i change the input id=”country” it won’t work anymore, what else do i have to change in the code if i use different id for the input text? Hope i explained myself
    Thanks!

    • Hello Luis,
      You should pay attention not only to our script (JS), but and also to HTML layout too. Try to keep unique ID’s of your forms, and – necessary ID’s for JS code too.

  7. Hey, good tutorial, but really for jQuery UI’s autocomplete you will find your PHP and Javascript syntax to be *far* simpler if you use JSON instead of XML. Have a look at PHP’s json_encode() function. Basically you build a PHP array of your results, and just json_encode it and echo the result.

    I think if you look at jQuery UI’s docs, that’s how they do it.

  8. Hi,
    Is it possible:
    In DB: Name, Surname and birthday. And autocomplete from all free values Name, Surname and birthday.
    Thanks

    • Hello Gromka,
      Yes, of course this is possible. You just have to perform search by all your custom fields (SQL), thats all.

  10. Thanks so much for the simple tutorial. I have it working right now with stock symbols and company names from my database. Is there a way to not force the user to have to select from the list?

    • Hello Dennis,
      What do you mean ‘not force’ ?
      If you needn’t select from the list, you can just complete the suggested word with typing

  11. When I type something in the input box and hit enter, the input box is automatically filled in with the first suggestion in the drop down box. I was wondering if its possible to type something into input box and maybe have choice to select something from the list of suggestions.

    • Hello Dennis,
      When you type something in input box, the script automatically gives you filtered results (you don’t need to click enter in this moment). You can just use arrow buttons to select something from that list.

  12. Thanks for responding. What I’m asking is, sometimes I don’t want to have to choose something from the list that is given. Maybe I type something in, and a few things are given as suggestions, but I don’t want to choose any suggestions. Right now if I type in a few characters and hit enter, the input box is automatically filled with the first selection in the list. Is it possible to have the user either enter what they typed in or make a selection from the list of suggestions?

    • Hello Dennis again,
      Yes, I understand your position right now, but, this is functionality of autocomplete plugin, it forwards active state to the first element in the list. So, if you need to put here something custom, you should fill it with some unique combination (which is out of possible suggestions), in this case you will be able to apply your custom text.
      Or, you can start typing (as example ‘feb’), then, when you have got suggestion (february), you can click ‘Esc’ button in order to close that popup, and then you can click ‘Enter’ :-)

  13. Getting a warning error for the XML and SQL fields, Warning: mysql_connect() [function.mysql-connect]: Access denied for user ‘_DATABASE_USERNAME_’@’localhost’ (using password: YES) in /opt/lampp/htdocs/test/example160/classes/CMySQL.php on line 19. I only want to use XML but this happens in the XML field as well.

    • Hello Benjamin,
      Don’t forget to update CMySQL.php with the details of your own database (db name, password)

  14. Dear Admin,
    Thanks a lot for this very useful script.
    Pls help me – I need to change the Width in script.js from 200 to 90%. Unfortunately, it does’nt work, it understands onle pixels. Is there any way to change absolute value (200) to Percents?
    Thank you in advance!

    • Hello Roland,
      Have you tried to override Width param of parent element (.ac_results) ? It works fine for me (use !important)

  15. Dear Admin, did you know that you are the best man? Thank you, it was so easy))…
    Waiting for new tuts!
    BR, Roland

  16. Hi Admin,

    Good evening,

    I am very new to PHP world, i am trying to connect to remote ORACLE database with ODBC connection. I have some changes to the original script, now the script runs without any error message, but at the same time it’s not returning any data too. ;)

    I would really appreciate it very much if you could provide some sample or perhaps converting the above script to be able to work with ODBC connection.

    Thanks with regards, Sashi.

    • Hello Sashi,
      Interesting :-) But why are you looking for how to link it with Oracle database? Some local project? Anyway, .. I think that before we link it to Oracle database (through odbc), we have to read some material how to work there.

  17. Great script. I do however have a question.

    I have a database with a list of stores(thousands). I use PHP to get the data in an indexed array where I am getting the store name, address, zip, etc.

    The user can type in the store name in full, or a partial store name, such as “The Store” or partial as “The”. I would like the query to return multiple entries if the user only types in a partial search. The user would then select using a radio button, the store they want.

    Is it correct to assume I can do this using an in_array function and if it returns true, then simply do a search for the term or partial term.

    Thanks,

    Grant

  18. Sorry, one other question. How do you set a minimum number of characters before the search starts. If someone types “a” I could get thousands of responses.

    Thanks,

    Grant

    • Hello Grand,
      It is ok, I don’t mind of lots of questions :-)
      Well, I didn’t get why you want to use ‘in_array’ PHP function here. Our SQL-based request already filters all entities by incomplete name, what is your problem? It uses LIKE ‘%keyword%’ logic.
      And, there is a one param:
      minLengthType – The minimum number of characters a user must type before a search is performed.

  19. Hi This is kalai, From Tamilnadu
    i am using your script is perfectly work, but i am adding dynmaic input field [clone input field] can’t work auto complete in clone input field
    please help me

    Thanks

    • Hi kalai,
      If you want to use autocomplete for your new (dynmaic) fields – don’t forget to initialize autocomplete for them

  20. Hello admin

    How to change the FONT SIZE of the results … They are very small

    i need your help. please! Thanks.

    • Hello Marvin,
      You can change it here :
      .ac_results li (jquery.autocomplete.css)
      Or, you can just override this CSS property in custom CSS file (main.css)

  21. Thank you very much, I use it in my work.
    Pls tell me – in case of I tried to make it via iframe. But even in that case the autocomplete works only when browser is able to javascript. Maybe do you know some way to find solution – if user’s browser has no javascript in settings.
    Thank you!

    • Hi Michael,
      I can advise you not to use iframes at all. Try to make code at your side (without iframes). And, I think that most of novaday’s browsers support javascript. You shouldn’t worry about it.

  22. I was able to get multiple XML files to work however the behavior is odd. It seems the first character is not being read. Example using the string ‘Jonathan’ … when typing ‘J’ nothing is displayed however typing ‘a’ shows my full name (Jonathan).

  24. Dear Admin, pls tell me one point.
    Because at my hoster at error_log has this text
    ” PHP Fatal error: Allowed memory size of 33554432 bytes
    > exhausted (tried to allocate 32 bytes) in
    > /home/database/mysite/www/classes/CMySQL.php on line 77″
    I asked to my hoster and received the answer that I need to change at my profile to PHP 5.3 and mysql 5.5

    I have to ask you – if there will be any problem with work of php-file, or it is OK. I believe that it is OK, but I must to ask you.
    Thank you very much!

    • Hello Michael,
      This error means only one: right now you have only 32MB ram, but, this script is required a bit more: 32.01Mb, .. try to increase amount of available memory, as example – 64mb

  25. Thank you very very much!!! Admin Thanks for sharing your code with us. You’re a great guy, god bless you

  26. I’m trying to add a new case to the switch in order to manage a second text field in the form via sql query, but I always get a “Database query error” on the second field. I can’t find a solution…

    • Hello Alex,
      What error exactly did you get? .. You can modify CMySQL.php class to see your actual error. Please pay attention to line 115 of this file, and, instead $text – put $sSqlQuery. In this case you will see your error.

  27. hello admin i want to display category or name or service wise so i write the sql query ————-
    $sRequest = “select Id,City,Category,Occupation,Path,Name from visiting_cards WHERE Category like ‘%$searchterm%’ or Name like ‘%$searchterm%’ or Company like ‘%$searchterm%’ or Occupation like ‘%$searchterm%'”;
    NOW I WANT TO DISPLAY VALUES according to search term…..how to display it….plz help me

    • Hello vijay,
      As I see – you want to add sql case. In this case, you can use ready code, look at data.php, it already has the necessary code. Just use your SQL request here, just change $searchterm to $sParam.

  28. I’m trying to show two different autocomplete fields on the same form, both based on the sql method. My code in script.js is something like:


    $('#country').autocomplete('data.php?mode=sql_country', {
    width: 200,
    max: 5
    });

    $('#city').autocomplete('data.php?mode=sql_city', {
    width: 200,
    max: 5
    });

    In data.php the switch statement is modified in order to recognize the two conditions, “sql_country” and “sql_city”, and to issue the appropriate query.

    The problem is that I always get a “Database query error” in the second field, while the first one works perfectly.

    How can I fix this issue?

    • Hello Alex again,
      Actually, it doesn’t matter how you pass params, you can use it like that:
      data.php?mode=sql&param=country
      data.php?mode=sql&param=city
      and – just use that $_GET[‘param’] at your server side as you like. Don’t forget to ‘escape’ this variable to make it safe.

  29. Hello admin, I want to use it in my work. But in using the tables of MySQL with Cyrillic, autocomplete does not work, although I try to use UTF-8 everywhere. Tell me how to set up autocomplete with Cyrillic.

    • Hi Evgeniy,
      It works well with russian characters, but, it is possible that you forgot to save your files in UTF8 (without BOM) encoding. You can do it easily in Notepad++

1 2

Leave a Reply