Re: [PHP] Complicated sql for search query ?

[Marek Kilimajer]

Something like:

select ...., IF(title_keywords like \"%$word1%\" or body_keywords like 
\"%$word1%\", 1, 0) + IF(title_keywords like \"%$word2%\" or 
body_keywords like \"%$word2%\", 1, 0) as relevance ...... ORDER BY 
relevance

I hope you get the point.

Dave Carrera wrote:
Hi List,

I think this a complicated sql query but I am sure one of you gurus will
show me how easy this is.
I am doing a search script an have it finding multiple words and
highlighting those words.
Now my issue is how do I order each row from my result so that if the row
has "word1" and "word2" and "word3" it is at the top of this listing
returned and then rows with "word1" and "word2" and then "word1".
I think it might be something to do with count() somewhere in my sql but I
have not found an answer elsewhere to achieve this.
Here is an example of how I am building the sql in Php:

$find = "word1 word2 word3";

        $far = explode(' ',$find);
        $far = array_unique($far);
        $count = count($far);
       // print_r($far);
        for($kw = 0; $kw <= count($far)-1; $kw++){
        if($kw ==0){
        $queryor .= " title_keywords like \"%$far[$kw]%\" ";
        $queryor .= "or body_keywords like \"%$far[$kw]%\" ";
                 }
        elseif($kw == $count){
        $queryor .= " title_keywords like \"%$far[$kw]%\" ";
        $queryor .= "or body_keywords like \"%$far[$kw]%\" ";
        } else {
        $queryor .= " or title_keywords like \"%$far[$kw]%\" ";
        $queryor .= "or body_keywords like \"%$far[$kw]%\" ";
        }
        }
        //echo $query;
        $sqlor = mysql_query("select * from database where".$queryor." ") or
die(mysql_error());
Any advise or examples are very much appreciated.

Thank you in advance for any help

Dave Carrera
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php