mysql从高到底排序,如何将该数组从高到低排序？

I have made this code which allows a user to type in a search bar a question. This code then takes that question and looks for watching words with questions in my database. It then counts the number of matching words for each question. Once done it then displays the top 4 best matched questions depending on how many words match.

However at the moment it displays these matches from lowest word match to highest word match (low-to-high) and I was it the other way around so that it displays the best match first (high-to-low). How do I do this in this code?

include("config.php");

$search_term = filter_var($_GET["s"], FILTER_SANITIZE_STRING); //User enetered data

$search_term = str_replace ("?", "", $search_term); //remove any question marks from string

$array = explode(" ", $search_term); //Seperate user enterd data

foreach ($array as $key=>$word) {

$array[$key] = " title LIKE '%".$word."%' "; //creates condition for MySQL query

}

$q = "SELECT * FROM posts WHERE " . implode(' OR ', $array); //Query to select data with word matches

$r = mysql_query($q);

$count = 0; //counter to limit results shown

while($row = mysql_fetch_assoc($r)){

$thetitle = $row['title']; //result from query

$thetitle = str_replace ("?", "", $thetitle); //remove any question marks from string

$title_array[] = $thetitle; //creating array for query results

$newarray = explode(" ", $search_term); //Seperate user enterd data again

foreach($title_array as $key => $value) {

$thenewarray = explode(" ", $value); //Seperate each result from query

$wordmatch = array_diff_key($thenewarray, array_flip($newarray));

$result = array_intersect($newarray, $wordmatch);

$matchingwords = count($result); //Count the number of matching words from user entered data and the database query

}

if(mysql_num_rows($r)==0)//no result found{

echo "

No result found!

";

}

else //result found

{

echo "

• ";

$title = $row['title'];

if ($matchingwords >= 4){

?>

'><?php echo $title ?>   No. matching words: <?php echo $matchingwords; ?>

$count++;

if ($count == 5) {break;

}

}else{

}

}

echo "

";

}

?>

解决方案

note this requires Mysql version 4+

A fulltext sql query returns values based on relevance.

Example:

SELECT *, MATCH (title)

AGAINST ('$search_term' IN NATURAL LANGUAGE MODE) AS score

FROM posts;

This is how you would implement it

$q = "SELECT *,MATCH (title) AGAINST ('$search_term' IN BOOLEAN MODE) AS relevancy FROM

posts WHERE MATCH (title) AGAINST ('$search_term' IN BOOLEAN MODE) ORDER BY

relevancy DESC";

This will return the posts in order by relevancy. May need to remove the '' around $search_term but you can figure that out with testing.

Please read up on Fulltext sql queries and the match() function. It is all clearly documented.