php - 匹配 mysql 表中的重复电子邮件条目(标准格式)

Question

我有下表，我要匹配邮箱。例如，如果我从这个表中找到 a.b.c@gmail.com 它应该匹配 abc@gmail.com

+--------+--------+---------+------------------+------------+-------+
| name   | owner  | species | email_id         | birth      | death |
+--------+--------+---------+------------------+------------+-------+
| Fluffy | Harold | cat     | abc@gmail.com    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | abcd@gmail.com   | 1989-05-13 | NULL  |
+--------+--------+---------+------------------+------------+-------+

目前，我允许将电子邮件存储在诸如 *a.bc@gmail.com 之类的表中**在这种情况下，如果我找到 abc@gmail.com 它应该匹配 a.bc@gmail.com

另一个可能的邮件地址

a.bc@gmail.com
ab.c@gmail.com
abc+1@gmail.com
abc+[.....]@gmail.com

[.....] - 表示数字

Answer 1

希望这能给你一个想法

$newUserEmail = "test4@example.com";
    $matchEmail=preg_replace('/[^a-zA-Z0-9_@-]/s', '', $newUserEmail);
    $result = $mysqli->query("SELECT * FROM your_table WHERE email_id = '$newUserEmail'");
    echo "num_rows = ".$result->num_rows."\n";
    $result1 = $mysqli->query("SELECT * FROM your_table WHERE email_id = '$matchEmail'");

    echo "matchEmail= ".$result1->num_rows."\n";

    if ($result->num_rows > 0) {
       echo "Duplicate email\n";
       // do something to alert user about non-unique email
    } elseif($result1->matchEmail > 0){
         echo "Duplicate email\n"; // regex match email found 

    }
    else{

      $result = $mysqli->query("INSERT IGNORE INTO your_table "); // make your insert query here 
      if ($result === false) {echo "SQL error:".$mysqli->error;}
    }