匹配多个列值以获取记录匹配百分比

Question

I have a table of records which contains first name, last name, mobile number, email of persons details. I want to check each time when I get these values passed from front end page to check whether first name matches to a record in Database with the same first name and also for last name and so on.. So if all four columns match to the parameters getting from front page some how then the matching percentage is 100%. Likewise if three parameters match then it'll be 75% and so on...

attempt...

    $firstName = 'peter';
    $lastName = 'cetera';
    $phoneNumber = '0384849494';
    $emailAddress = '[e-mail]';
    $matchPercentage;
    $stmt = $mysqli->prepare("SELECT 
                              person.firstName,
                              person.lastName,
                              person.mobileNo,
                              person.email
                            FROM
                              person
                              LEFT OUTER JOIN reg_person ON (person.personId = reg_person.personId)
                              AND (person.messageid = reg_person.messageId)
                              AND (person.firstName = reg_person.firstName)
                              AND (person.lastName = reg_person.lastName)
                              AND (person.mobileNo = reg_person.mobile)
                              AND (person.email = reg_person.email)
                            WHERE
                              person.firstName = ? AND 
                              person.lastName = ? AND 
                              person.mobileNo = ? AND 
                              person.email = ?");

    $stmt->bind_param("ssss",$firstName,$lastName,$phoneNumber,$emailAddress) or die($mysqli->error);
    $stmt->execute();
    $stmt->bind_result($firstName,$lastName,$mobileNo,$email);
    $stmt->store_result();
    $num_of_rows = $stmt->num_rows;
    if($num_of_rows > 0){
        $matchPercentage = '100%';
    }else{......

will it take 16 mysql queries to do it?... or is there a simpler way?

Answer 1

You can get the best matching record by counting the number of matching fields and then doing a reverse order by:

SELECT p.*,
       ( (p.firstName = ?) + (p.lastName = ?) + (p.mobileNo = ?) + (p.email = ?) ) numMatches
FROM person p LEFT OUTER JOIN
     reg_person rp
     ON p.personId = rp.personId AND
        p.messageid = rp.messageId AND
        p.firstName = rp.firstName AND
        p.lastName = rp.lastName AND
        p.mobileNo = rp.mobile AND
        p.email = rp.email
 WHERE p.firstName = ? OR 
       p.lastName = ? OR 
       p.mobileNo = ? OR 
       p.email = ?
ORDER BY numMatches DESC
LIMIT 1;