Mysql search via PHP help

Question

Hi.

I have a textfield variable $string...

It contains for example "BMW COUPE"...

How can i search mysql db field for word matches???

SELECT * FROM db WHERE description xxxxxxxxxxxxxxxxxxx?

description is the field name!

so if description containse either 'BMW' or 'COUPE' it will return this field...

Thanx

Answer 1

it might not be the best way, but if you want to use sql only try

select * from db where description like '%BMW%' or description like '%coupe%';

Answer 2

$string = 'find me';
$stringParts = explode(' ', $string);
$sql = "SELECT * FROM table_name WHERE 1";
foreach ($stringParts as $word) {
    // notice the spaces after and before the %
    $sql .= " AND description LIKE '% " . mysql_real_escape_string(trim($string)) . " %' ";
}

This should do the trick, with the exception that there needs to be space around each "word". So if you have "find-me" in a description, it won't be included in the results.

Answer 3

Using the mysql full text search feature :

SELECT field FROM base WHERE MATCH(description) AGAINST('BMW COUPE')

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

You must add a FULLTEXT index on the fields you want to search :

 ALTER TABLE `table` ADD FULLTEXT `fulltest_test` (`field1`, `field2`)

You can add only one fulltext index per table. But you can add as many fields to it as you need.

Answer 4

One other solution as apparently these ones doesn't satisfy you would be to use Sphing which does full text queries. But is particularly optimized for it.
It's used by several relatively big project.

Answer 5

An alternative to full text searching, which may be sufficient, is to use a REGEXP function.

Your example query might then be:

SELECT *
  FROM db
 WHERE description REGEXP '[[:<:]]$string1[[:>:]]' = 1
    OR description REGEXP '[[:<:]]$string2[[:>:]]' = 1

See http://dev.mysql.com/doc/refman/5.1/en/regexp.html