Sorting and substr_count

Im trying to make a simple searc开发者_StackOverflowh function for a small site, and have the most relevant items at the top

$q = "SELECT * FROM pages as p WHERE p.content LIKE '%$searchparam%' OR p.title LIKE '%$searchparam%' LIMIT 500";       
$r = @mysqli_query ($dbc, $q); // Run the query.
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {

    $count = substr_count($row['content'], "$searchparam");

I'm guessing I need to sort $row with the value from $count, but I'm not sure how to do this. Can anyone help me out with the syntax?

A better solution would be use use MySq'ls full text search capabilities, as the results are returned ranked and it is probably easier and more robust than trying to write it yourself. You could also look into other search engines like Sphinx.

However, if you wanted to continue with your method, you should do something like:

$results = array();
while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
    $count = substr_count($row['content'], "$searchparam");
    //add the details you want to the array. You could also just add count
    // to $row and add $row to $results
    $results[] = array(
        'count' => $count,
        'resultid' => $row['id']
    );
}

//custom sort function that uses the count to order results    
function cmp($a, $b){
    if ($a['count'] == $b['count]) {
        return 0;
    }
    return ($a > $b) ? -1 : 1;
}

//sort the array using the sort function
usort($results, 'cmp');