how to compare an array to the value of another array in PHP

Question

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

@Dave the array has multiple results with videos name. you can consider it as youtube videos. i have 400+ youtube videos in a DB and want to search some words from videos name. like "how to write code in php" now i want that video names contact maximum words of search term in its name should be displayed on top — saleem, Nov 21 '18 at 10:09
Would be great if you show us a sample in- and output.. by the way Your queries are highly in **danger** of getting sql-injected! See [How can I prevent SQL injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) — B001ᛦ, Nov 21 '18 at 10:09
@B001ᛦ take a look at the question. its updated. don't worry about SQL injection. i will take care about it. its a simple form of code — saleem, Nov 21 '18 at 10:18
_take a look at the question..._ I don't see the array structure but some text — B001ᛦ, Nov 21 '18 at 10:19
@saleem can you explain why this should be different? You are looking for a resulting array, sorted by relevance — Nico Haase, Nov 21 '18 at 12:49
thanks @NicoHaase it is working with some little modifications — saleem, Nov 26 '18 at 11:33

Ivkin Igor · Answer 1 · 2018-11-21T12:30:16.870

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.

sorry i miss-understood Nice Haase, yes it is working for me. thanks for mentioning. have a great day — saleem, Nov 26 '18 at 11:35

how to compare an array to the value of another array in PHP

1 Answers1