How to check duplicates in the database and not to make an entry at their location?

Good afternoon.
Record the cycle in the database, but there is a need in the filtering repeated keys (in this case s2).
How to write a condition for when finding matches in the database were made only those rows where no match?
$stmt = mysqli_prepare($link, "INSERT INTO Users (a1, a2, a3, s1, s2) VALUES (?, ?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sssss', $a1, $a2, $a3, $s1, $s2);

$check = mysqli_prepare($link, "SELECT * FROM Users WHERE s2='$s2'");
mysqli_stmt_bind_param($check, s, $s2);

foreach($json['object'] as $item) {
 $a1 = $item['a1'];
 $a2 = $item['a2'];
 $a3 = $item['a3'];
 $s1 = $item['s1'];
 $s2 = $item['s2'];
mysqli_stmt_execute($check);
if($check){
...
}
 else {
mysqli_stmt_execute($stmt);
}
}
mysqli_stmt_close($stmt);
 mysqli_close($link);
March 23rd 20 at 18:44
1 answer
March 23rd 20 at 18:46
Solution
Read about the design: INSERT IGNORE ..

$stmt = mysqli_prepare($link, "INSERT IGNORE INTO Users (a1, a2, a3, s1, s2) VALUES (?, ?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sssss', $a1, $a2, $a3, $s1, $s2);

foreach($json['object'] as $item) {
 $a1 = $item['a1'];
 $a2 = $item['a2'];
 $a3 = $item['a3'];
 $s1 = $item['s1'];
 $s2 = $item['s2'];
mysqli_stmt_execute($stmt);
}
mysqli_stmt_close($stmt);
 mysqli_close($link);
Understood. How can I specify it to ignore values of s2? Others, too, can be repeated... - bradly40 commented on March 23rd 20 at 18:49
@bradly40if other fields have a unique index in the DB, then it'll work for them. If a unique index not, just as you did in the beginning make a request with a check whether there is already such data in the database for the desired conditions, then if there is missing, if not added. - brisa57 commented on March 23rd 20 at 18:52
@brisa57, I'll try to explain)
I have two unique fields - id and s2. Data at rest can be repeated. In principle, id could I refuse. If I make the pitch s2 as the primary key - this design insert ignore will work if in other fields of type a1 will match? - bradly40 commented on March 23rd 20 at 18:55
@bradly40, Yes, it works this way
-------------
id - primary
a1 = unique
a2 = unique
a3
-------------
With INSERT IGNORE, mysql will check whether there is already in the fields a1 or a2 value is the same as in the request. If there is, data will not be added.

It can also be a composite index
-------------
id - primary
a1 unique (a1,a2)
a2 unique (a1,a2)
a3
-------------
In this case, it will check whether there is already a value a1+a2, if so, details will not be added

But if you need to make sure that a3 will be unique, then you have to do first SELECT and then INSERT - brisa57 commented on March 23rd 20 at 18:58
@brisa57, Yes, I made the unique field s2 and it worked, thanks :) - bradly40 commented on March 23rd 20 at 19:01
@brisa57,
INSERT IGNORE in mysql


But if you need to make sure that a3 will be unique, then you have to do first SELECT and then INSERT


Ay-ay, ay. Now and then all say that php *ovno-coders. - Lawson86 commented on March 23rd 20 at 19:04

Find more questions by tags MySQLPHP