Re: Re: find (matching) person in other table

Jared Farrish wrote:

On 5/30/07, Afan Pasalic <afan@afan.net> wrote:

yes. in one hand it's more for mysql list. though, I was thinking more if somebody had already something similar as a "project". more as path I have to follow. e.g., in your example, in where clause AND doesn't work because bob could be robert too, right? and last name has to match 100%, right? (or I'm wrong?)

You're right. Remember, that was an example of what you MIGHT do, not necessarily what you SHOULD do. sure. I just want to be sure you understand what I was thinking (because of my english :-) )

You could also situationally check the returned fields and if it's greater than, say, 25 or 50, re-run the query and change the letters matched to 4, for instance, and then add a link to get the greater total. You could also look at the "search box suggestion" code that's out there for a way to implement this on the server side. Don't know if that code will be optimized or not, but that's essentially what you're doing here. how "smart" solution will be something like this:

$query = my_query("select id from members where last_name='$last_name'"); while($result = mysql_fetch_array($query)) {

    $MEMBERS[$result['id']] += 50;

}

Well, see, if the match isn't exact, it won't return anything. Unless you know the exact name. You also may have to deal with someone misstyping their name(s). $query = my_query("select id from members where first_name='$first_name'");

while($result = mysql_fetch_array($query)) {

    $MEMBERS[$result['id']] += 10;

} $query = my_query("select id from members where email='$email'"); while($result = mysql_fetch_array($query)) {

    $MEMBERS[$result['id']] += 85;

}

Why would you do that many SELECTs? (Also, if you cap the SQL commands, it's easier to read.) "most likely" because I was thinking that it shouldn't be big deal. but after your and richard's email - definitely have to try to make it as one query.

etc.

after last query I will have an array of people. and I'll list all person with "score" more than 50.

This is a really roundabout way to do this. Look at the Levinshtein PHP manual page for some suggestions on how to calculate similarities. I *think* that should be better to do this: for ($i = 0; $i < count($mysqlresultset); $i++) { $lev = levenshtein($mysqlresultset[$i][$firstname], $postedname); if ($lev > 49) {

       $matches[] = $mysqlresultset[$i];

} } I'm just studying it. :-)

or, since last name MUST match, I think it's better this way (just got

in my head): $query = my_query("select id from members where last_name='$last_name'"); while($result = mysql_fetch_array($query)) {

    $query = my_query("select id from members where

first_name='$first_name'");

    while($result = mysql_fetch_array($query))
    {
        $MEMBERS[$result['id']] += 10;
    }

    $query = my_query("select id from members where email='$email'");
    while($result = mysql_fetch_array($query))
    {
        $MEMBERS[$result['id']] += 85;
    }

    etc.

}

There's a lot of unnecessary work you're making PHP and your database do. This is quite inefficient code. that's why I ask here - to learn. and I appreciate for any help.

If you're trying to match the emails and whatnot, then combine all those queries together. SELECT them all together. It looks like what you're doing is weighting it by email address, which you can add to the SELECT I posted (although you need to think about how you use your wildcards for email addresses, such as maybe matching the beginning OR the end, for instance). It's even better if the person has to activate the account with an email link to activate, since then you'd know the email address existed (although it doesn't mean it isn't someone in the database that isn't already in there).

email has to match "in total". sales@abc-comp.com and info@abc-comp.com are NOT the same in my case. thanks jared, -afan