Papermashup

Subscribe


Tweets


"RT @MagnificHQ: Dude miraculously saves his #drone from crashing into ocean in weird futuristic Baywatch scene http://t.co/FspY4TKES5 http:…"

@ashleyford 22 hours ago

"Thanks @KBedders @bethgordon @twitteruk for #BrandingMe! http://t.co/NNA2ke8NHc"

@ashleyford 1 day ago

Designer and web developer, Co-founder and Technical Director at Harkable.com. Previously I worked at Spotify, MySpace and InMobi. Contact me - ashley[at]papermashup.com

jQuery PHP Ajax Autosuggest

AshleyAshley

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:

      <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.

demodownload

Designer and web developer, Co-founder and Technical Director at Harkable.com. Previously I worked at Spotify, MySpace and InMobi. Contact me - ashley[at]papermashup.com

Comments 108
  • Scott
    Posted on

    Scott Scott

    Reply Author

    Been pulling my hair out for hours trying to get this to work and finally found the problem. This script will not work on https secure pages. >:|

    Is there any way to get this to work on a secure page? I love the script and really want to use it.


  • Anil
    Posted on

    Anil Anil

    Reply Author

    Can you please tell us how to enable arrow keys for selection


  • Anil
    Posted on

    Anil Anil

    Reply Author

    Can you please tell us how to enable arrow keys for selection


  • Alan
    Posted on

    Alan Alan

    Reply Author

    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 {


  • hekmatullah
    Posted on

    hekmatullah hekmatullah

    Reply Author

    this is a good website i really like this


  • usman
    Posted on

    usman usman

    Reply Author

    Hi, thnx for this lovely post, it helped me alot, this work is awesome, keep it up


  • John
    Posted on

    John John

    Reply Author

    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


  • shubhbandh
    Posted on

    shubhbandh shubhbandh

    Reply Author

    Thanks really it’s great code. I was looking for that only. I want to use in e-commerce site.


  • sherry
    Posted on

    sherry sherry

    Reply Author

    Thank you for the Fantastic tutorial!
    I wonder if there is a way to make the up/down arrow to work?


  • Julian
    Posted on

    Julian Julian

    Reply Author

    Is there nobody who can solve the problem with the arrow keys?


  • Radhe
    Posted on

    Radhe Radhe

    Reply Author

    good tutorial………….!


  • mostafa
    Posted on

    mostafa mostafa

    Reply Author

    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.


  • donford
    Posted on

    donford donford

    Reply Author

    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
    }
    }
    ?>


  • mike
    Posted on

    mike mike

    Reply Author

    I have everything hooked up but the .post() jquery method isnt returning the results even though its hitting the ajax url

    any ideas?


  • Momo
    Posted on

    Momo Momo

    Reply Author

    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″);


  • Momo
    Posted on

    Momo Momo

    Reply Author

    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


  • Momo
    Posted on

    Momo Momo

    Reply Author

    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.


  • dan
    Posted on

    dan dan

    Reply Author

    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


  • Sir Terr
    Posted on

    Sir Terr Sir Terr

    Reply Author

    Hi,
    clean script, but I have problem with Jquery up to 1.3.2. It’s does’nt work for me correctly.


  • Roxana
    Posted on

    Roxana Roxana

    Reply Author

    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


  • Elias
    Posted on

    Elias Elias

    Reply Author

    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


  • fati
    Posted on

    fati fati

    Reply Author

    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


  • yavuz
    Posted on

    yavuz yavuz

    Reply Author

    would be great if we could choose with our direction keys :)


  • nikunj shingala
    Posted on

    nikunj shingala nikunj shingala

    Reply Author

    Hey…Its awesome yar ……I try and run many Autosuggest but it is clean and superb effective. :) Thanks for post dude :)


  • hoshiar
    Posted on

    hoshiar hoshiar

    Reply Author

    Good script .. very useful.. weldone…..


  • Markus
    Posted on

    Markus Markus

    Reply Author

    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 :-(


  • Marko
    Posted on

    Marko Marko

    Reply Author

    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 :)


  • Mingyang Gao
    Posted on

    Mingyang Gao Mingyang Gao

    Reply Author

    Great! Thanks!


  • jarenzo
    Posted on

    jarenzo jarenzo

    Reply Author

    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!


  • melbouy
    Posted on

    melbouy melbouy

    Reply Author

    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.


  • Ghulam Haider
    Posted on

    Ghulam Haider Ghulam Haider

    Reply Author

    Excellent and simplest work…… !
    how can we add functionality to control selection from arrow buttons?


  • Marty
    Posted on

    Marty Marty

    Reply Author

    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!


  • Kyathi
    Posted on

    Kyathi Kyathi

    Reply Author

    Awesome


  • Manivannan
    Posted on

    Manivannan Manivannan

    Reply Author

    how to make suggestion box appear all above the site content.
    currently suggest box appear below my flash player and it looks ugly


  • peter
    Posted on

    peter peter

    Reply Author

    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


  • David
    Posted on

    David David

    Reply Author

    Great….But take a look at this too:
    http://codecanyon.net/item/isuggest-v11/195446


  • ndhol
    Posted on

    ndhol ndhol

    Reply Author

    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


  • Michael
    Posted on

    Michael Michael

    Reply Author

    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


  • vishwas
    Posted on

    vishwas vishwas

    Reply Author

    How can be manage is arrow key down, please help me


  • zaifox
    Posted on

    zaifox zaifox

    Reply Author

    guys what will be the modification if i need to search stuff on my website. and not search for countries…


  • Julian Gonzalez
    Posted on

    Julian Gonzalez Julian Gonzalez

    Reply Author

    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.


  • hichem ben chaaben
    Posted on

    hichem ben chaaben hichem ben chaaben

    Reply Author

    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 .


  • Sumit Joshi
    Posted on

    Sumit Joshi Sumit Joshi

    Reply Author

    Hi, there!
    How can I user UP and DOWN arrow keys to move around to it?
    Please help me out.

    Thanks in advance.


  • Nijil
    Posted on

    Nijil Nijil

    Reply Author

    Is it possible to pass multiple parameters


  • wu
    Posted on

    wu wu

    Reply Author

    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’?


  • Scott Pickles
    Posted on

    Scott Pickles Scott Pickles

    Reply Author

    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


  • Besucher80
    Posted on

    Besucher80 Besucher80

    Reply Author

    Hi, great code .. i love it …

    but is there a way to disable and enable the suggest?

    With a link or a checkbox?

    Thanks


  • hanumant
    Posted on

    hanumant hanumant

    Reply Author

    you can solve my…..json and php dropdown query


  • Dwight
    Posted on

    Dwight Dwight

    Reply Author

    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


  • akshay shah
    Posted on

    akshay shah akshay shah

    Reply Author

    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.


  • David
    Posted on

    David David

    Reply Author

    @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.


  • Subroto Mahindar
    Posted on

    Subroto Mahindar Subroto Mahindar

    Reply Author

    if there are more that 1 textboxes with different id. then please suggest how to work.
    Thanks in advance


  • Neil Adavan
    Posted on

    Neil Adavan Neil Adavan

    Reply Author

    I think the code lacks some lines..

    while ($result = $query ->fetch_object()) {
    echo ”.$result->country.”;

    there where no link when i print it.

    thankss…

    by the way great tutorial..


  • Neil Adavan
    Posted on

    Neil Adavan Neil Adavan

    Reply Author

    i badly needed it for my next project..

    thank you..


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @Neil,all you need to do is change this in autosuggest.php:

      [ecko_code_highlight='php']
      while ($result = $query ->fetch_object()) {
      echo ‘country).”);”>’.$result->country.”;
      }

      [/ecko_code_highlight]

      to this:

      [ecko_code_highlight='php']
      while ($result = $query ->fetch_object()) {
      echo ‘‘.$result->country.’‘;
      }

      [ecko_code_highlight]

      Hope that helps.
      Let me know how you get on.


  • Neil
    Posted on

    Neil Neil

    Reply Author

    how does the suggestion be clickable???

    for example: when i clicked the country Philippines it will go to (sample.php)another page..

    thankss


  • Sashi
    Posted on

    Sashi Sashi

    Reply Author

    Thanks for the example, works awesome !


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @Sashi, No problem, glad you found it useful!


  • Savio
    Posted on

    Savio Savio

    Reply Author

    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


  • zia
    Posted on

    zia zia

    Reply Author

    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


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @zia, you could use CSS and set a height on the drop down div with overflow scroll.


  • Dennis
    Posted on

    Dennis Dennis

    Reply Author

    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


  • David
    Posted on

    David David

    Reply Author

    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.


  • Andrew
    Posted on

    Andrew Andrew

    Reply Author

    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!';
    }
    }


  • Andrew
    Posted on

    Andrew Andrew

    Reply Author

    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!';
    }
    }


  • David
    Posted on

    David David

    Reply Author

    Thanks, Ashley. SELECT DISTINCT did the trick. I’ll look into suggestion #2…


  • David
    Posted on

    David David

    Reply Author

    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.


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @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
    Posted on

    Ibad Ibad

    Reply Author

    it’s works with IE6 ?
    or conflict with other browser?


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @Ibad, i’ve used this in IE6 with no problems in the past.


  • Ponle
    Posted on

    Ponle Ponle

    Reply Author

    Please I forgot, add fclose($fh) to the script


  • Ponle
    Posted on

    Ponle Ponle

    Reply Author

    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 ;

    }


  • mark taylor
    Posted on

    mark taylor mark taylor

    Reply Author

    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!';
    }
    }
    ?>


  • mark taylor
    Posted on

    mark taylor mark taylor

    Reply Author

    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


  • Anjali
    Posted on

    Anjali Anjali

    Reply Author

    Please can anybody tell me how selection with arrow keys will work on the above script?

    Great job indeed.


  • Drone
    Posted on

    Drone Drone

    Reply Author

    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.


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @Drone, you’ll need to re-work the script, so it works with classes, and adjust the php script that has the onclick event.


  • DIM$ON
    Posted on

    DIM$ON DIM$ON

    Reply Author

    …the example is good, i shall remake him under searching on my website… – thanks


  • DIM$ON
    Posted on

    DIM$ON DIM$ON

    Reply Author

    it’s cool, thanks :)


  • Vinidog
    Posted on

    Vinidog Vinidog

    Reply Author

    Great job!!!

    Tks 😉


  • Zoran
    Posted on

    Zoran Zoran

    Reply Author

    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.


  • Tony B
    Posted on

    Tony B Tony B

    Reply Author

    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 :
    ——————————————————————————
    [ecko_code_highlight='php']

    if($query) {
    echo ”;
    while ($result = $query ->fetch_object()) {
    echo ‘country).”);”>’.$result->country.”;
    }
    echo ”;

    [/ecko_code_highlight]
    ——————————————————————————

    to :
    ——————————————————————————
    [ecko_code_highlight='php']

    if($query) {
    while ($result = $query ->fetch_object()) {
    echo ‘ country).”);”>’.$result->country.’‘;
    }

    [/ecko_code_highlight]

    ——————————————————————————

    and change the CSS from:
    ——————————————————————————
    [ecko_code_highlight='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;
    }

    [/ecko_code_highlight]
    ——————————————————————————

    to:
    ——————————————————————————

    [ecko_code_highlight='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;
    }

    [/ecko_code_highlight]
    ——————————————————————————

    and jobs a good ‘un, now works flawlessly in IE& and (hopefully!!) lower by substituting the list with a stack of divs.


  • alex5o2
    Posted on

    alex5o2 alex5o2

    Reply Author

    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.


  • Juan Maia
    Posted on

    Juan Maia Juan Maia

    Reply Author

    That worked for me, on the autosuggest.php file I just added the following code:

    $(function(){
    $(“li”).click(function(){
    $(“#country”).val(
    $(this).html()
    );
    });
    });


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @Juan glad to see you figured it out.


  • Juan Maia
    Posted on

    Juan Maia Juan Maia

    Reply Author

    good one, how does the suggestion can be clickable?

    Nice work.


  • Zoli
    Posted on

    Zoli Zoli

    Reply Author

    Hi!
    Great tool.But I have a question,how do I make a suggestion clickable?


  • Craig
    Posted on

    Craig Craig

    Reply Author

    Fantastic tutorial, I will be implementing this into my store soon! Thanks very much!


  • AL-Kateb
    Posted on

    AL-Kateb AL-Kateb

    Reply Author

    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 : )


  • Jessica
    Posted on

    Jessica Jessica

    Reply Author

    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 :)


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @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.


  • tom
    Posted on

    tom tom

    Reply Author

    up/down key is not working….


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @tom Yeah it wont work because its not a form drop down element just a set of list items with the data in


  • Rocky
    Posted on

    Rocky Rocky

    Reply Author

    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’];
    }


    • Ashley
      Posted on

      Ashley Ashley

      Reply Author

      @Rocky thanks for that 😉


  • Jamie Bicknell
    Posted on

    Jamie Bicknell Jamie Bicknell

    Reply Author

    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


  • Visualsuspect
    Posted on

    Visualsuspect Visualsuspect

    Reply Author

    Very clean and easy!
    Loving your posts! Keep em comming.


  • Sufian Hassan
    Posted on

    Sufian Hassan Sufian Hassan

    Reply Author

    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.


  • Felix
    Posted on

    Felix Felix

    Reply Author

    Great!…realy easy way!..thanks