Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Feb 2012
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PHP - How to sort the results of this code?

    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 Code:
    <?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($thenewarrayarray_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...?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    I'm not sure if there's an easy way to count the words in a string that match a given value within MySQL itself. I'm sure it can be done, but I'd expect it to be messy.
    PHP wise sorting is easy. PHP will need to be given an array of this data, and easiest to use a usort for it.
    PHP Code:
    $aSearchTerms explode(' '$search_term); // You've sorta done this already, but overwritten it in a foreach.
    $aRecords = array();

    function 
    sortByKeywordsASC(array $a, array $b)
    {
        
    $iResult 0;
        if (isset(
    $a['_keywords'], $b['_keywords']))
        {
            
    $iResult $a['_keywords'] - $b['_keywords'];
        }
        return 
    $iResult;
    }

    function 
    sortByKeywordsDESC(array $a, array $b)
    {
        return 
    sortByKeywordsASC($b$a);
    }

    if (
    mysql_num_rows($r) > 0)
    {
        while (
    $row mysql_fetch_assoc($r))
        {
            
    $thetitle str_replace('?'''$row['title']);
            
    $aTitleParts explode(' '$thetitle);
            
    $aTitleParts array_unique($aTitleParts);
            
    $row['_keywords'] = count(array_intersect($aTitleParts$aSearchTerms));
            
    $aRecords[] = $row;
        }

        
    usort($aRecords'sortByKeywordsASC');
        
    // here you issue a foreach on $aRecords to pull what you want to display.
    }
    else
    {
        
    // no results.

    You should check in the SQL forum as well to see what it would take to do this in SQL instead. You'd be better off pulling only 5 records from your database based on criteria and sorted, than using PHP to pull an undefined number of records and then iterate only the top 5.

    Edit:
    In hindsight, it would actually be better to count the number of keywords in the while instead of within the callback. Otherwise each comparison need to call a count which will grow very quickly in count calls versus one per record in the while.
    Last edited by Fou-Lu; 08-04-2012 at 04:58 PM.

  • #3
    New to the CF scene
    Join Date
    Feb 2012
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    I'm not sure if there's an easy way to count the words in a string that match a given value within MySQL itself. I'm sure it can be done, but I'd expect it to be messy.
    PHP wise sorting is easy. PHP will need to be given an array of this data, and easiest to use a usort for it.
    PHP Code:
    $aSearchTerms explode(' '$search_term); // You've sorta done this already, but overwritten it in a foreach.
    $aRecords = array();

    function 
    sortByKeywordsASC(array $a, array $b)
    {
        
    $iResult 0;
        if (isset(
    $a['_keywords'], $b['_keywords']))
        {
            
    $iResult $a['_keywords'] - $b['_keywords'];
        }
        return 
    $iResult;
    }

    function 
    sortByKeywordsDESC(array $a, array $b)
    {
        return 
    sortByKeywordsASC($b$a);
    }

    if (
    mysql_num_rows($r) > 0)
    {
        while (
    $row mysql_fetch_assoc($r))
        {
            
    $thetitle str_replace('?'''$row['title']);
            
    $aTitleParts explode(' '$thetitle);
            
    $aTitleParts array_unique($aTitleParts);
            
    $row['_keywords'] = count(array_intersect($aTitleParts$aSearchTerms));
            
    $aRecords[] = $row;
        }

        
    usort($aRecords'sortByKeywordsASC');
        
    // here you issue a foreach on $aRecords to pull what you want to display.
    }
    else
    {
        
    // no results.

    You should check in the SQL forum as well to see what it would take to do this in SQL instead. You'd be better off pulling only 5 records from your database based on criteria and sorted, than using PHP to pull an undefined number of records and then iterate only the top 5.

    Edit:
    In hindsight, it would actually be better to count the number of keywords in the while instead of within the callback. Otherwise each comparison need to call a count which will grow very quickly in count calls versus one per record in the while.
    Hi, thanks for sending me this but ill be honest and say im not sure where to put this and what to replace with this. I'll keep trying but there is always an error wherever I put it.
    But I will keep trying
    Thanks

  • #4
    Senior Coder
    Join Date
    Apr 2011
    Location
    London, England
    Posts
    2,120
    Thanks
    15
    Thanked 354 Times in 353 Posts
    It would be worth investing FULLTEXT searches; this requires the table(s) to be:

    Code:
    ENGINE=MyISAM
    which may already be the case in Windows. phpMyAdmin can confirm this.

    Perhaps not of immediate assistance though but something for the future. It will be easier to count the matches, and perform partial/fuzzy matching.

    It will Edit:CAN be faster as well - once the appropriate indexes have been created.
    Last edited by AndrewGSW; 08-04-2012 at 08:11 PM.
    "I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
    Validate your HTML and CSS


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •