CODEWITHSUNDEEP
phpmysql
Tom Senner
Tom Senner

Reputation: 111

mysql php query find longest matching parts of input

I need to get always the longest possible matches from my database.

MyDatabase ( id / phrase )
1: 'I'
2: 'go'
3: 'I go to school'
4: 'with'
5: 'with my dog named Gogo'
6: 'day'
7: 'every day'
8: 'except'
9: 'except weekends'
10:'weekends'

User Input = 'AwordThatsNOTinDB I go to school AwordThatsNOTinDB with my dog named Gogo every day except weekends AwordThatsNOTinDB'

So when the user inputs the above I'd like to get the following IDs in this order from the database as an array:
array( "Not-Found", 3, "Not-Found", 5, 7, 9, "Not-Found");

Upvotes: 1

Views: 154

Answers (2)

CodeBird
CodeBird

Reputation: 3858

I would use this solution:

1- select all strings from the database order by length of the string.

2- loop through my db strings and replace them in the user input. if the string changes I update the oringal string, and I add the id of the one I am in to the found array.

 $found_phrase=array();
 $string=strtolower('AwordThatsNOTinDB I go to school AwordThatsNOTinDB with my dog named Gogo every day except weekends AwordThatsNOTinDB');  

 // LIMIT DB-SEARCH  
 $string=preg_replace('!\s+!', ' ', $string); // replace multiple spaces with one space
 $where=explode(" ",$string);
 foreach($where as $whereVal){
   $whereQuery=$whereQuery." phrase LIKE '%".$whereVal."%' OR";  
 }
 $whereQuery=rtrim($whereQuery,"OR"); // delete last OR
 $whereQuery="WHERE ".$whereQuery." ";    

 $query=$conn->prepare("SELECT id, phrase FROM table $whereQuery ORDER BY length(phrase) DESC");
 $query->execute();
 while($array=$query->fetch(PDO::FETCH_ASSOC)){
     $new_string=str_replace(strtolower($array['phrase']), "[{$array['id']}]", $string);
     if($new_string!=$string){
         $string=$new_string;
     }
 }
 $string=preg_replace('/([A-Z0-9,.]){1,} /i', ' Not-Found ', $string);
 $string=preg_replace('/\[([0-9])*\]/i', str_replace(array('[',']'), '',"$1"), $string);
 $string_array=explode(' ', $string);
 print_r($string_array);

Upvotes: 1

Bhadra
Bhadra

Reputation: 2104

Please get this as an array from database 

$phrases=array(
    '1'=> 'I',
    '2'=> 'go',
    '3'=> 'I go to school',
    '4'=> 'with',
    '5'=> 'with my dog named Gogo',
    '6'=> 'day',
    '7'=> 'every day',
    '8'=> 'except',
    '9'=> 'except weekends',
    '10'=>'weekends'
);

$user_input = 'AwordThatsNOTinDB I go to school AwordThatsNOTinDB with my dog named Gogo every day except weekends AwordThatsNOTinDB';
$matches=array();

foreach($phrases as $id=>$phrase){
    $x=false;
    if(strpos($user_input,$phrase)!==false){


    if(!empty($matches)){
        foreach($matches as $key=>$match){
            if((strpos($phrase,$match)!==false) && $phrase !== $match)
                unset($matches[$key]);

            if((strpos($match,$phrase)!==false)){
                 $x = true;
                break;
            }

        }
    }
    if(!$x)
        $matches[$id]=$phrase;


}
}
var_dump($matches);
?>

Upvotes: 0

Related Questions