Simple search in MySql

Question

I'm trying to create a basic search functionality for MySQL InnoDB. I know there is Full-text search, but I just want to make an easy and simple solution for now. What I want is that when a user search for example "BMW Car" I want to find results not just like "BMW Car" but also "Car BMW" "BMW Z4 Car" and so on.. Is there an easy way to do this? How would you implement a search like this?

Right now I'm using LIKE:

SELECT title,id FROM table WHERE title LIKE '%BMW Car%'

But that will not give me results like "Car BMW" "BMW Z4 Car"..

Answer 1

Have a look at the logical operators in MySQL: http://dev.mysql.com/doc/refman/5.1/en/logical-operators.html

# Title must contain either "bmw" or "car"
SELECT title, id FROM table WHERE title LIKE '%bmw%' OR title LIKE '%car%'

#Title must contain both "bmw" and "car"
SELECT title, id FROM table WHERE title LIKE '%bmw%' AND title LIKE '%car%'

Answer 2

You can work with two LIKEs:

SELECT title,id FROM table WHERE title LIKE '%BMW%' AND title LIKE '%Car%'

If there weren't the need to change the server's configuration to reduce the minimum key word size from 4 to 3 to match BMW, I'd say you'd be better off with full-text search in boolean mode from the start. It is so much more flexible:

SELECT title,id FROM table WHERE MATCH (title)
AGAINST ('+BMW +Car -Volvo' IN BOOLEAN MODE);

full-text search can also search for phrases:

SELECT title,id FROM table WHERE MATCH (title)
AGAINST ('+"BMW Z4" -"BMW Z3"' IN BOOLEAN MODE);

Answer 3

This will take some pre-processing to split the user input up into words. In php, you could do:

$sql = "SELECT title,id FROM table WHERE " ;
$or = "" ;
$inputs = explode(" ", $_GET['q']) ;
foreach($inputs as $input) {
    if(trim($input)=="") continue ;
    $sql .= $or."title LIKE '%".sprintf("%s", mysql_real_escape_string($input))."%' " ;
    $or = "OR " ;
}
$result = mysql_query($sql) ;

Answer 4

You should look into using Lucene for search functionality like this. It's entire existence revolves around searching documents. You'd get a document ID back (which would represent your tables PK) and then select out of the DB using that.