php - How to sort the results of this code?

Question

im creating a search feature that will allow a user to type in a question, my code will then match as many words as possible with the questions already in my MySQL database and display the top 5 results depending on the amount of words that are matched in the question.

I use a count() function which counts the number of matching words, however at the moment the results shown are displayed as the first 5 results in the database that have a 50% word match or more. I want the results to be shown as the highest match first and work its way down for every result in the database but only show the top 5. Here is the code I have

<?php
    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
    $search_count = str_word_count($search_term);  //count words of string entered by user
    $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 "<div id='search-status'>No result found!</div>";
        }
        else //result found
        {
            echo "<ul>";
            $title = $row['title'];
            $percentage = '.5'; //percentage to take of search word count
            $percent = $search_count - ($search_count * $percentage); //take percentage off word count
            if ($matchingwords >= $percent){

                ?>
            <li><a href='<?php echo $row['url']; ?>'><?php echo $title ?><i> &nbsp; No. matching words: <?php echo $matchingwords; ?></i></a></li>
            <?php

                $count++;
                if ($count == 5) {break;
                }
            }else{
            }
        }
        echo "</ul>";
    }
?>

The image below shows the what happens when I search "How to make my own website" in the search bar. I already have several questions in the database for testing which are all similar questions and one the last entry is an exact match to the question I asked, but as its currently showing them as the first 5 mathing results, it ignores the full match. Here is the results from that search.

I have added a bit of code which shows how many word matches there are in each question just so you can see it working a bit better. Also its a coincidence that its in ascending order, it is showing the first 5 matching results in the database.

What code do I need to add to this to arrange it so that it shows the closest match from the entire database first then the second best match, third etc...?

Answer 1

Either you use a nested SQL query where you can order by count or load the values to array php array first and then sort the array. Using SQL is very efficient && faster.

Multi Dimension Array Sorting

select column1, column2,..., LENGTH(titlecolumn) - LENGTH(REPLACE(titlecolumn, '$search term', '')) AS nummatchwords from posts where " . implode(' OR ', $array) order by nummatchwords;