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.

Cheers!
Rodric MacOliverResearcherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leakim971PluritechnicianCommented:
what is your current code client and server side?
what are your knowledges in jquery and php?
0
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.
0
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...
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

leakim971PluritechnicianCommented:
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 :
<?php

$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/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
zappafan2k2Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.