Mikkel Bertelsen Mikkel Bertelsen - 7 months ago 14
PHP Question

How to prevent inserting duplicate records?

I want to make my PHP check if something with the same specific data already exists in my database table.

I have a database called

test
with a table called
users
.

This is where I would like to check if a row
steamid
already exists with the same
$steamid
number.

<?php

// Datebase infomation
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";
$steamid = $steamprofile['steamid'];

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Create data

$sql = "INSERT INTO users (steamid)
VALUES ('$steamid')";

if (!$conn->query($sql) === TRUE) {
echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Answer

you need to add a PRIMARY KEY or a UNIQUE constraint on the steamid column.

after deleting all the duplicates, then you can run this command:

ALTER TABLE users ADD PRIMARY KEY(steamid)

then your query will fail if you attempt to insert a duplicate steamid into the users table.

as @Jeremy mentioned, you can use REPLACE, but this also only works if you have a PRIMARY KEY or UNIQUE constraint on the column. this is the preferred method if you have additional columns aside from the steamid that you want to store in the database table because it will update those values. however, since your query as stated in the question INSERT INTO users (steamid) VALUES ('$steamid') only contains the one field, then it's not of much consequence unless you want to catch the conditional error of an attempted duplicate record, in which case you should stick to the INSERT statement.

Comments