PHP JQuery autocomplete on huge database: PHP, MYSQL, JQUERY, Autocomplete

by: hieloPosted on 2009-08-07 at 09:34:28ID: 25044540

Here's a jQuery autocomplete plugin:
http://bassistance.de/jquery-plugins/jquery-plugin-autocomplete/

One of the options is:
minChar

If you were to set it to 3, then the user would need to type at least three chars before it makes a request to the server. Available options are here:
http://docs.jquery.com/Plugins/Autocomplete/autocomplete#url_or_dataoptions

by: hieloPosted on 2009-08-07 at 09:36:31ID: 25044566

Look at the demo page:
http://jquery.bassistance.de/autocomplete/demo/

The code for the "Month" textbox is attached so you can see how it is used:

	$("#month").autocomplete(months, {
		minChars: 0,
		max: 12,
		autoFill: true,
		mustMatch: true,
		matchContains: false,
		scrollHeight: 220,
		formatItem: function(data, i, total) {
			// don't show the current month in the list of values (for whatever reason)
			if ( data[0] == months[new Date().getMonth()] ) 
				return false;
			return data[0];
		}
	});

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

Select all Open in new window

by: navinbabuPosted on 2009-08-07 at 09:56:15ID: 25044784

Hello ,

Is it possible to go on to the database once pickup any char starting with say "SW" and then put all the words starting with "SW" in an array and then maybe feed it to the auto-completer

Yes , the input word is queried against the database the results are collected in array and are fed to the autocompleter

I have a huge database which holds almost half a million words in it

We are querying the word against database how large is the database

Im including all the code and jquery file as zip . Please check the code and let me know if you need anything else .

JavaScript
 
<script src="jquery-1.2.1.pack.js" type="text/javascript"></script>
<script type="text/javascript">
 
function lookup(inputString) {
    if(inputString.length == 0) {
        // Hide the suggestion box.
        $('#suggestions').hide();
    } else {
        $.post("rpc.php", {queryString: ""+inputString+""}, function(data){
            if(data.length >0) {
                $('#suggestions').show();
                $('#autoSuggestionsList').html(data);
            }
        });
    }
} // lookup
 
function fill(thisValue) {
    $('#inputString').val(thisValue);
   $('#suggestions').hide();
}
 
</script>
 
 
 
The Php backend file (rpc.php)
 
<?
 
   if(isset($_POST['queryString'])) {
   $queryString = $_POST['queryString'];  
   if(strlen($queryString) >0) {
    $query = "SELECT * from table where something LIKE '$queryString%' LIMIT 10";
    $result = mysql_query($query) or die("There is an error in database ");
     while($row = mysql_fetch_array($result)){
     echo '<li onClick="fill(''.$row[blog_tags].'');">'.$row[blog_tags].'</li>';                                        
      }
   }
   }
?>
 
The html file 
 
<input size="30" id="inputString" onkeyup="lookup(this.value);" type="text" />
 
         <div class="suggestionsBox" id="suggestions" style="display: none;">
      <div class="suggestionList" id="autoSuggestionsList">
 
</div>
 
    </div>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:

Select all Open in new window

by: aniga42Posted on 2009-08-10 at 03:52:50ID: 25058641

navinbabu:

Thanks for the tip, how responsive is your autocompleter with such a huge data, and I have also noticed your sql statements limits to 10 elements of data, so what happens if the keyword I am looking for say "SW" had more than 10,000 elements all starting with "SW" wouldnt that mean I am not getting the correct info populated into the array?

PS. I would love to see your whole script as you said in Zipped format.

again, Many thanks.

hielo:

The info you have given are a very valuable information, I thank you for your effort.

by: hieloPosted on 2009-08-10 at 06:59:26ID: 25059938

>>how responsive is your autocompleter with such a huge data,
because the size of your data is huge, you should only query the db after certain number of characters have been typed. The example posted above will query the db as soon as the first char is typed. If you want the db to be queried after two elements have been typed, I suggest you change:

function lookup(inputString) {
    if(inputString.length == 0) {
...
 
to:
function lookup(inputString) {
    if( !(inputString.length>2) ) {
...
 
>>so what happens if the keyword I am looking for say "SW" had more than 10,000 elements all starting with "SW" wouldnt that mean I am not getting the correct info populated into the array?
Correct, but he is just giving you an example. You need to decide if two records max is acceptable/suit your needs. You can always omit the LIMIT clause completely and get all the records, but you need to test your application to see if it degrades performance. If you have 10,000 that begin with "S", then you will have fewer that begin with "SW". The fewer results returned to the autocomplete, the better your autocomplete fill perform. Why? Some user's will have limited RAM so you need to account for that. If you are really dealing with a large data set, perhaps querying after 3 chars will give you good enough performance:
function lookup(inputString) {
    if( !(inputString.length>3) ) {
...
 
also, make sure you escape the posted data to avoid sql injection by using mysql_real_escape_string:
<?php
   if(isset($_POST['queryString'])) {
 
   //connect to the db here first
   ...
 
   $queryString = mysql_real_escape_string(trim($_POST['queryString']));  
   if( strlen(trim($queryString)) > 0 ) {
    $query = "SELECT * from table where something LIKE '{$queryString}%'";
 
   ...

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

Select all Open in new window

by: navinbabuPosted on 2009-08-10 at 09:06:18ID: 25061234

Yes helio is right u should query the database once the user typed minimum no of words .

@ hielo : May be that is what they call it experience :P

Anyways aniga42

Im including the code as zip with the changes helio suggested

Change the parameters as required and PHP file ofcourse database connection is required .

I tried uplaoding it here but it seems EE doesnt allow it so im uplaoded the zip in to my domain ,,

http://www.thetruehd.com/ee.zip

Cheers

by: hieloPosted on 2009-08-10 at 12:57:30ID: 25063469

>>I tried uplaoding it here but it seems EE doesn't allow it
I've never tried uploading a zip file here, but what may actually work is if you rename you ee.zip to ee.zip.txt and try uploading it. Then aniga42 will just need to rename the ee.zip.txt back to ee.zip before extracting the contents. Again, I am just guessing that will work (must be that experience factor at work again huh :)

Question

PHP JQuery autocomplete on huge database

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

PHP

MYSQL

JQUERY

Autocomplete

PHP and Databases

PHP Scripting Language

Asynchronous Javascript and XML (AJAX)

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press