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.
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);
}
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:
<input id="country" onkeyup="suggest(this.value);" size="25" type="text" />
<div id="suggestions" class="suggestionsBox" style="display: none;">
<img style="position: relative; top: -12px; left: 30px;" src="arrow.png" alt="upArrow" />
<div id="suggestionsList" class="suggestionList"></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.
Hi there I love your post and I have tried your code and it works well, I am able to populate my text box with the postcode,state and suburb if i wish to do so.
One problem I am stuck on is to pass a variable through to another page without the user seeing it, EG I want to pass the longtitude and latitude through but dont want to show it in the dropdown box.
here is some of my code
if(strlen($queryString) >0) {
$query = $db->query(“SELECT suburb,postcode,state,longtitude,latitude FROM Postcode WHERE suburb LIKE ‘$queryString%’ or postcode LIKE ‘$queryString%’ LIMIT 10″);
if($query) {
echo ”;
while ($result = $query ->fetch_object()) {
echo ‘suburb).’\');”>’.$result->suburb.’ ‘.$result->postcode.’ ‘.$result->state.”;
}
echo ”;
} else {
this is a good website i really like this
Hi, thnx for this lovely post, it helped me alot, this work is awesome, keep it up
Hi there i’m using this script but one thing is missing when i press keyCode it doesn’t move up and down to choose from… Help me please
Thanks really it’s great code. I was looking for that only. I want to use in e-commerce site.
Thank you for the Fantastic tutorial!
I wonder if there is a way to make the up/down arrow to work?
Is there nobody who can solve the problem with the arrow keys?
good tutorial………….!
hi,this is very use full script, but my problem is this not support op and down arrow and this is a lake for my program.
thanks.
First thank you for the script!
Here is an mySQL version based directly off yours. Hope it helps others. Only thing that is different is the php page edited for mySQL databases.
START PHP
————————-
0) {
$query = mysql_query(“SELECT countries FROM country WHERE countries LIKE ‘$queryString%’”);
if($query) {
echo ”;
while ($result = mysql_fetch_array($query)) {
echo ”.$result['part_number'].”;
}
echo ”;
} else {
echo ‘OOPS we had a problem
‘;
}
} else {
// do nothing
}
}
?>
I have everything hooked up but the .post() jquery method isnt returning the results even though its hitting the ajax url
any ideas?
USE The latest jquery library from Google
http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.js
ok, i found the solution :
http://www.latko.org/2009/07/02/the-proper-way-to-use-utf8-php-mysql/
You have to use set stuff before every query!!! See my autocomplete part now looks like this. And my russian indexing works.
$db->query(“SET CHARACTER SET ‘utf8′”);
$db->query(“SET collation_connection = ‘utf8_general_ci’”);
$query = $db->query(“SELECT country FROM countries WHERE country LIKE ‘$queryString%’ LIMIT 10″);
ok, i just wanted to give a little heads up. I have been trying to make it work for non ascii characters. So my dbase is in utf8, my page is in utf8. I activated the mbstring options in php.
The problem seems to come from here :
$query = $db->query(“SELECT country FROM countries WHERE country LIKE ‘$queryString%’ LIMIT 10″);
Querystring is fine in utf8.
the query is always empty when it is not a normal character in Querystring
Hello,
First thanks a lot. This is really helpful, i was able to make it work in 5 minutes I was wondering if somebody managed to use this with foreign characters?
My msql is in utf-8, my page too and the mysql connect too. It will show correctly results in regular characters but not foreign.
Many thanks.
I have tried to manipulate this just the tables but i get the oops error. I changed everywhere where it says country in both index and autosuggest but it still does not work. Please Help
Hi,
clean script, but I have problem with Jquery up to 1.3.2. It’s does’nt work for me correctly.
Hi!
Thanks for the script.
Do you have any reply to the post made by Julian Gonzalez on November 4, 2010 at 5:32 am about solving the problem with spanish accents and the letter Ñ in the .js file.
Please, if anyone knows how to solve this could you post it?
Thanks and best regards from Lima, Peru
hi is working very nice but. i try to get surnames english surnames are working fine when i am trying to find a greek surname it doesn’t appear anything use php syntax to convert to utf8
mysql_query(‘set character set utf8′,$db);
mysql_query(“SET NAMES ‘utf8′”,$db);
but nothing
I’ve searched a lot of auto complete tutorial but this is the only one that works for me. I like it and it’s very easy to apply on my project. Thank you very much. d(^___^)b
would be great if we could choose with our direction keys
Hey…Its awesome yar ……I try and run many Autosuggest but it is clean and superb effective.
Thanks for post dude
Good script .. very useful.. weldone…..
Thx for the script.
How can I realize two autosuggest fields on the same page?
I implemented a second parameter on
function suggest(inputString, id) and
function fill(thisValue, id)
id is in this case the id of the input field.
onkeyup=”suggest(this.value, ‘#countrya’);” onblur=”fill(this.value, ‘#countrya’);”
But the function fill is called twoi times. First time it works and at the second time in will destroied
How do I create a database to look for the word in a sentence and not only that outputs the results by the first letter in a sentence. Greetings to all.
Postscript I do not know very well php
Great! Thanks!
Awesome! Thank you!
I’m having a small issue not so much with the script but a minor POST problem once the field has been selected. The code is below. It works in IE9 correctly, not so much on the other 3, they only post the original value of the text field, NOT the one that populates via the function….
function fill(thisValue) {
// populates the sterm field – only field in the form //
$(‘#sterm’).val(thisValue);
// submits the entire form tried this with $.post but couldnt’ figure out the syntax //
document.forms["sterm_form"].submit();
// Theoretically this line never actually runs as the previous one submits the form //
setTimeout(“$(‘#suggestions’).fadeOut();”, 600);
}
The issue is the value being posted by the submit() is NOT the one being applied by this $(‘#sterm’).val(thisValue);
$.post(“search.php”, this_is_where_the_value_goes?_no_idea );
I can’t figure the syntax out. it’s been 2 hours, i’m calling in for backup!
HELP!
In regards to the resulting list elements selectable;
JqueryUI’s selectable function could be easily implemented here, even adding some cool functionality like multiple & lasso selections.
Only problem now is how to attach keyboard actions to this.
Working on it.
Excellent and simplest work…… !
how can we add functionality to control selection from arrow buttons?
Is there a way to increase the timeout of the suggestions?
Right, after 3 seconds they disappear. My boss wants them to stay up longer. Thanks!
Awesome
how to make suggestion box appear all above the site content.
currently suggest box appear below my flash player and it looks ugly
if i just want to work it in my local site, it doesn’t work, how can i do that,
i think that the following line
http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.js
have to be changed, i took the code inside it and put it in my local folder but it doesn’t work
can anyone help me for that
Great….But take a look at this too:
http://codecanyon.net/item/isuggest-v11/195446
Thanks, for a great script. I had a problem with trying to make 2 textfield with autosuggest. I’ve succeed displaying different suggest on each textfield, but when I clicked one of suggest , it show in a wrong textfield. Any idea? Thx
Hey Ashley, great script.
I’m trying to get the fill() funciton to work, but can’t see what your script http://papermashup.com/demos/autosuggest/autosuggest.php generates to return back to the form at http://papermashup.com/demos/autosuggest/
Running a query direct to http://papermashup.com/demos/autosuggest/autosuggest.php results in “There should be no direct access to this script!”
I’m not a PHP coder, so I am wanting to see what HTML that page generates. I’m not getting something quite right, so the fill() function isn’t working.
Thanks,
Michael
How can be manage is arrow key down, please help me
guys what will be the modification if i need to search stuff on my website. and not search for countries…
Hi Buddy,
If you could help me with this I’d be really grateful.
I live in Colombia, so our languaje is spanish, I have a list of the departmens and cities in DB but, they are encoded as UTF8-spanish, so they have accent like “chocó” and when I bring it to the autosuggest appears a black rhombus instead of the accent, I’m in a hurry with this, the only I can think is change all accents in the list of the DB, but could be really nice if i could set up something in the js to show accents correctly.
Thank you very much.
Saludos desde Colombia amigos.
Great source ,
A little bit tricky in if else statements in the php file , i dont prefer to use the mysql socket connection because in some hastings it’s not configured or some doesn’t allow it by default in the php.ini file .
Hi, there!
How can I user UP and DOWN arrow keys to move around to it?
Please help me out.
Thanks in advance.
Is it possible to pass multiple parameters
Hi there
I do like your script, but is it possiable to add up and down keys to select the matched results. and also is there a way to disable the cached results like ‘autocomplete=off’?
Ashley –
Thanks for the code. I don’t see the normal auto complete from browser history on your demo page, nor do I see any JS to disable that. So the issue I’m having is that the auto suggest from your code AND my browser history show up at the same time. I’ve read things on the net about disabling the browser auto complete with ‘autocomplete=off’, but it is only supported in newer browsers. Any suggestions?
P.S. You might want to check your email validation script (if it’s something you wrote and/or control) since it stated that my work email was not valid.
Thanks,
Scott
Hi, great code .. i love it …
but is there a way to disable and enable the suggest?
With a link or a checkbox?
Thanks
you can solve my…..json and php dropdown query
How can I get an hidden value like ID in this script
I can select it by the query id, country but in the onclick handling I can’t make it work
this tutorial is great but it cannot select value in the textbox and also not working up down key and also copy the javascript in new php page it cannot working. and in ie6 it cannot shown the colour when the mouse select any row. if you have any solution please give me.
@Subroto, if you read my 2/10-11/10 and 2/26/10 comments and Ashley’s reply above you should be able to do multiple textboxes. Regarding two textboxes overlapping, the z-index change worked for Mozilla (if I recall), but wouldn’t work for IE, so I had to place the two side by side instead of in a vertical arrangement.
if there are more that 1 textboxes with different id. then please suggest how to work.
Thanks in advance
how does the suggestion be clickable???
for example: when i clicked the country Philippines it will go to (sample.php)another page..
thankss
Thanks for the example, works awesome !
Hi Ashley, brilliant script. I just have a bit of a problem though and would be very grateful if you could shed some light on it.
If I set up the form action as values.php, add a submit button to it, and print out the value of the ‘country’ element in values.php using print_r($_POST), I can’t seem to access it.
So, the value of country, although filled in the form field, cannot be passed on to the action page.
I look forward to hearing from you.
Cheers
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
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
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() and fill(). 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.
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!’;
}
}
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!’;
}
}
Thanks, Ashley. SELECT DISTINCT did the trick. I’ll look into suggestion #2…
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.
it’s works with IE6 ?
or conflict with other browser?
Please I forgot, add fclose($fh) to the script
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 ;
}
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!’;
}
}
?>
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
Please can anybody tell me how selection with arrow keys will work on the above script?
Great job indeed.
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.
…the example is good, i shall remake him under searching on my website… – thanks
it’s cool, thanks
Great job!!!
Tks
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.
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 :
——————————————————————————
if($query) {
echo '';
while ($result = $query ->fetch_object()) {
echo 'country).'\');">'.$result->country.'';
}
echo '';
——————————————————————————
to :
——————————————————————————
if($query) {
while ($result = $query ->fetch_object()) {
echo ' <a>country).'\');">'.$result->country.'</a>';
}
——————————————————————————
and change the CSS from:
——————————————————————————
.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;
}
——————————————————————————
to:
——————————————————————————
.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;
}
——————————————————————————
and jobs a good ‘un, now works flawlessly in IE& and (hopefully!!) lower by substituting the list with a stack of divs.
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.
Hi!
Great tool.But I have a question,how do I make a suggestion clickable?
Fantastic tutorial, I will be implementing this into my store soon! Thanks very much!
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 : )
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
up/down key is not working….
Pingback: PhoneBook Search – No code required | End User SharePoint
Pingback: Twitted by nVitou
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'];
}
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
Pingback: CSS Brigit | jQuery PHP Ajax Autosuggest
Very clean and easy!
Loving your posts! Keep em comming.
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.
Great!…realy easy way!..thanks
Designer and web developer, Co-founder and Technical Director at Harkable.com London. Previously I worked at InMobi, Spotify and MySpace. Interests include photography and making short videos. Also an avid F1 fan. I also run Mega Infographics for your daily dose of the best infographics.
Follow us on Twitter and get in-stream updates.
Subscribe to all the Papermashup tutorials and articles straight to your RSS reader.
Sign up and get all the Papermashup tutorials straight to your inbox.
105 discussions around jQuery PHP Ajax Autosuggest