too long

Question

This question already has an answer here:

• MySQL match() against() - order by relevance and column? 4 answers

i have an array with the result of the search term. please take a look at blow code

$keyword = trim($_POST['keyword']);

$keyword = preg_replace('!\s+!', ' ', $keyword);
$words = explode(' ', $keyword);
$keyword = str_replace(" ","+",$keyword);
$query = "SELECT * FROM videos WHERE MATCH (video_name) AGAINST ('$keyword' IN BOOLEAN MODE)";

$q_result = mysql_query($query) or die (mysql_error());
if (mysql_num_rows ($q_result) != 0) {
    while ($row = mysql_fetch_assoc($q_result)) {
        $id = $row['id'];
        $video_name = $row['video_name'];
        $result[]= "$id $video_name";
    }
}
foreach($result as $res){
    echo "$res <br>";
}
}

it is working perfectly. but it is displaying the result in ascending order to id. what I want is if a row contains all or maximum keywords it should be the first element of the array.

let me explain further. suppose we have 3 rows id video_name 1 how to create php code. 2 php language scope in future. 3 the future of php.

if a user searches "PHP language scope in future" the array should be arranged by id 2,3,1

here is the output. as you can see query I have searched 3 words

hope its clear

</div>

Answer 1

Frankly speaking this question is very similar to yours one as Nico Haase mentioned.

In your case you could use the following query:

SELECT videos.*,
       MATCH (video_name) AGAINST ('$keyword' IN BOOLEAN MODE) AS relevance
FROM videos 
WHERE MATCH (video_name) AGAINST ('$keyword' IN BOOLEAN MODE)
ORDER BY relevance DESC

Here you are going to use calculated value "relevance" that performs as "weight" of every row to order by it. The value will be in the form of number determining the weight. This weight will be higher when most of keywords are here and lower when lesser amount of keywords is presented.

UPD: If you prefer to do a sort on the side of PHP not the side of database engine then you can check the function usort. It allows to define your own logic to sort an array of any structure. Personally me I prefer database side sorting in such a case.