I've tried both of these, INSERT IGNORE
and LIMIT 1
but records continue to be inserted into my DB. Aren't these commands supposed to prevent a new row being inserted if the row exists already ? Is it because the auto_increment value is different when the new row is added and therefore it is technically a different row?
The relevant piece of code looks like this :
$user_id = 3;
while ($row = $result->fetch_assoc()) {
//this the the user_id in the user table of the matching phone number
echo $row['user_id']."<br />";
//call this user_id contact_id
$contact_id = $row['user_id'];
$stmt2 = $con->prepare("INSERT IGNORE INTO contacts (user_id, contact_id) VALUES(?,?)") or die(mysqli_error($con));
$stmt2->bind_param('ii', $user_id, $contact_id) or die ("MySQLi-stmt binding failed ".$stmt2->error);
$stmt2->execute() or die ("MySQLi-stmt execute failed ".$stmt2->error);
}
I also tried :
$stmt2 = $con->prepare("INSERT IGNORE INTO contacts (user_id, contact_id) VALUES(?,?) LIMIT 1")
My table structure looks something like this, 3 columns :
auto_inc user_id contact_id
1 3 24
2 3 12
3 3 87
etc...