>"Dave Carrera" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >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]%\" "; > } > } > file://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
Hi Dave, take a look at MySQL's fulltext search functions. I think the results will be ordered by relevance: http://www.mysql.de/doc/en/Fulltext_Search.html If this doesn't work for you here's my workaround suggestion: I would do a loop for every search word within every row of your result and add it to an multi-dimensional array with the count of the found words as the index: // $count is set above in your script $result = array(); while($row = mysql_fetch_assoc($sqlor)) { $found = 0; for ($i = 0; $i < $count; $i++) { // increment found if the word is found in one of the columns if (stristr($row['title_keywords'], $far[$i]) || stristr($row['body_keywords'], $far[$i])) { $found++; } } // add row values to result array $array[$found][] = $row; } This should give you something like this where the first dimension is the count fo the found words: Array { [1] => Array { [0] => Array { title_keywords = 'xyz', body_keywords 'whatever' }, [1] => Array { title_keywords = 'xyz', body_keywords 'whatever' } }, [3] => Array { [0] => Array { title_keywords = 'xyz', body_keywords 'whatever' } } } Then you could loop through the array again starting with the highest index (highest number of found words) and output your resulst. Have not tested the code. Maybe you could try it out and do var_dump($result) to check if the result is what you want. Regards, Torsten -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
