mySQL Search Function

[keepitcoder]

So I have a basic search form that goes through and does a mySQL like '%$sanitizedInput%' to column in the table.

 

The problem I have is that if I have two rows, one with a value "green" and the second "blue", and I do a search for "green", it shows the green result, if I do a search for "blue", it shows the blue result, but if i do a search for "green blue" it returns no results.

 

Any ideas on how to fix this?

[nethnet]

Are you trying to search for any row that matches ANY of the search terms, instead of ALL of them?

 

If so, explode your search query and modify your SQL to include all of the possibilities.

 

<?php

$searchterm = "red blue green";
$explode = explode(" ", $searchterm);

$sql = "SELECT * FROM `table` WHERE `column` LIKE '%{$explode[0]}%'";

for ($i = 1; $i < count($explode); $i ++){
     $sql .= " OR `column` LIKE '%{$explode[$i]}%'";
}

$query = mysql_query($sql);

?>

 

Did I understand your question correctly?

 

Theo

[abdfahim]

'%green blue%' will return those rows which have the phrase "green blue" precede and followed by anything. So, normally you will not get only green or only bule. fulltext search might serve your purpose

[Alex]

Are you trying to search for any row that matches ANY of the search terms, instead of ALL of them?

 

If so, explode your search query and modify your SQL to include all of the possibilities.

 

<?php

$searchterm = "red blue green";
$explode = explode(" ", $searchterm);

$sql = "SELECT * FROM `table` WHERE `column` LIKE '%{$explode[0]}%'";

for ($i = 1; $i < count($explode); $i ++){
     $sql .= " OR `column` LIKE '%{$explode[$i]}%'";
}

$query = mysql_query($sql);

?>

 

Did I understand your question correctly?

 

Theo

That can be simplified and made a bit cleaner.

 

$searchterm = "red blue green";
$sql = "SELECT * FROM `table` WHERE `column` LIKE '%" . str_replace(' ', "%' OR `column` LIKE '%", $searchterm) . "%'";