Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • Last Modified:

mysql instead of $string

this is a followup to question
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_27838075.html

where I want to gather data from title field in my mysql table
instead of $string[]

<?php // RAY_temp_rgb192.html
error_reporting(E_ALL);
echo "<pre>";

// COPIED FROM THE POST AT EE
$string[1]='hello, how are you';
$string[2]='very different';
$string[3]='hi, how are you';
$string[4]='another string';

$string[] = 'Yousef Islam';
$string[] = 'Yusuf Islam';
$string[] = 'Yusef Islaam';
$string[] = 'Yousif Isla\'am';
$string[] = 'YUSUF Islam';

// COMPARISONS USING SOUNDEX+LEVENSHTEIN
foreach ($string as $x)
{
    // COMPUTE THE SOUNDEX KEY
    $sx = soundex($x);
    echo PHP_EOL . "TESTING <b>$x</b> WITH SOUNDEX() $sx";

    // COMPARE TO THE OTHER STRINGS
    foreach ($string as $y)
    {
        $sy = soundex($y);
        $sl = levenshtein($sx, $sy);
        echo PHP_EOL
        . "SOUNDEX() $sx"
        . " IS $sl DISTANCE FROM $sy";
    }
}
echo PHP_EOL;

Open in new window



changed to

<?php // RAY_temp_rgb192.html
include '5/db.php';
error_reporting(E_ALL);
echo "<pre>";
$result=mysql_query("select * from table order by title limit 0,4");
//$results=mysql_fetch_array($query);

//echo '<br>results content: {'.$result['title'].'}';
//foreach ($results as $row){
/*while ($row=mysql_fetch_array($result,MYSQL_ASSOC)){
  echo '<br>'.$row['title'];
}*/

// COPIED FROM THE POST AT EE
$string[1]='hello, how are you';
$string[2]='very different';
$string[3]='hi, how are you';
$string[4]='another string';

$string[] = 'Yousef Islam';
$string[] = 'Yusuf Islam';
$string[] = 'Yusef Islaam';
$string[] = 'Yousif Isla\'am';
$string[] = 'YUSUF Islam';

// COMPARISONS USING SOUNDEX+LEVENSHTEIN
//foreach ($string as $x)
//foreach (mysql_fetch_array($result,MYSQL_ASSOC) as $x)
while ($x=mysql_fetch_array($result,MYSQL_ASSOC))
{
  
  echo 'hello'.$x['title'];
    // COMPUTE THE SOUNDEX KEY
    $sx = soundex($x);
    echo PHP_EOL . "TESTING <b>$x</b> WITH SOUNDEX() $sx";

    // COMPARE TO THE OTHER STRINGS
    //foreach ($string as $y)
    while ($y=mysql_fetch_array($result,MYSQL_ASSOC))    
    {
        $sy = soundex($y);
        $sl = levenshtein($sx, $sy);
        echo PHP_EOL
        . "SOUNDEX() $sx"
        . " IS $sl DISTANCE FROM $sy";
    }
}
echo PHP_EOL;

Open in new window

0
rgb192
Asked:
rgb192
  • 3
  • 2
  • 2
  • +2
3 Solutions
 
Julian HansenCommented:
You are calling mysql_fetch_array and then using an associative index. Change to mysql_fetch_assoc
<?php // RAY_temp_rgb192.html
include '5/db.php';
error_reporting(E_ALL);
echo "<pre>";
$result=mysql_query("select * from table order by title limit 0,4");
//$results=mysql_fetch_array($query);

//echo '<br>results content: {'.$result['title'].'}';
//foreach ($results as $row){
/*while ($row=mysql_fetch_array($result,MYSQL_ASSOC)){
  echo '<br>'.$row['title'];
}*/

// COPIED FROM THE POST AT EE
$string[1]='hello, how are you';
$string[2]='very different';
$string[3]='hi, how are you';
$string[4]='another string';

$string[] = 'Yousef Islam';
$string[] = 'Yusuf Islam';
$string[] = 'Yusef Islaam';
$string[] = 'Yousif Isla\'am';
$string[] = 'YUSUF Islam';

// COMPARISONS USING SOUNDEX+LEVENSHTEIN
//foreach ($string as $x)
//foreach (mysql_fetch_array($result,MYSQL_ASSOC) as $x)
//
// CHANGE THIS LINE TO mysql_fetch_assoc
//
while ($x=mysql_fetch_assoc($result,MYSQL_ASSOC))
{
  
  echo 'hello'.$x['title'];
    // COMPUTE THE SOUNDEX KEY
    $sx = soundex($x);
    echo PHP_EOL . "TESTING <b>$x</b> WITH SOUNDEX() $sx";

    // COMPARE TO THE OTHER STRINGS
    //foreach ($string as $y)
    while ($y=mysql_fetch_array($result,MYSQL_ASSOC))    
    {
        $sy = soundex($y);
        $sl = levenshtein($sx, $sy);
        echo PHP_EOL
        . "SOUNDEX() $sx"
        . " IS $sl DISTANCE FROM $sy";
    }
}
echo PHP_EOL;
                                  

Open in new window

0
 
lwadwellCommented:
What was your question?
0
 
