0

Possible Duplicate:
How to sort the results of this code?

Im making a search feature which allows a user to search a question and it will show the top 5 best matching results by counting the number of matching words in the question. Basically I want the order to show the best match first which would be the question with the highest amount of matching words.
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){
        $finalarray = array($title => $matchingwords);

        foreach( $finalarray as $thetitle=>$countmatch ){


?>
    <li><a href="#"><?php echo $thetitle ?><i> &nbsp; <br />No. of matching words: <?php echo $countmatch; ?></i></a></li>
<?php


                }
                $count++;
        if ($count == 5) {break;
        }
                }else{



            }
    }
echo "</ul>";
}

?>
When you search something it will show something like this.
enter image description here
Iv put the number of matching words under each of the questions however they are not in order. It just shows the first 5 questions from the database that have a 50% word match. I want it to show the top 5 with the most amount of matching words.
What code would I need to add and where would I put it in order to do this?
Thanks

Community
  • 1
  • 1
Ben
  • 335
  • 3
  • 12
  • This is the [third](http://stackoverflow.com/questions/11806840/how-to-order-this-array-to-high-to-low) [time](http://stackoverflow.com/questions/11809077/php-how-to-sort-the-results-of-this-code) you have asked this question... At first glance, you should first loop over all the result rows and build an array recording each row's record and number of matches in that row, sort *that* array as you see fit, and display the results. And for the love of all that's beautiful, please indent your code consistently. – DCoder Aug 05 '12 at 13:59
  • Its the third time because iv still not got it sorted. Iv been trying for 4 days now and still havnt got it done and im not going to give up until its done. Iv tried what you suggested, but I couldnt get that working, this is the closest iv got it to working, and all I need is to get it sorted and thats it. As for the indenting I will get that done eventually, but iv been inserting soo much code and deleting loads of code that it just always gets messed up – Ben Aug 05 '12 at 14:13

2 Answers2

1

Here's my take on your problem. A lot of things have been changed:

  • mysql_ functions replaced with PDO
  • usage of anonymous functions means PHP 5.3 is required
  • main logic has been restructured (it's really hard to follow your result processing, so I might be missing something you need, for example the point of that $percentage)

I realize this might look complicated, but I think that the sooner you learn modern practices (PDO, anonymous functions), the better off you will be.

<?php
/**
 * @param string $search_term word or space-separated list of words to search for
 * @param int $count
 * @return stdClass[] array of matching row objects
 */
function find_matches($search_term, $count = 5) {
    $search_term = str_replace("?", "", $search_term);
    $search_term = trim($search_term);
    if(!strlen($search_term)) {
        return array();
    }
    $search_terms = explode(" ", $search_term);

    // build query with bind variables to avoid sql injection
    $params = array();
    $clauses = array();

    foreach ($search_terms as $key => $word) {
        $ident = ":choice" . intval($key);
        $clause = "`title` LIKE {$ident}";
        $clauses []= $clause;
        $params [$ident] = '%' . $word . '%';
    }

    // execute query
    $pdo = new PDO('connection_string');
    $q = "SELECT * FROM `posts` WHERE  " . implode(' OR ', $clauses);
    $query = $pdo->prepare($q);
    $query->execute($params);
    $rows = $query->fetchAll(PDO::FETCH_OBJ);

    // for each row, count matches
    foreach($rows as $row) {
        $the_title = $row->title;
        $the_title = str_replace("?", "", $the_title);

        $title_terms = explode(" ", $the_title);
        $result = array_intersect($search_terms, $title_terms);
        $row->matchcount = count($result);
    }

    // sort all rows by match count descending, rows with more matches come first
    usort($rows, function($row1, $row2) {
        return - ($row1->matchcount - $row2->matchcount);
    });

    return array_slice($rows, 0, $count);
}
?>

<?php
    $search_term = filter_var($_GET["s"], FILTER_SANITIZE_STRING);
    $best_matches = find_matches($search_term, 5);
?>

<?php if(count($best_matches)): ?>
    <ul>
    <?php foreach($best_matches as $match): ?>
        <li><a href="#"><?php echo htmlspecialchars($match->title); ?><i> &nbsp; <br/>No. of matching words: <?php echo $match->matchcount; ?></i></a></li>
    <?php endforeach; ?>
    </ul>
<?php else: ?>
    <div id="search-status">No result found!</div>
<?php endif; ?>
DCoder
  • 12,962
  • 4
  • 40
  • 62
  • Hi, thanks for your reply, Iv tried this code but im getting a fatal error with the line with this on it "$pdo = new PDO('connection_string');", the error says "Uncaught exception 'PDOException' with message 'invalid data source name'" – Ben Aug 05 '12 at 16:24
  • Yeah, you need to read up on [how PDO connections work](http://us.php.net/manual/en/pdo.construct.php#refsect1-pdo.construct-examples). – DCoder Aug 05 '12 at 16:27
  • Ye sorry, I just figured that out haha, sorry. And thank you soooooo much, it works brilliantly. Thanks a lot :D – Ben Aug 05 '12 at 16:29
0

Try adding asort($finalarray); after your $finalarray = array($title => $matchingwords); declaration:

...

if ($matchingwords >= $percent){
    $finalarray = array($title => $matchingwords);
    asort($finalarray);

....

It should sort your array Ascending by the Values

Wes Foster
  • 8,770
  • 5
  • 42
  • 62