search mysql column with similar_text() matching percentage

Welcome to EE, chrisvee.  Here are some general guidelines to make your use of this site more enjoyable and productive for you...

At EE, the experts exchange answers and advice for points.  If you look at the questions awaiting answers in this zone, you will see a lot of 500 point questions.  Your question is competing for the experts' attention among those high-point questions.  So as a matter of simple economics you might be able to envision which questions will get the experts' attention first.   Just a thought.

We are experts, but not mind readers.  Inquiries that are broad, vague and hypothetical may not get answers that are as succinct and effective as inquiries that have actual URLs, complete code examples, and clearly expressed questions.  "It doesn't work" is not an error message.  Whenever possible, please provide the inputs and tell us what you want for the outputs.  An incredibly important concept is the SSCCE; please read the online page and embrace the SSCCE strategy.  If you do not have the SSCCE, stop what you're doing and create one, and post it with your question.  And please accept that sometimes the right answer is, "Don't do that -- it doesn't work that way."

If you want us to be able to share working code, we need you to show us where you have put your test data.  If you have no test data, please create some.  We do not want you to post "live" passwords and such.  Instead, please set up a testbed and show us the links to that, instead of the live data.

We answer questions and provide teaching examples, but we cannot build applications for you.  If you do not understand the basics of computer science and the programming languages involved in your applications, you might be better off to hire a developer.  Often a great deal of trial and error, plus a depth of knowledge and background information is necessary to get a piece of an application working.  The experts will try to help, but sometimes the only reasonable answer is, "Please read the fine manuals" or "Be respectful of your time; don't take a year learning information technology -- hire a professional developer and you'll get good results in a couple of weeks."

All of us who have been at EE for a while have seen questions like, "How do I do 'X' in 'Y' language, and by the way, I do not know anything about 'Y' language."  For some reason we never see anyone ask, "I want to play a piano sonata, and by the way, I have never taken piano lessons."  It is hardly a sin if you do not know a particular programming language -- I do not know most of them -- but it is not reasonable to expect that you will learn a programming language by asking questions in an online forum, any more than you could learn to play the piano by asking questions in an online forum.  Instead your best question might be, "What are good learning resources to get a foundation in 'Y' language?"  We are glad to help with that.

Here are some good learning resources for PHP.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

Best regards, ~Ray

hi ray, i am just asking a sample of code to achieve % matching search in mysql column. myself php developer and i can build php web applications.

The only thing I see wrong with the code snippet is that the last line should not be ?> (never use the close PHP tag if you can avoid it).  Instead it should be } to close the control structure.

i want to output only the value from mysql column which is 90% similar to php variable.

 similar_ text()  between _GET variable and 90% similar string in mysql column.

waiting for the correct code?

Please review the post here.
https://www.experts-exchange.com/questions/28157402/search-mysql-column-with-similar-text-matching-percentage.html?anchorAnswerId=39247440#a39247440

See especially the part about the SSCCE.  Waiting for your test data.

You could do it like this as well - eliminating the superfluous second loop

<?php
$x = $_GET[x];
$con=mysqli_connect("example.com","peter","abc123","my_db");
$res =  mysqli_query($con,"SELECT * FROM text");
while ($row = mysql_fetch_assoc($res)) {
  if (similar_text($x, $y, $percent) > 90) {
    echo $row['text'] . "<br/>";
    // Save only strings > 90% here $string[] = $row['text']
  }
   // Save all strings here $string[] = $row['text'];
}
?>

Select allOpen in new window

Academy Engraved LETAgency FBAharoniAlgerianAndalusAngsana NewAngsanaUPCAparajitaArabic TypesettingArialArial BlackArial NarrowArial Rounded MT BoldArial Unicode MSBaskerville Old FaceBatangBatangCheBauhaus 93Bell MTBerlin Sans FBBerlin Sans FB DemiBernard MT CondensedBlackadder ITCBlackletter686 BTBodoni MTBodoni MT BlackBodoni MT CondensedBodoni MT Poster CompressedBook AntiquaBookman Old StyleBookshelf Symbol 7Bradley Hand ITCBritannic BoldBroadwayBroadway BTBrowallia NewBrowalliaUPCBrush Script MTCalibriCalifornian FBCalisto MTCalligraph421 BTCambriaCambria MathCandaraCastellarCataneo BTCentaurCenturyCentury GothicCentury SchoolbookChillerColonna MTComic Sans MSConsolasConstantiaCooper BlackCopperplate Gothic BoldCopperplate Gothic LightCorbelCordia NewCordiaUPCCourierCourier NewCurlz MTDFKai-SBDaunPenhDavidDilleniaUPCDokChampaDotumDotumCheEbrimaEdwardian Script ITCElephantEngravers MTEras Bold ITCEras Demi ITCEras Light ITCEras Medium ITCEstrangelo EdessaEucrosiaUPCEuphemiaFangSongFelix TitlingFixedsysFootlight MT LightForteFrankRuehlFranklin Gothic BookFranklin Gothic DemiFranklin Gothic Demi CondFranklin Gothic HeavyFranklin Gothic MediumFranklin Gothic Medium CondFreesiaUPCFreestyle ScriptFrench Script MTGabriolaGaramondGautamiGeorgiaGigiGill Sans MTGill Sans MT CondensedGill Sans MT Ext Condensed BoldGill Sans Ultra BoldGill Sans Ultra Bold CondensedGishaGloucester MT Extra CondensedGoudy Old StyleGoudy StoutGulimGulimCheGungsuhGungsuhCheHaettenschweilerHarlow Solid ItalicHarringtonHigh Tower TextHighlight LETHolidayPi BTImpactImprint MT ShadowInformal RomanIrisUPCIskoola PotaJasmineUPCJohn Handy LETJokermanJokerman LETJuice ITCKaiTiKalingaKartikaKhmer UIKodchiangUPCKokilaKristen ITCKunstler ScriptLa Bamba LETLao UILathaLeelawadeeLevenim MTLilyUPCLucida BrightLucida CalligraphyLucida ConsoleLucida FaxLucida HandwritingLucida SansLucida Sans TypewriterLucida Sans UnicodeMS GothicMS MinchoMS OutlookMS PGothicMS PMinchoMS Reference Sans SerifMS Reference SpecialtyMS Sans SerifMS SerifMS UI GothicMT ExtraMV BoliMagnetoMaiandra GDMalgun GothicMangalMarlettMatura MT Script CapitalsMeiryoMeiryo UIMekanik LETMicrosoft HimalayaMicrosoft JhengHeiMicrosoft New Tai LueMicrosoft PhagsPaMicrosoft Sans SerifMicrosoft Tai LeMicrosoft UighurMicrosoft YaHeiMicrosoft Yi BaitiMilano LETMingLiUMingLiU-ExtBMingLiU_HKSCSMingLiU_HKSCS-ExtBMiriamMiriam FixedMisterEarl BTMistralModernModern No. 20Mongolian BaitiMonotype CorsivaMoolBoranNSimSunNarkisimNiagara EngravedNiagara SolidNyalaOCR A ExtendedOdessa LETOld English Text MTOldDreadfulNo7 BTOne Stroke Script LETOnyxOrange LETPMingLiUPMingLiU-ExtBPalace Script MTPalatino LinotypePapyrusParchmentParkAvenue BTPerpetuaPerpetua Titling MTPlantagenet CherokeePlaybillPoor RichardPristinaPump Demi Bold LETQuixley LETRaaviRage ItalicRage Italic LETRavieRockwellRockwell CondensedRockwell Extra BoldRodRomanRuach LETSakkal MajallaScriptScript MT BoldScruff LETSegoe PrintSegoe ScriptSegoe UISegoe UI LightSegoe UI SemiboldSegoe UI SymbolShonar BanglaShowcard GothicShrutiSimHeiSimSunSimSun-ExtBSimplified ArabicSimplified Arabic FixedSmall FontsSmudger LETSnap ITCSquare721 BTStaccato222 BTStencilSylfaenSymbolSystemTahomaTempus Sans ITCTerminalTimes New RomanTiranti Solid LETTraditional ArabicTrebuchet MSTungaTw Cen MTTw Cen MT CondensedTw Cen MT Condensed Extra BoldUniversity Roman LETUtsaahVaniVerdanaVictorian LETVijayaViner Hand ITCVivaldiVladimir ScriptVrindaWebdingsWestwood LETWide LatinWingdingsWingdings 2Wingdings 3

Select allOpen in new window

html page collects fonts like above through javascript and sends it to php page. fonts are different for different browsers.but those are almost same in all browsers. we store fonts  mysql column.

 I want to find  similar fonts string($_GET)  with already stored fonts in database.

Normally WHERE clause is used to find exact match. But here in this case, return only above 90% matching.

julionH  what is   $y ?

You'd have to tokenize everything (meaning break things down to individual words), but you might be able to use SOUNDEX()

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_soundex

To find similar matches.  There's nothing that will easily give you a "90%" match. (Genetic pattern matching algorithms might let you specify a '.9' match, but there isn't anything that I know of in MySQL or PHP besides SOUNDEX() that can do "close" matches).

The test data looks a little garbled, but this will work for an example.  You can adapt it to your own data base.  Moving parts are at the end.
http://www.laprbass.com/RAY_temp_chrisvee.php?q=wingding
http://www.laprbass.com/RAY_temp_chrisvee.php?q=wing

<?php // RAY_temp_chrisvee.php
error_reporting(E_ALL);

// USE AN ARRAY TO SIMULATE A DATA BASE TABLE
$dat = array
( 'Academy'
, 'Engraved'
, 'LETAgency'
, 'FBAharoniAlgerianAndalusAngsana'
, 'NewAngsanaUPCAparajitaArabic'
, 'TypesettingArialArial'
, 'BlackArial'
, 'NarrowArial'
, 'Rounded'
, 'MT'
, 'BoldArial'
, 'Unicode'
, 'MSBaskerville'
, 'Old'
, 'FaceBatangBatangCheBauhaus'
, '93Bell'
, 'MTBerlin'
, 'Sans'
, 'FBBerlin'
, 'Sans'
, 'FB'
, 'DemiBernard'
, 'MT'
, 'CondensedBlackadder'
, 'ITCBlackletter686'
, 'BTBodoni'
, 'MTBodoni'
, 'MT'
, 'BlackBodoni'
, 'MT'
, 'CondensedBodoni'
, 'MT'
, 'Poster'
, 'CompressedBook'
, 'AntiquaBookman'
, 'Old'
, 'StyleBookshelf'
, 'Symbol'
, '7Bradley'
, 'Hand'
, 'ITCBritannic'
, 'BoldBroadwayBroadway'
, 'BTBrowallia'
, 'NewBrowalliaUPCBrush'
, 'Script'
, 'MTCalibriCalifornian'
, 'FBCalisto'
, 'MTCalligraph421'
, 'BTCambriaCambria'
, 'MathCandaraCastellarCataneo'
, 'BTCentaurCenturyCentury'
, 'GothicCentury'
, 'SchoolbookChillerColonna'
, 'MTComic'
, 'Sans'
, 'MSConsolasConstantiaCooper'
, 'BlackCopperplate'
, 'Gothic'
, 'BoldCopperplate'
, 'Gothic'
, 'LightCorbelCordia'
, 'NewCordiaUPCCourierCourier'
, 'NewCurlz'
, 'MTDFKai-SBDaunPenhDavidDilleniaUPCDokChampaDotumDotumCheEbrimaEdwardian'
, 'Script'
, 'ITCElephantEngravers'
, 'MTEras'
, 'Bold'
, 'ITCEras'
, 'Demi'
, 'ITCEras'
, 'Light'
, 'ITCEras'
, 'Medium'
, 'ITCEstrangelo'
, 'EdessaEucrosiaUPCEuphemiaFangSongFelix'
, 'TitlingFixedsysFootlight'
, 'MT'
, 'LightForteFrankRuehlFranklin'
, 'Gothic'
, 'BookFranklin'
, 'Gothic'
, 'DemiFranklin'
, 'Gothic'
, 'Demi'
, 'CondFranklin'
, 'Gothic'
, 'HeavyFranklin'
, 'Gothic'
, 'MediumFranklin'
, 'Gothic'
, 'Medium'
, 'CondFreesiaUPCFreestyle'
, 'ScriptFrench'
, 'Script'
, 'MTGabriolaGaramondGautamiGeorgiaGigiGill'
, 'Sans'
, 'MTGill'
, 'Sans'
, 'MT'
, 'CondensedGill'
, 'Sans'
, 'MT'
, 'Ext'
, 'Condensed'
, 'BoldGill'
, 'Sans'
, 'Ultra'
, 'BoldGill'
, 'Sans'
, 'Ultra'
, 'Bold'
, 'CondensedGishaGloucester'
, 'MT'
, 'Extra'
, 'CondensedGoudy'
, 'Old'
, 'StyleGoudy'
, 'StoutGulimGulimCheGungsuhGungsuhCheHaettenschweilerHarlow'
, 'Solid'
, 'ItalicHarringtonHigh'
, 'Tower'
, 'TextHighlight'
, 'LETHolidayPi'
, 'BTImpactImprint'
, 'MT'
, 'ShadowInformal'
, 'RomanIrisUPCIskoola'
, 'PotaJasmineUPCJohn'
, 'Handy'
, 'LETJokermanJokerman'
, 'LETJuice'
, 'ITCKaiTiKalingaKartikaKhmer'
, 'UIKodchiangUPCKokilaKristen'
, 'ITCKunstler'
, 'ScriptLa'
, 'Bamba'
, 'LETLao'
, 'UILathaLeelawadeeLevenim'
, 'MTLilyUPCLucida'
, 'BrightLucida'
, 'CalligraphyLucida'
, 'ConsoleLucida'
, 'FaxLucida'
, 'HandwritingLucida'
, 'SansLucida'
, 'Sans'
, 'TypewriterLucida'
, 'Sans'
, 'UnicodeMS'
, 'GothicMS'
, 'MinchoMS'
, 'OutlookMS'
, 'PGothicMS'
, 'PMinchoMS'
, 'Reference'
, 'Sans'
, 'SerifMS'
, 'Reference'
, 'SpecialtyMS'
, 'Sans'
, 'SerifMS'
, 'SerifMS'
, 'UI'
, 'GothicMT'
, 'ExtraMV'
, 'BoliMagnetoMaiandra'
, 'GDMalgun'
, 'GothicMangalMarlettMatura'
, 'MT'
, 'Script'
, 'CapitalsMeiryoMeiryo'
, 'UIMekanik'
, 'LETMicrosoft'
, 'HimalayaMicrosoft'
, 'JhengHeiMicrosoft'
, 'New'
, 'Tai'
, 'LueMicrosoft'
, 'PhagsPaMicrosoft'
, 'Sans'
, 'SerifMicrosoft'
, 'Tai'
, 'LeMicrosoft'
, 'UighurMicrosoft'
, 'YaHeiMicrosoft'
, 'Yi'
, 'BaitiMilano'
, 'LETMingLiUMingLiU-ExtBMingLiU_HKSCSMingLiU_HKSCS-ExtBMiriamMiriam'
, 'FixedMisterEarl'
, 'BTMistralModernModern'
, 'No.'
, '20Mongolian'
, 'BaitiMonotype'
, 'CorsivaMoolBoranNSimSunNarkisimNiagara'
, 'EngravedNiagara'
, 'SolidNyalaOCR'
, 'A'
, 'ExtendedOdessa'
, 'LETOld'
, 'English'
, 'Text'
, 'MTOldDreadfulNo7'
, 'BTOne'
, 'Stroke'
, 'Script'
, 'LETOnyxOrange'
, 'LETPMingLiUPMingLiU-ExtBPalace'
, 'Script'
, 'MTPalatino'
, 'LinotypePapyrusParchmentParkAvenue'
, 'BTPerpetuaPerpetua'
, 'Titling'
, 'MTPlantagenet'
, 'CherokeePlaybillPoor'
, 'RichardPristinaPump'
, 'Demi'
, 'Bold'
, 'LETQuixley'
, 'LETRaaviRage'
, 'ItalicRage'
, 'Italic'
, 'LETRavieRockwellRockwell'
, 'CondensedRockwell'
, 'Extra'
, 'BoldRodRomanRuach'
, 'LETSakkal'
, 'MajallaScriptScript'
, 'MT'
, 'BoldScruff'
, 'LETSegoe'
, 'PrintSegoe'
, 'ScriptSegoe'
, 'UISegoe'
, 'UI'
, 'LightSegoe'
, 'UI'
, 'SemiboldSegoe'
, 'UI'
, 'SymbolShonar'
, 'BanglaShowcard'
, 'GothicShrutiSimHeiSimSunSimSun-ExtBSimplified'
, 'ArabicSimplified'
, 'Arabic'
, 'FixedSmall'
, 'FontsSmudger'
, 'LETSnap'
, 'ITCSquare721'
, 'BTStaccato222'
, 'BTStencilSylfaenSymbolSystemTahomaTempus'
, 'Sans'
, 'ITCTerminalTimes'
, 'New'
, 'RomanTiranti'
, 'Solid'
, 'LETTraditional'
, 'ArabicTrebuchet'
, 'MSTungaTw'
, 'Cen'
, 'MTTw'
, 'Cen'
, 'MT'
, 'CondensedTw'
, 'Cen'
, 'MT'
, 'Condensed'
, 'Extra'
, 'BoldUniversity'
, 'Roman'
, 'LETUtsaahVaniVerdanaVictorian'
, 'LETVijayaViner'
, 'Hand'
, 'ITCVivaldiVladimir'
, 'ScriptVrindaWebdingsWestwood'
, 'LETWide'
, 'Latin'
, 'Wingdings'
, 'Wingdings 2'
, 'Wingdings 3'
)
;

