MySql - autocomplete

Question

Hi Everyone,

I am creating an Ajax autocomplete application and would like know if there is an SQL query I can use for this purpose - e.g if someone types "p" I would like to retrieve all words beginning with "p", if they add "e" retrieve all words starting with "pe" - and continue like that.

Someone suggested the query below but I don't think it's what I'm looking for:

$query = "SELECT* FROM nametable WHERE names LIKE '$partialstring' ";

Answer 1

This should work:

$query = "SELECT * FROM nametable WHERE names LIKE '$partialstring%'"

The %is the wildcard character in SQL.

Edit: And yes, please sanitize that input.

Answer 2

Assuming you are sanitising that input, it should be something like:

$query = "SELECT* FROM nametable WHERE names LIKE '$partialstring%' ";

Answer 3

$query = "SELECT* FROM nametable WHERE names LIKE '$partialstring%' ";

I've added % only on the right side since you would like to have only the words that are beginning with the input letters.

However, please don't use this query until you've filtered it against SQL injections.

Answer 4

You can run a query like what Henning and others have written. And, yes, please sanitize the input to prevent injection attacks. I have also found this method to be rather slow when there are a lot of records. If you wish to match 'Bush' in the word 'GeorgeW**Bush**Senior' ,the above said method wont work. In which case you have to use the query

  select name from nametable where name like '%match%';

Try looking at the mysql full text query method. Its extremely handy in some cases.

Answer 5

Apart from regular special chars, you have to escape those with a special meaning in LIKE clauses.

I haven't fully tested the code but it should be something like this, assuming you are using PHP and the good old mysql extension (you don't mention):

$escape = '|';

$like = strtr(
    mysql_real_escape_string($partialstring),
    array(
     '%' => $escape . '%',
     '_' => $escape . '_',
     $escape => $escape . $escape,
    )
);

$query = "SELECT names FROM nametable WHERE names LIKE '$like%' ESCAPE '$escape' ORDER BY names LIMIT 10";

Also, don't forget to create an index on the names column.