How to search for similar records in mysql by possible similarity of 3 fields?

Question

Nginx

Good day. I'll go straight to the question.
There is a table with fields: id, id_double, field_1, field_2, field_3
Fields: field_1, field_2, field_3 can have the same values.
There are several duplicates:
Duplicate 1 (by field_1 (or by field_2 or by field_3 or by all 3 fields at once))
Duplicate 2 (by field_2 (or by field_1 or by field_3 or by all 3 fields at once))
Now you need to check among the existing duplicates, and if at least one field matches, then these two duplicates are combined into a group.
PS it is desirable to bring it in a cycle, since the search is performed on all records in the table and must take into account the current changes in duplicates.

Answer 1

what does it write in the logs?

Answer 2

Well, the beginning of trouble is :-)
is mod_ssl.so present in the specified directory? What does it write in the logs? The logs need to be put in more detail so that the error is not missed.

Answer 3

With this table structure:
This code turned out:

$reg_name = 1; // имя пользователя
$reg_ip = 1; // ип адрес
$reg_phone = 1; // телефон


$q = mysqli_connect('localhost', 'root', '', 'bospor');

$arr_name = array($reg_name);
$arr_ip = array($reg_ip);
$arr_phone = array($reg_phone);

$arr_group = array();
$arr_id = array();
$stop = false;
while($stop == false){
  $sum1 = count($arr_name) + count($arr_ip) + count($arr_phone) + count($arr_group);
        
    $name = '';
    foreach($arr_name as $w){ $name .= "'$w',"; }
    $name =  mb_substr($name, 0, -1, 'utf-8');
    $ip = '';
    foreach($arr_ip as $w){ $ip .= "'$w',"; }
    $ip =  mb_substr($ip, 0, -1, 'utf-8');
    $phone = '';
    foreach($arr_phone as $w){ $phone .= "'$w',"; }
    $phone =  mb_substr($phone, 0, -1, 'utf-8');
    $group = '';
    foreach($arr_group as $w){ $group .= "'$w',"; }
    $group =  @mb_substr($group, 0, -1, 'utf-8');
        
    $sql = "SELECT * FROM `bospor_double` WHERE `user_name` IN($name) OR `user_phone` IN($phone) OR `user_ip` IN($ip)".( ( empty($group) ) ? '' : " OR `user_ip` IN($group) " );

    $res = $q->query($sql);
    $res = $res->fetch_all(MYSQLI_ASSOC);

    foreach($res as $w){
          
       if( !in_array(  $w['user_name'], $arr_name  ) ) $arr_name[] = $w['user_name'];
      if( !in_array(  $w['user_ip'], $arr_ip  ) ) $arr_ip[] = $w['user_ip'];
            if( !in_array(  $w['user_phone'], $arr_phone  ) ) $arr_phone[] = $w['user_phone'];
       if( !in_array(  $w['user_group_id'], $arr_group  ) ) $arr_group[] = $w['user_group_id'];
      if( !in_array(  $w['user_id'], $arr_id  ) ) $arr_id[] = $w['user_id'];
    }

        $sum2 = count($arr_name) + count($arr_ip) + count($arr_phone) + count($arr_group);
        
        if($sum1 == $sum2){
          $stop = true;
        }
        else {
          $sum1 = $sum2;
        }
}

$id = '';
foreach($arr_id as $w){ $id .= "'$w',"; }
$id =  mb_substr($id, 0, -1, 'utf-8');

        
// $id - ваш список пользователей

Answer 4

I did not understand anything.

Answer 5

I can't think of a question, so I'm asking for help. Thank you.