Anything which uses LIKE '%$word%' will be really slow for large datasets, or large users. You might look at the newer versions of mysql which support the "MATCH('word') AGAINST( column )" feature... called full text indexing.
The strategy that i'm using is first to get the keywords into separate arrays ($includes, $excludes) and then building up the WHERE statement:
<?PHP
$sql = '';
foreach( $includes as $term )
{
$sql .= " AND MATCH( '$term' ) AGAINST(column) ";
}
foreach( $excludes as $term )
{
$sql .= " AND NOT MATCH( '$term' ) AGAINST(column) ";
}
?>
You can do something similar (replace AND MATCH AGAINST with "AND column LIKE "%$term%") but adding support for parsing OR keywords might be difficult.
If you can control the input, it would be a lot easier to break out the OR'ed search boxes on the input side, like:
<form action="dosearch.php">
Search: <input type=text name=search1><br>
- or - <input type=text name=search2><br>
- or - <input type=text name=search3><br>...
</form>
and then run the above functions on each inputted search field, joining with OR's:
<?PHP
$final_sql = "($search1_sql) OR ($search2_sql) OR ...";
?>
Hope this helps!
--Robert
