• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

How to create a search field with autocomplete

Hi folks, I need to create a search file that searches in a MySql database in a certain field for something contained in it, for example:

Search for the word or partial word in a varchar field. It should complete itself on real time when typing...
Any help would be appreciated.

Rodric MacOliver
Rodric MacOliver
1 Solution
what is your current code client and server side?
what are your knowledges in jquery and php?
Ray PaseurCommented:
I may have something in my teaching library.  If I do, I will post it here.  One caution about auto-complete... you may get too many results if you start the auto-complete process before the client types about 4 or 5 characters.  Consider using LIMIT on your queries.
Rodric MacOliverResearcherAuthor Commented:
Ray I hope you do as usual you come to my rescue... :)

leakim971: php, unfortunately I don't have a solid knowledge in JQuery but I do know enough I think of php...
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Click on view source on this page : http://jqueryui.com/autocomplete/#multiple-remote

the doc is here : http://api.jqueryui.com/autocomplete/
to limit the number of char to type before sending the query(doing the ajax call) to server use minLength parameter : http://api.jqueryui.com/autocomplete/#option-minLength

on the server side (in the example search.php) you just need to read the "term" parameter :

$term = $_GET["term"];

// init connexion database
$sql = "SELECT * FROM mytable WHERE myfield LIKE '%" . $term . "'";

// run query

$arr = array();
// now we loop over all results
foreach($rows as $row) { 
  $arr[] = array("label"=>$row["fieldname"], "value"=>$row["fieldname"]);

echo json_encode($arr);

Open in new window

you've a good and complete solution here : http://www.daveismyname.com/tutorials/php-tutorials/autocomplete-with-php-mysql-and-jquery-ui/
Ray PaseurCommented:
I think @leakim971 has you on the right track.  No points for this, but you might want to think about using ORDER and LIMIT clauses in the query shown on line 6.

Best to all, ~Ray
Are you dead set on having an autocomplete?  If you're not comfortable with jQuery or Javascript, you are in for a learning curve, albeit a worthwhile one (in my opinion).  Once familiar with jQuery, though, autocompletes are pretty easy.

If you can give us a link to a page you have started, we can help.

Obviously, leakim971 has written a simple example, but I have three comments in case you aren't familiar with that aspect.
1. You should always escape your search term using mysql_escape_string() or even using PDO.
2. I would advise you to make the search case-insensitive.  See below.
3. It is generally advisable to add the following header to the search file:
header("Cache-Control: no-cache, must-revalidate");  You don't want the page to be cached.

I would modify lines 3 and 5 of @leakim971's code thusly:
$term = mysql_escape_string(strtoupper($_GET["term"]));

// init connexion database
$sql = "SELECT * FROM mytable WHERE UPPER(myfield) LIKE '%" . $term . "%'";

Open in new window

I added a second '%' in line 5.  The way it was before would only match the search term at the end of the field.  If you only want to the search term to match the beginning of the field, remove the first '%' sign.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now