0

For example mysql table like

TextColumn                  
------------------------
Full Metal Dog Guard for Passat Estate protecting rear seats and above

User types some text. I want to check if in mysql exists similar text to the typed text

$user_input = 'Full';

query like this

SELECT TextColumn FROM table WHERE MATCH(TextColumn) AGAINST(? IN BOOLEAN MODE) LIMIT 1

$stmt_for_duplicate->execute( array( $user_input ) );
$duplicate_data = $stmt_for_duplicate->fetchAll(PDO::FETCH_ASSOC);

And get

Array
(
    [0] => Array
    (
    [TextColumn] => Full Metal Dog Guard for Passat Estate protecting rear seats and above 
    )

)

So only one word ('Full') is the same as in TextColumn and i get that result is similar

Seems i incorrectly use MATCH AGAINST.

Please, advice how to use correctly? May be I can add some percentage somehow? Or may be exists another simple and fast solution?

Another way but it is not effective (change one letter and get "as if not similar"). So better to use MATCH ... AGAINST but AGAINST must contain some sentence

I do not know how effective is such solution, but here it is (please, advice if can improve something).

So I have user input. I create array from certain number of first words

 $array_from_input = explode( " ", ( substr( (trim($_POST['user_input'])),0,150 ) ) );

Then create query and data

$part_of_sql_check_similar = '';
foreach( $array_from_input as $i_array_from_input => $v_array_from_input ) {

if( $i_array_from_input == 0 ){
$part_of_sql_check_similar .= 'TextColumn LIKE ? ';
}
else{
$part_of_sql_check_similar .= 'AND TextColumn LIKE ? ';
}

$data_check_similar[] = '%'. trim($v_array_from_input). '%';

}

And then select

try {
$stmt_for_duplicate = $db->prepare( '
SELECT 
Id FROM table_name 
WHERE '. 
$part_of_sql_check_similar. 
'LIMIT 1
;' );

$stmt_for_duplicate->execute( $data_check_similar );
$duplicate_data = $stmt_for_duplicate->fetchAll(PDO::FETCH_ASSOC);
}

In such way i check if one particular row contains all the words from user's input. All works, but how about performance? Seems slow?

Andris
  • 1,434
  • 1
  • 19
  • 34
  • Expect for example if 70% of words matches – Andris Jan 11 '15 at 15:16
  • @user2118559 . . . Percentage matches are based on the word count of the *query* not the *text*. So, `'full'` has a 100% match rate. – Gordon Linoff Jan 11 '15 at 15:24
  • So I can not use `MATCH AGAINST` in my situation (if I want to check if at least 70% of words is the same)? – Andris Jan 11 '15 at 15:28
  • You can use a hack :count all the words in a row,use a COUNT(*) when you search for a particular word,divide the COUNT(*) over the number of rows and use that in your condition.But its awful and why do you need 70% anyway? – Mihai Jan 11 '15 at 15:30
  • User posts an ad. I want to check if too similar ad already exists – Andris Jan 11 '15 at 15:31
  • Maybe something like http://stackoverflow.com/questions/5322917/how-to-compute-similarity-between-two-strings-in-mysql Also google for `check text similarity php` lost of nice results – Mihai Jan 11 '15 at 15:38

0 Answers0