// IF THERE IS A GET REQUEST
$q = !empty($_GET['q']) ? strtoupper($_GET['q']) : NULL;
if (!$q) die();

// SIMULATE A DATA BASE LOOKUP
$num = 0;
foreach ($dat as $str)
{
    $new = strtoupper($str);
    similar_text($q, $new, $pct);
    if ($pct > 90.0)
    {
        $num++;
        echo PHP_EOL . $str;
    }
}
if (!$num) echo PHP_EOL . "NO MATCH FOR $q";

Select allOpen in new window

similar_text() in PHP!  Hmm.   Learn something new every day. :)

This might not work as expected... the return value is the matching character count.

if (similar_text($x, $y, $percent) > 90)

If you want a value greater than 90% matched, you would want to test the $percent variable.
http://php.net/manual/en/function.similar-text.php

nemws1  Similar_Text()  gives % percent matching. but how to search in mysql column and return which is 90% similar.

@nemws1:  Here's a teaching example showing some of the ways to compare strings.

<?php // RAY_similar_strings.php
error_reporting(E_ALL);
echo "<pre>" . PHP_EOL;


// SHOW SOME WAYS OF LOOKING AT IMPRECISELY MATCHED INFORMATION


// THINGS TO COMPARE
$strings = array
( array( '12345.1', '12345. 1'  )
, array( 'kitten',  'kitty'     )
, array( 'CATALOG', 'Catalog'   )
, array( 'cell',    'sell'      )
, array( 'super',   'souper'    )
, array( 'mi niña', 'mi nina'   )
, array( 'mi niña', 'mi ninia'  )
, array( 'ça va!',  'ca va!'    )
, array( 'Plaçe',   'Place'     )
, array( 'ça va!',  'sa va!'    )
, array( 'ca va!',  'sa va!'    )
, array( 'Yeehah',  'Yee-hah'   )
, array( 'toxic',   'poisonous' )
, array( 'glad',    'unglad'    )
, array( 'McLean',  'MacLean'   )
)
;