gr8gonzoConsultantCommented:
@julian - mysql_fetch_array allows associative indexes by default. He also specified the MYSQL_ASSOC parameter later, too.

@rgb192 - Change:
$sy = soundex($y);
to
$sy = soundex($y['title']);
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
gr8gonzoConsultantCommented:
Actually, disregard that last suggestion - I think I see what you're trying to do. If I'm not mistaken, you're trying to pull several titles and then compare them all to each other.

Try this:
<?php // RAY_temp_rgb192.html
include '5/db.php';
error_reporting(E_ALL);
echo "<pre>";
$result=mysql_query("select * from table order by title limit 0,4");
$titles = array();
while ($x=mysql_fetch_array($result,MYSQL_ASSOC))
{
  $titles[] = $x["title"];
}

// COMPARISONS USING SOUNDEX+LEVENSHTEIN
foreach($titles as $indexA => $titleA)
{  
    // COMPUTE THE SOUNDEX KEY
    $sA = soundex($titleA);
    echo PHP_EOL . "TESTING <b>$titleA</b> WITH SOUNDEX() $sA";

    // COMPARE TO THE OTHER STRINGS
    foreach($titles as $indexB => $titleB)
    {
        $sB = soundex($titleB);
        $sl = levenshtein($sA, $sB);
        echo PHP_EOL
        . "SOUNDEX() $sA"
        . " IS $sl DISTANCE FROM $sB";
    }
}
echo PHP_EOL;

Open in new window

0
 
Ray PaseurCommented:
A strategy might be to query the DB table and copy all of the results set into an array (like $string) then apply the original algorithm.  The code might look something like this.  See lines 5-15.  Make the changes appropriate to your table and column names.

<?php // RAY_temp_rgb192.html
error_reporting(E_ALL);
echo "<pre>";

// GETTING THE TITLES INTO THE ARRAY
$sql = 'SELECT title FROM my_table ORDER BY title';
$res = mysql_query($sql) or die("FAIL: $sql <br/> " . mysql_error());
while ($row = mysql_fetch_assoc($res))
{
    $string[] = $row['title'];
}

// SHOWING THE ARRAY OF TITLES
print_r($string);

// COMPARISONS USING SOUNDEX+LEVENSHTEIN
foreach ($string as $x)
{
    // COMPUTE THE SOUNDEX KEY
    $sx = soundex($x);
    echo PHP_EOL . "TESTING <b>$x</b> WITH SOUNDEX() $sx";

    // COMPARE TO THE OTHER STRINGS
    foreach ($string as $y)
    {
        $sy = soundex($y);
        $sl = levenshtein($sx, $sy);
        echo PHP_EOL
        . "SOUNDEX() $sx"
        . " IS $sl DISTANCE FROM $sy";
    }
}
echo PHP_EOL;

// COMPARISONS USING METAPHONE+LEVENSHTEIN
foreach ($string as $x)
{
    // COMPUTE THE METAPHONE KEY
    $sx = metaphone($x);
    echo PHP_EOL . "TESTING <b>$x</b> WITH METAPHONE() $sx";

    // COMPARE TO THE OTHER STRINGS
    foreach ($string as $y)
    {
        $sy = metaphone($y);
        $sl = levenshtein($sx, $sy);
        echo PHP_EOL
        . "METAPHONE() $sx"
        . " IS $sl DISTANCE FROM $sy";
    }
}
echo PHP_EOL;

// COMPARISONS USING SIMILAR_TEXT() BUT SEE THE NOTES HERE BEFORE YOU USE IT!
// http://php.net/manual/en/function.similar-text.php#109507
// COMPARISONS USING SIMILAR_TEXT
foreach ($string as $x)
{
    echo PHP_EOL . "TESTING <b>$x</b> WITH SIMILAR_TEXT()";

    // COMPARE TO THE OTHER STRINGS
    foreach ($string as $y)
    {
        $ss = similar_text($x, $y, $sp);
        echo PHP_EOL
        . "SIMILAR_TEXT() $x"
        . " HAS $ss CHARACTERS IN COMMON WITH $y "
        . '('
        . number_format($sp, 0)
        . '%)'
        ;
    }
}
echo PHP_EOL;

Open in new window

HTH, ~Ray
0
 
Julian HansenCommented:
As gr8gonzon demonstrated I have only had a superficial look at the code but it occurred to me that you might be able to do this with a SQL query - I am under the cosh a bit so can't look at this in depth so maybe Ray_Paseur or Gr8Gonzo can pick this up (I don't want the points)

I was thinking along the lines of
SELECT DISTINCT title FROM table WHERE SOUNDEX(title) 
IN (SELECT SOUNDEX(title) FROM table ORDER BY title) 

Open in new window

Not quite the same because subqueries don't support LIMIT and original was only taking the top 4 results - but might be something to exploit through MySQL on this.
0
 
rgb192Author Commented:
thanks for code samples
0
 
Julian HansenCommented:
Thanks for the points but as gr8gonzo pointed out my recommendation would not have made much of a difference.
0
 
rgb192Author Commented:
I have a similar question

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_27877009.html

where I want to echo many columns from same row
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now