Pages

Auto complete text box with PHP, jQuery and MySql

Thursday, May 17, 2012
In this tutorial I want to show you how easy to do a auto complete text box with PHP, jQuery and MySql.

Firstly download the autocomplete jQuery plugin and extract it to your hard drive.
And then create a folder in your localhost. Copy jquery.autocomplete.css and jquery.autocomplete.js file to your project folder. And you will also need jquery.js. Now we can start our project.

Creating database table


We need to create a table in our database before writing our script. I also add some data for this table. Import following SQL statement via phpMyAdmin or any other MySQL tool.

CREATE TABLE `tag` (
  `id` int(20) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

INSERT INTO `tag` (`id`, `name`) VALUES
(1, 'php'),
(2, 'php frameword'),
(3, 'php tutorial'),
(4, 'jquery'),
(5, 'ajax'),
(6, 'mysql'),
(7, 'wordpress'),
(8, 'wordpress theme'),
(9, 'xml');

index.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Auto Complete Input box</title>
<link rel="stylesheet" type="text/css" href="jquery.autocomplete.css" />
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="jquery.autocomplete.js"></script>
<script>
 $(document).ready(function(){
  $("#tag").autocomplete("autocomplete.php", {
        selectFirst: true
  });
 });
</script>
</head>
<body>
<label>Tag:</label>
<input name="tag" type="text" id="tag" size="20"/>
</body>
</html>

autocomplete.php

This file is called from our jquery script. This php script is easy to follow.
<?php
 $q=$_GET['q'];
 $my_data=mysql_real_escape_string($q);
 $mysqli=mysqli_connect('localhost','username','password','databasename') or die("Database Error");
 $sql="SELECT name FROM tag WHERE name LIKE '%$my_data%' ORDER BY name";
 $result = mysqli_query($mysqli,$sql) or die(mysqli_error());

 if($result)
 {
  while($row=mysqli_fetch_array($result))
  {
   echo $row['name']."\n";
  }
 }
?>
We have done. Run your project and type in your text box you will see following screenshot.

By the way, I use the old jquery plug in this project. If you want to learn new thing, you can learn here and here. I create this tutorial because it is still useful and easy, although it is too old. I will also write about new jquery ui later.
Download Source Code

41 comments:

  1. Thank you for sharing... great job ! would be cool explain how to improve some css customization

    ReplyDelete
  2. Thanks for sharing really cool work

    ReplyDelete
  3. he please, on mouse click event this code is not working properly please can any one help me.

    thanks.

    ReplyDelete
  4. How do you increase the number of items in the list?

    ReplyDelete
  5. Embu Tido: You make a "Limit 20" or another number, in the end of your SQL-statement, I quess.

    ReplyDelete
  6. Hello everyone, I would like to know how to implement urls results by searching the database, so that when clicked, open the reference page. Could you help me?

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. hi thank you for your code but this code does not work for table that contains larger records.

    ReplyDelete
  9. please provide jquery.autocomplete.js,jquery.js,jquery.autocomplete.css files

    ReplyDelete
  10. Sorry but it is not working for me as well , maybe large amount of Data

    can you provide some suggestions Plz !

    ReplyDelete
  11. Hi,
    Amount of data is a problem. I thing "JQuery" should come up with a solution to this problem. Meanwhile you can try a work around. You can assign a value to the "minLength:" method to filter the data extracted from the database and help JQuery's AJAX method function properly. It also makes sense. If number of rows fetched is more than the screen length, it might look awkward. So I suggest, that you assign a value like "minLength: 4". I am sure this will come as a great relief for everyone.

    Arun @ http://www.encodedna.com


    ReplyDelete
  12. sorry dear its not working properly .. please provide another code..
    thanks for try

    ReplyDelete
  13. Is there a way to add autosend form after selecting from dropdown generated by autoselect?

    ReplyDelete
  14. Good job man...
    But I have 2 Questions. What would be edited if
    1. I want to perform search after typing 6 or 8 characters?
    2. And typed character automatically changed into upper case?

    ReplyDelete
  15. Ok I got the solution of first question that is in "minChars:" and trying for finding solution of next question.

    ReplyDelete
  16. hi i have a question i want to send id with this how can it possible ....

    ReplyDelete
  17. please tell me and i want to get this id in hidden field after sending so please tell me the solution for that i m in trouble . :(

    ReplyDelete
  18. how can I more than one auto suggest in a a single page.Please suggest.

    ReplyDelete
  19. Thanks for sharing
    Sharing is caring

    ReplyDelete
  20. hey guys how can i submit form when i select one of the autocomplete result help me..

    ReplyDelete
  21. finally i found solution for above problem
    find and hide the following lines in your jquery.js

    // stop default to prevent a form submit, Opera needs special handling
    //event.preventDefault();
    // blockSubmit = false;
    // return false;

    /* var blockSubmit;

    // prevent form submit in opera when selecting with return key
    $.browser.opera && $(input.form).bind("submit.autocomplete", function() {
    if (blockSubmit) {
    blockSubmit = false;
    return false;
    }
    }); */

    ReplyDelete
  22. i need the value for other page what i selected any one can help

    ReplyDelete
  23. Notice: Undefined index: kkk in C:\xampp\htdocs\myfiles\auto.php on line 2
    ajax jquery mysql php php frameword php tutorial wordpress wordpress theme xml

    ReplyDelete
  24. Great coding. Great job. I am learn for many information about your article. Thank you for sharing...
    http://www.dreamdestinations.in/

    ReplyDelete
  25. In autocomplete.php the third line is: $my_data=mysql_real_escape_string($q);
    Mysql_real_escape_string() is deprecated function. But this autocomplete don't
    work properly with mysqli_real_escape_string() function, can't find upper case
    letters. Is there some solution?

    ReplyDelete
  26. This comment has been removed by the author.

    ReplyDelete
  27. how to do the sategory and sucategory in php.help me

    ReplyDelete
  28. Great tutorials. nice explanations. Thanks for sharing this information.
    http://www.dreamdestinations.in/

    ReplyDelete
  29. hi please tell me how to implement this in a form
    I mean you are entering data in one text box and dta willl be entered automatically in next text box.
    I am entering the zip code of a specific city in one text box and automatically the respective city will come in the city text box.
    Please tell me the code in PHP connected with MYSQL database as the above one .
    Thanks

    City


    State


    Zip


    ReplyDelete
  30. Thank you friend, very helpful, there is another example, it may help you:
    http://www.bewebdeveloper.com/tutorial-about-autocomplete-using-php-mysql-and-jquery

    ReplyDelete
  31. php code for photo tagging with demo and database

    http://www.lessonwithdemo.com/205/ajax/photo-tagging-like-facebook-without-page-refresh-with-ajax-without-jquery.php/

    ReplyDelete
  32. You can refer to the source code here
    http://www.kythuatmang.net/

    ReplyDelete
  33. It's Working Great... That's exact I am searching for. Thanks a lot. Thanks again.

    ReplyDelete