SQL For 'LIKE' based search relevance

1

I’m writing a search script into a product inventory list I’ve got. I’ve got the search working great, but I want order the results by search relevance, as opposed to by a given column.

Anybody got a technique/algorithm to use here? (I’m not interested in a full search engine solution, just a method/class/advice/etc.)

Here’s my code (albeit it’s very messy/sloppy…it’s a few minutes worth of coding on a bad day and a tight deadline.


    function search_products($string = NULL, $column = 'Description', $rows = '20') {
        if(isset($string) && is_string($string)) {
            $args = explode(' ', $string);
            $arg = " WHERE";
            $i = 1;
            foreach($args as $item) {
                $arg .= ($i == 1) ? "" : " OR";
                $arg .= " `$column` LIKE '%$item%'";
                $i++;
            }
            $query = "SELECT * FROM `$this->prod_table`";
            $query .= ($arg == NULL) ? '' : $arg;
            $query .= ($rows == FALSE) ? '' : ' LIMIT '.$rows;
            $query .= ";";
            return $this->wpdb->get_results($query, OBJECT);
        } else {
            return false;
        }
    }
2

Using LIKE is slower than using MATCH(someCol) AGAINST (‘str*’ IN BOOLEAN MODE) and then you can order them by search relevance. I’m sure there’s some way to do similar thing for LIKE, but if I were you, I’d use MATCH … AGAINST …

Please see MySQL manual for MATCH … AGAINST …

Hope this helps. :slight_smile:

3

Good deal, thanks :slight_smile: I’d considered MATCH, but in my haste chose LIKE instead.

I’ll update the code in a few moments.