MySQL Searching not using LIKE

search any words in mysql table

[rossmcd]

I have a search function on a mysql table. The LIKE operator is letting me down though. For example, if someone puts in Blue Jacket into the search box they will get the row containing Blue Jacket in the results. However, I ALSO want them to get the same result (possibly more) if they wrote Jacket Blue or Blue or Jacket. I know this is possible but it is way too complicated for me. Here is the line from my code:

$query = "select a.itemname,a.shortdescription,a.longdescription2,a.longdescription1,a.image1_small,a.price,a.productid,a.category from products a,category b where a.itemname like '%$searchtext%' or a.shortdescription like '%$searchtext%' or a.longdescription2 like '%$searchtext%' or a.longdescription1 like '%$searchtext%' and a.category = b.catid group by a.productid order by a.price,a.itemname";

Can anyone tell me how to do this with the code above? If it affects anything I'm using templatepower html templates with my php code.

Many thanks in advance,

Ross

[zCat]

You might like to take a look at MySQL's fulltext search capabilities:

[dev.mysql.com...]

That is generally the way to go once you find yourself combining several LIKE operators in a query.

[prometeus]

Call this piece of PHP with a standart FORM query and your set.

function search_split_terms($terms){
$terms = preg_replace("/\"(.*?)\"/e", "search_transform_term('\$1')", $terms);
$terms = preg_split("/\s+¦,/", $terms);
$out = array();
foreach($terms as $term){
$term = preg_replace("/\{WHITESPACE-([0-9]+)\}/e", "chr(\$1)", $term);
$term = preg_replace("/\{COMMA\}/", ",", $term);
$out[] = $term;
}
return $out;
}
function search_transform_term($term){
$term = preg_replace("/(\s)/e", "'{WHITESPACE-'.ord('\$1').'}'", $term);
$term = preg_replace("/,/", "{COMMA}", $term);
return $term;
}

function search_escape_rlike($string){
return preg_replace("/([.\[\]*^\$])/", '\\\$1', $string);
}

function search_db_escape_terms($terms){
$out = array();
foreach($terms as $term){
$out[] = '[[:<:]]'.AddSlashes(search_escape_rlike($term)).'[[:>:]]';
}
return $out;
}

function search_perform($terms){
$terms = search_split_terms($terms);
$terms_db = search_db_escape_terms($terms);
$terms_rx = search_rx_escape_terms($terms);
$parts = array();
foreach($terms_db as $term_db){
$parts[] = "item RLIKE '$term_db'";
}
$parts = implode(' AND ', $parts);
$sql = "SELECT * FROM <YOUR TABLE> WHERE $parts";
$rows = array();
$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
$row[score] = 0;
foreach($terms_rx as $term_rx){
$row[score] += preg_match_all("/$term_rx/i", $row[content_body], $null);
}
$rows[] = $row;
}
uasort($rows, 'search_sort_results');
return $rows;
}

function search_rx_escape_terms($terms){
$out = array();
foreach($terms as $term){
$out[] = '\b'.preg_quote($term, '/').'\b';
}
return $out;
}

function search_sort_results($a, $b){
$ax = $a[score];
$bx = $b[score];
if ($ax == $bx){ return 0; }
return ($ax > $bx)? -1 : 1;
}

function search_html_escape_terms($terms){
$out = array();
foreach($terms as $term){
if (preg_match("/\s¦,/", $term)){
$temp[] = '"'.HtmlSpecialChars($term).'"';
}else{
$temp[] = HtmlSpecialChars($term);
}
}
return $out;
}

function search_pretty_terms($terms_html){
if (count($terms_html) == 1){
return array_pop($terms_html);
}
$last = array_pop($terms_html);
return implode(', ', $terms_html)." and $last";
}

#
# do the search here...
#

$results = search_perform($HTTP_GET_VARS[query]);
$term_list = search_pretty_terms(search_html_escape_terms(search_split_terms($HTTP_GET_VARS[query])));

foreach($results as $value) {

{display results here..}

}