CODEWITHSUNDEEP
phpmysqlsql
Somk
Somk

Reputation: 12047

MySQL Search by Relevance of all fields

I am sure this is possible but I think it maybe just very complex to write. I want to search every field by:

='SearchTerm' then Like %SearchTerm then like SearchTerm% and finally like %SearchTerm%. I want to run this on every field in my table which there is around 30 or 40. Is there an easy way to run this over multiple fields or will I have to declare every single one?

I think I have seen a query before where different matches between %query %query% etc are ranked by assigning an integer value and then ordering by this. Would that be possible on a query like this?

Any advice and help in the right direction is much appreciated.

Upvotes: 0

Views: 137

Answers (3)

Ned
Ned

Reputation: 246

You should really have some sort of id to select the rows in your table.

You should have put a column with 

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

Then you could use 

SELECT * FROM table WHERE column1 LIKE "%SearchTerm%" AND id BETWEEN 1 AND 40

Upvotes: 0

Tin Tran
Tin Tran

Reputation: 6202

I do something very similar to what you're describing (in php and mysql)

Here's my code:

$search = trim($_GET["search"]);
$searches = explode(" ",$search);
$sql = "SELECT *,wordmatch+descmatch+usagematch+bymatch as `match` FROM (SELECT id,word,LEFT(description,100)as description, 
         IFNULL((SELECT sum(vote)
                 FROM vote v
                 WHERE v.definition_id = d.id),0) as votecount,
            ";
$sqlword = "";
$sqldesc = "";
$sqlusage = "";
$sqlby = "";
foreach ($searches as $value) {
    $value = mysqli_real_escape_string($con,$value);
       $sqlword = $sqlword . "+ IFNULL(ROUND((LENGTH(word) - LENGTH(REPLACE(UPPER(word), UPPER('$value'), '')))/LENGTH('$value')),0)";
       $sqldesc = $sqldesc . "+ IFNULL(ROUND((LENGTH(description) - LENGTH(REPLACE(UPPER(description), UPPER('$value'), '')))/LENGTH('$value')),0)";
     $sqlusage = $sqlusage . "+ IFNULL(ROUND((LENGTH(`usage`) - LENGTH(REPLACE(UPPER(`usage`), UPPER('$value'), '')))/LENGTH('$value')),0)";
       $sqlby = $sqlby     . "+ IFNULL(ROUND((LENGTH(`by`) - LENGTH(REPLACE(UPPER(`by`), UPPER('$value'), '')))/LENGTH('$value')),0)";
}     
$sql = $sql . $sqlword ." as wordmatch,"
            . $sqldesc ." as descmatch,"
            . $sqlusage ." as usagematch,"
            . $sqlby ." as bymatch 
        FROM definition d
        HAVING (wordmatch > 0 OR descmatch > 0 OR usagematch > 0 OR bymatch > 0)
        ORDER BY 
        wordmatch DESC,
        descmatch DESC,
        usagematch DESC,
        bymatch DESC,
        votecount DESC)T1";
$queries[] = $sql;
$result = mysqli_query($con,$sql);

You can see this at work http://unurbandictionary.comule.com/view_search.php?search=George+Miley+Cyrus this is when I search for "George Miley Cyrus"
What it does is it explodes the search string to find each word and returns the number of occurences of each word in each of my column, and then i do an ORDER BY to have relevance (priority) to come back first. So in my case word field has the highest relevance, then description field, then usage field, then by field.
Before this version of my code I was using LIKE but it didn't give me a count of occurences, since I want the row with the most occurences of my search word to return first before other rows.

Upvotes: 0

Brian H.
Brian H.

Reputation: 505

You should use fulltext indexing on the fields you want searched and use MATCH AGAINST instead of LIKE %%. It's much faster and returns results based on relevancy. More info here:

Mysql match...against vs. simple like "%term%"

Upvotes: 1

Related Questions