ha. i've done this based on that link i mentioned at the end of the first post:
php code: function get_search_sql($select_columns, $table, $search_fields, $search_terms, $group_by="", $limit="")
{
$search = array_combine($search_fields, $search_terms);
Â
$count = 0;
foreach($search as $field => $term) {
if (trim($term) == "")
continue;
if (!get_magic_quotes_gpc()) {
$term = addslashes($term);
}
if (!$count) {
$sql_fields = "";
$sql_fields_replace = "";
} else {
$sql_fields.= ",";
$sql_fields_replace.= ",";
}
$sql_fields.= $field;
$terms = explode(" ", $term);
$tmp_fields_replace = "LOWER($field)";
Â
foreach($terms as $term) {
if ($count)
$sql_search.= "OR ";
$term = trim(strtolower($term));
$tmp_fields_replace = "REPLACE($tmp_fields_replace,'$term','')";
$sql_search.= "LOWER($field) LIKE '%$term%' ";
$count++;
}
$sql_fields_replace.= $tmp_fields_replace;
}
if (trim($group_by) != "")
$group_by = "GROUP BY $group_by";
if (trim($limit) != "")
$limit = "LIMIT $limit";
return "SELECT $select_columns, ( LENGTH(CONCAT($sql_fields)) - LENGTH(CONCAT($sql_fields_replace)) ) / LENGTH(CONCAT($sql_fields)) AS relevancy FROM $table WHERE $sql_search $group_by ORDER BY relevancy DESC $limit";
}
which, when called with get_search_sql("id", "table", array("col1", "col2"), array("col1word1 col1word2", "col2word1", "")), results in SQL such like:
sql code: SELECT id, ( LENGTH(CONCAT(col1,col2)) - LENGTH(CONCAT(REPLACE(REPLACE(LOWER(col1),'col1word1',''),'col1word2',''),REPLACE(LOWER(col2),'col2word1',''))) ) / LENGTH(CONCAT(col1,col2)) AS relevancy
FROM TABLE
WHERE LOWER(col1) LIKE '%col1word1%' OR LOWER(col1) LIKE '%col1word2%' OR LOWER(col2) LIKE '%col2word1%'
ORDER BY relevancy DESC
beautiful, right? yours is good but i think the 'relevancy' score of my one should be a bit more accurate (it's based on the number of occurences rather than just a sum of true/falses). no idea about performance as my DB is too small to test it properly. i don't know if i care enough. |
