jQuery PHP Ajax Autosuggest
I’ve been working on a lot of JavaScript and PHP projects at work recently which is one of the reasons why I haven’t written a post for the last week. During my many projects i had the need to use an ‘autosuggest’ script that would, in my case, get a list of 10 schools based on the users input in a text field. I thought I’d share this with you as it’s probably easier than you think to do with JavaScript.
Lets run through the JavaScript.
There are two functions in the block of code below. the first one ’suggest’ performs the ajax request based on the users input into the text field which is passed into the function when the function is run. We reference the input with the variable ‘inputString’.
the first section of code is a simple if statement to check if the length of the users input in the text box is more than 0. If it is we run the ajax request to get the list of suggestions from the database. #country refers to the id of the text input in our form. You can see that we’re adding the class ‘load’ to the text input. This is to display an animated loading gif to show the user that we’re getting data from the database.
We then use ‘$.post’ to post the users input text to the ‘autosuggest.php’ page for processing. The autosuggest.php page simple returns a result based on a LIKE % sql query.
Once we have data back from the ‘autosuggest.php’ file, we fade in the suggestion box and inject the contents into the ‘#suggestionsList’ div using ‘.html’. We finally remove the ‘load’ class which removes the animated loading gif.
The function ‘fill()’ populates the text input field with the users selection if they click in a country from the suggested list, it then fades out the ‘#suggestions’ div removing it from the page.
function suggest(inputString){
if(inputString.length == 0) {
$('#suggestions').fadeOut();
} else {
$('#country').addClass('load');
$.post("autosuggest.php", {queryString: ""+inputString+""}, function(data){
if(data.length >0) {
$('#suggestions').fadeIn();
$('#suggestionsList').html(data);
$('#country').removeClass('load');
}
});
}
}
function fill(thisValue) {
$('#country').val(thisValue);
setTimeout("$('#suggestions').fadeOut();", 600);
}
The HTML.
This is the HTML needed to get the autosuggest feature working. Its a simple form, with a text input with an onKeyup and onBlur attribute. The suggestion box with arrow is situated below the form input and positioned absolutely relative to the div suggest.
<form id="form" action="#">
<div id="suggest">Start to type a country: <br />
<input type="text" size="25" value="" id="country" onkeyup="suggest(this.value);" onblur="fill();" class="" />
<div class="suggestionsBox" id="suggestions" style="display: none;"> <img src="arrow.png" style="position: relative; top: -12px; left: 30px;" alt="upArrow" />
<div class="suggestionList" id="suggestionsList"> </div>
</div>
</div>
</form>
Included in the download package is the database .sql file needed to create the table to search from. You could however modify the SQL query to point to a different table. The database connection details are also declared at the top of the autosuggest.php page however it is advised to abstract these from the page.
Great!…realy easy way!..thanks
cool I like it
why don’t you make an if message were it says no results found when their are no results rather then showing a blank box.
Very clean and easy!
Loving your posts! Keep em comming.
jQuery PHP Ajax Autosuggest…
During my many projects i had the need to use an ‘autosuggest' script that would, in my case, get a list of 10 schools based on the users input in a text field.
…
My only suggestion is that you turn auto-complete off for the text box, as it currently interferes with the auto suggest drop down.
Also, I believe being able to select via the arrow keys is an important usability feature.
Other than that, cracking job, very nice indeed
To check if there are any rows u need to pass the query to a associtve function IN PHP
mysql_fetch_assoc();
mysql_fetch_array();
$q = “QUERY STRING”;
$res = mysql_query($q);
if(mysql_num_rows($res) == 0){
// No Rows Found
} else {
// DISPLAY VALUES
$row = mysql_fetch_assoc($res);
echo $row['value'];
}
@Rocky thanks for that
[...] This post was Twitted by nVitou [...]
[...] dem Blog PaperMashUp wird eine schöne Autosuggest Lösung mit JQuery vorgestellt, habe ich nun selber gerade bei einer Seite eingesetzt, und : Works as [...]
[...] jQuery PHP AJAX Autosuggest Autosuggest 是目前的 Web 应用程序中一个有用的功能。了解如何使用 jQuery 和 [...]
[...] API for the SharePoint webservices by Darren Johnstone to query the data and jQuery PHP Ajax Autosuggest by Ashley to create the suggestion [...]
up/down key is not working….
@tom Yeah it wont work because its not a form drop down element just a set of list items with the data in
Hello guys, I’ve just tried this script and it looks and works great, I just have one question, is there any way to make it search for 2 different table fields? Lets say we want to search for city or country, its that possible? Ill really appreciate your help
@Jessica this is possible by querying 2 tables at once. here is an example:
SELECT countries.country, cities.city FROM countries, cities WHERE countries.country LIKE ‘$queryString%’ OR cities.city LIKE ‘$queryString%’ LIMIT 10;
This is the original query:
SELECT country FROM countries WHERE country LIKE ‘$queryString%’ LIMIT 10
NOTE: i haven’t tested the modified query but this should give you an idea of how to achieve the result your looking for.
Hey! nice work but i wonder if you can get the needed functions out of the jquery.js and put it in a file cos we are talking about over 100 kb file in here : ) and some people are still using 56 kbps connections out here : )
you can always get the compressed javascript jquery from here: http://code.google.com/apis/ajaxlibs/documentation/index.html#jquery
Fantastic tutorial, I will be implementing this into my store soon! Thanks very much!
Hi!
Great tool.But I have a question,how do I make a suggestion clickable?
[...] 8.jQuery PHP AJAX Autosuggest [...]
good one, how does the suggestion can be clickable?
Nice work.
That worked for me, on the autosuggest.php file I just added the following code:
$(function(){
$(“li”).click(function(){
$(“#country”).val(
$(this).html()
);
});
});
@Juan glad to see you figured it out.
[...] 8. jQuery PHP AJAX Autosuggest [...]
Hello good work.
Solution for to see if there is no result:
$query = $db->query(“SELECT country FROM countries WHERE country LIKE ‘$queryString%’ LIMIT 10″);
$num = $query->num_rows;
if($num!=0) {
Regards.
Thanks a lot for this, have put it to great use in my first effort at piecing together Javascript, PHP & SQL – I’m usually just a “make stuff pretty” kinda guy.
However, I hit a problem with this, could be down to some other part of my code, who knows. I couldn’t get IE7 or lower to work with the – no any other on… command I put in it’s place. Just flat wouldn’t work at all.
Anyway, long story short, change this :
——————————————————————————
[code='php']
if($query) {
echo '';
while ($result = $query ->fetch_object()) {
echo 'country).'\');">'.$result->country.'';
}
echo '';
[/code]
——————————————————————————
to :
——————————————————————————
[code='php']
if($query) {
while ($result = $query ->fetch_object()) {
echo ' country).'\');">'.$result->country.'';
}
[/code]
——————————————————————————
and change the CSS from:
——————————————————————————
[code='css']
.suggestionList {
margin: 0px;
padding: 0px;
}
.suggestionList ul li {
list-style:none;
margin: 0px;
padding: 6px;
border-bottom:1px dotted #666;
cursor: pointer;
}
.suggestionList ul li:hover {
background-color: #FC3;
color:#000;
}
ul {
font-family:Arial, Helvetica, sans-serif;
font-size:11px;
color:#FFF;
padding:0;
margin:0;
}
[/code]
——————————————————————————
to:
——————————————————————————
[code='css']
.suggestionList {
font-family:Arial, Helvetica, sans-serif;
font-size:11px;
color:#FFF;
list-style:none;
margin: 0px;
padding: 6px;
border-bottom:1px dotted #666;
cursor: pointer;
}
.suggestionList:hover {
background-color: #FC3;
color:#000;
}
[/code]
——————————————————————————
and jobs a good ‘un, now works flawlessly in IE& and (hopefully!!) lower by substituting the list with a stack of divs.
Hey, thank you for this, it’s nice.
I have a one little tip to this.
Try altering your table to MyISAM engine in order to use fulltext search, it is much more accurate than LIKE statement.
ALTER TABLE countries ENGINE = MyISAM;
Then make your query something like this.
SELECT country FROM countries WHERE MATCH (country) AGAINST (‘”.$queryString.”‘ IN BOOLEAN MODE);
This way MySQL will return the results faster and it will choose more unique results each time.
Great job!!!
Tks
it’s cool, thanks
…the example is good, i shall remake him under searching on my website… – thanks
Any idea how one would implement multiple autosuggestboxes on one page?
Somehow the script keeps filling the first input field (When selected from the dropdown in the second box) even if I remove the onBlur – fill event.
@Drone, you’ll need to re-work the script, so it works with classes, and adjust the php script that has the onclick event.
Please can anybody tell me how selection with arrow keys will work on the above script?
Great job indeed.
Hi thanks for this great tutorial, it’s almost exactly what i’m looking for, however can somebody help me get this working for an MSSQL database. I’ve tried loads of different things but to no avail.
Cheers in advance
Never mind, i worked it out, here’s how i did it for an MSSQL database (still tweaking it but it works)
Replace the XX’s with your stuff
0) {
$query = mssql_query(“SELECT xxxxx FROM xxxxxx WHERE xxxxxx LIKE ‘%$queryString%’”);
$numRows = mssql_num_rows($query );
if($query) {
echo ”;
for ($i = 0; $i<$numRows; $i++) {
$val = mssql_result($query, $i, "xxxxxx");
echo "$val";
echo "”;
}
echo ”;
} else {
echo ‘OOPS we had a problem’;
}
} else {
// do now’t
}
} else {
echo ‘You cannot access this script directly!’;
}
}
?>
GRATIA
Ashley, You are absolutely fantastic, worked like a charm.
However, I did some tweaking to allow for highlighting of original search string amongst the return suggestions .
Also did a flat file version, you can check it out (I only altered the autosuggest.php file) Tested and works
autosuggest.php
//$db = new mysqli(‘localhost’, ‘root’ ,’root’, ‘unijos_pgdata’);
// form ponle with help form the mashup guys
$txtfile = “info.txt” ;
$maxstrlen = 20 ;
$maxsuggestlen = 7 ;
if(!file_exists($txtfile)) {
echo ‘Could not connect to the database.’;
} else {
if(isset($_POST['queryString'])) {
//$queryString = $db->real_escape_string($_POST['queryString']);
$queryString = $_POST['queryString'] ;
if(strlen($queryString) >0) {
/*
$query = $db->query(“SELECT nation FROM nationality WHERE nation LIKE ‘$queryString%’ LIMIT 10″);
if($query) {
echo ”;
while ($result = $query ->fetch_object()) {
echo ‘nation).’\');”>’.$result->nation.”;
}
echo ”;
} else {
‘;
echo ‘OOPS we had a problem
}
*/
$data = array();
$fh = fopen($txtfile ,r);
while(!feof($fh)){
$data[] = stripslashes(fgets($fh));
}
$i = 0 ;
$fullstring = ”;
if(count($data)){
foreach($data as $dstr){
if(stristr($dstr , $queryString)){
if(strlen($dstr)){ // this check might not be neccessary
$nustr2 = substr($dstr, 0 ,$maxstrlen) ; // the text file am using contains stupid long texts
$start = stripos($dstr, $queryString) ;
$col_len = strlen($queryString) ;
$nustr = colorme($dstr, $start, $col_len) ;
$fullstring .= ”.$nustr.”;
$i++ ;
}
}
if($i > $maxsuggestlen) break ;
}
}
$fullstring .= ”;
echo $fullstring ;
} else {
// do nothing
}
} else {
echo ‘There should be no direct access to this script!’;
}
}
function colorme($dstr, $start, $col_len){
$end = $start + $col_len ;
$retstr = “” ;
$dlen = strlen($dstr) ;
if($end > $dlen) $end = $dlen ;
for($i = 0; $i <= ($start – 1 ); $i++ ) $retstr .= $dstr[$i] ;
$retstr .= "” ;
for($i = $start; $i < $end ; $i++ ) $retstr .= $dstr[$i] ;
$retstr .= '’ ;
for($i = $end ; $i <= $dlen ; $i++ ) $retstr .= $dstr[$i] ;
return $retstr ;
}
Please I forgot, add fclose($fh) to the script
it’s works with IE6 ?
or conflict with other browser?
Two questions:
1) We’re pulling our autosuggest data from a “Locality” table field. Several records in the database have the same Locality. So, for instance, when user types “e” the following duplications display: Elba, Espirito Santo, Erongo, Espirito Santo, Erongo, Elba, Erongo. Type a second letter and the duplicates disappear. Any way to get the duplicates not to display upon entry of the first letter?
2) Has anyone been successful in getting this to work with multiple entry fields, as Jessica and Drone asked? We’ve tried getting it to work by creating new classes, but with no luck.
@David, for your first question, try looking into the MySQL query SELECT DISTINCT, i believe this should fix this problem.
Your second question, I haven’t looked into this but I can’t see it being too much of a problem. Look into jQuery and $(this), This maybe something that i write a post on in the future.
@Ibad, i’ve used this in IE6 with no problems in the past.
Thanks, Ashley. SELECT DISTINCT did the trick. I’ll look into suggestion #2…
This is my solution for searching multiple tables.
$db = new mysqli(‘localhost’, ‘user’ ,’pass’, ‘database’);
if(!$db) {
echo ‘Could not connect to the database.’;
} else {
if(isset($_POST['queryString'])) {
$queryString = $db->real_escape_string($_POST['queryString']);
if(strlen($queryString) >0) {
$query = “SELECT firstname, lastname FROM database WHERE firstname LIKE ‘$queryString%’ LIMIT 30″;
if ($result = mysqli_query($db, $query)) {
echo ”;
while ($obj = mysqli_fetch_object($result)) {
echo ‘firstname).’ ‘.addslashes($obj->lastname).’\');”>’.$obj->firstname.’ ‘.$obj->lastname.”;
}
echo ”;
} else {
‘;
echo ‘OOPS we had a problem
}
} else {
// do nothing
}
} else {
echo ‘There should be no direct access to this script!’;
}
}
Also, if you want it to come back with no results found dialogue, replace just past query with this code:
$result = mysqli_query($db, $query);
if ($row_cnt = mysqli_num_rows($result)) {
echo ”;
while ($obj = mysqli_fetch_object($result)) {
echo ‘firstname).’ ‘.addslashes($obj->lastname).’\');”>’.$obj->firstname.’ ‘.$obj->lastname.”;
}
echo ”;
} else {
echo ‘ No Results Found’;
}
} else {
// do nothing
}
} else {
echo ‘There should be no direct access to this script!’;
}
}
Ashley, getting this to work with multiple entry fields was a lot easier than I thought.
I had missed the fact that the script uses two functions:
suggest()andfill().Duh. If you rename those uniquely for use with each field in the JS, HTML, and PHP, it will work.Also, of course, make unique the names of several of the IDs and classes in the HTML, JS, and CSS. (I probably did more than I needed to.)
And my first autosuggest list kept displaying underneath the second entry field, so I had to change the z-index of its container,
.suggestionsBox.Hello from Germany,
great script – thanks for your work.
Is it possible to send the form automatically after clicking one of my results?
Thanks
Dennis
hi
it works nice, how do i make the result scrollable, as i’m having a big list of subcategories starting with word computer
So if i search for keyword starting with c, it ends to big list till i complete typing computer
so any way to make things scrollable and display limit to 10
thanks
@zia, you could use CSS and set a height on the drop down div with overflow scroll.
Leave a comment...
Connect
Latest Poll
Recent Posts
Design & Dev Jobs
Full-time and freelance job opportunities available at Authentic Jobs:
Post a job and reach web professionals everywhere.