How to check if entry already exists?

Question

I want to check if the $steamprofile['steamid'] already exists, to prevent multiple entries.

The SteamID64 looks like: 76561197968052866 and is stored in bigint(64).

Thats my code so far:

$db = mysqli_connect($servername,$username,$password,$dbname) or die ("could not connect to database");
$user_check_steamid = "SELECT * FROM Users WHERE SteamID64";
$result = mysqli_query($db, $user_check_steamid);
$user = mysqli_fetch_assoc($result);

if($user) {
    if($user['SteamID64'] === $steamprofile['steamid']){
    //Update username
    //code don't exists right now
    echo "update username";
    } else {
    //Create new account in database
    //$sql = "INSERT INTO Users (SteamID64, Username) VALUES ('" . $steamprofile['steamid'] . "', '" . $steamprofile['personaname'] . "')";
    //if ($db->query($sql) === TRUE) { }
    echo "creating user";
    }}

I don't want to use a primary key.

Do you have any ideas, what I did wrong or how I can improve it?`

What is `WHERE SteamID64` supposed to do? Does that work? Will it return ALL users? (if so you'd need to iterate over the results) — brombeer, Jan 16 '20 at 16:57
The query is wrong. SELECT * FROM Users WHERE SteamID64 is wrong, it should be something like SELECT * FROM Users WHERE SteamID64 = ? — Giacomo M, Jan 16 '20 at 16:57
if you only want to get the SteamID64 field do this - > $user_check_steamid = "SELECT SteamID64 FROM Users"; — Ramyz, Jan 16 '20 at 17:08
@kerbholz it should compare with ='"$steamprofile['steamid']"', that wasn't working for me, so I just removed it. Because of that it was only working for the first entry. This was just a logical bad from me, got it now with if(!$user) and if($user) :D — Schmaniel, Jan 16 '20 at 17:10
@AlexHowansky I wanted to learn something new and I need this for other inserts too, because I can only make one primary key. — Schmaniel, Jan 16 '20 at 17:10
if it should be compare with ='"$steamprofile['steamid']"'. THEN SQL statment should be like this -> "SELECT * FROM Users WHERE SteamID64 = ' " . $steamprofile['steamid'] . " ' "; — Ramyz, Jan 16 '20 at 17:14

score 1 · Answer 1 · answered Jan 16 '20 at 17:19

Never rely solely on the output of a SELECT query to avoid inserting duplicates. In a multi-user system, another process could insert into the database between the time when your SELECT runs and your subsequent INSERT runs, creating a duplicate even though the SELECT didn't previously find one.

The only way to definitively prevent duplicates is via a UNIQUE constraint. You can use a SELECT as a convenient pre-condition, but you still need to create a UNIQUE constraint on the field in question and then verify that your INSERT actually succeeded.

While you can make only one primary key, you can make an arbitrary number of unique constraints.

score -2 · Answer 2 · answered Jan 16 '20 at 17:23

-2

Your query is wrong:

"SELECT * FROM Users WHERE SteamID64"

Should be

"SELECT * FROM Users WHERE SteamID64 = :searchedtext"
$query->bindParam(':searchedtext', $steamprofile['steamid']);

Then you bind your php variable to a mysqli parameter used in the query to try to retrieve the user.

answered Jan 16 '20 at 17:23

davidc2p

320
3
9

They're using the MySQLi_ api, not PDO. – Funk Forty Niner Jan 16 '20 at 17:32
The principle is exactly the same: $query = $mysqli->prepare('SELECT * FROM Users WHERE SteamID64 = ?'); $query->bind_param('s', $steamprofile['steamid']); – davidc2p Jan 16 '20 at 17:46

How to check if entry already exists?

2 Answers2