foreach ($strings as $string)
{
    $x = $string[0];
    $y = $string[1];
    echo PHP_EOL;
    echo "<b>COMPARING $x TO $y</b>";
    echo PHP_EOL;


    // http://php.net/manual/en/function.soundex.php
    echo PHP_EOL;
    echo 'SOUNDEX()';
    echo PHP_EOL;
    var_dump(soundex($x));
    var_dump(soundex($y));

    // http://php.net/manual/en/function.metaphone.php
    echo PHP_EOL;
    echo 'METAPHONE()';
    echo PHP_EOL;
    var_dump(metaphone($x));
    var_dump(metaphone($y));

    // http://php.net/manual/en/function.levenshtein.php
    echo PHP_EOL;
    echo 'LEVENSHTEIN()';
    echo PHP_EOL;
    var_dump(levenshtein($x, $y));

    // http://php.net/manual/en/function.similar-text.php
    echo PHP_EOL;
    echo 'SIMILAR_TEXT()';
    echo PHP_EOL;
    echo "COMMON CHARACTERS: " . similar_text($x, $y);
    echo PHP_EOL;
    similar_text($x, $y, &$pct);
    echo "PERCENT ALIKE: " . number_format($pct,1) . '%';

    echo PHP_EOL;
}

Select allOpen in new window

Best regards, ~Ray

@Ray_Paseur

$_GET wont be like array format. It is like above snippet font only.

You could also look at this option

http://stackoverflow.com/questions/9870913/php-mysql-select-where-similar-textx

http://blog.cavar.si/2011/04/how-to-implement-levenshtein-algotithm.html

... and $y was supposed to be changed to $row['text']

$_GET wont be like array format. It is like above snippet font only.

I don't know what that means.  Sorry.

On line 4, the $qf variable appears to be undefined.  You might want to raise the error_reporting() level to E_ALL so you can catch these things!

At Experts-Exchange it is traditional to award points when you have exercised the volunteer community.  Now it seems that you want to close the question without awarding any points.

It is not our job to write working code for you (although you got several examples of working code here).  For that, you need to hire a paid professional developer.  Our role here in the EE community is to help you find answers to your questions.  I thought we did a pretty good job of that.

So what you're saying to us is that there was nothing here at all that helped you in any way?  You already knew all about similar_text()?  You already knew how to set up test data and simulate queries?

this is the only working code as well as tested.

Except your solution does not match the question asked which dealt with similar text and a percentage

i want to echo one value from mysql column which is 90% similar to php variable