Php check if users exists in database

Hello, I’ve recorded data in database and read data from database, no trouble with that. Now I’m trying to search a specific data, and if found do nothing, or if not found insert a record.

I’ve been reading this forum and trying stuff from what I’ve red, but not grabbing the hand of it. Could someone explain it better? or what should I search in the manual to understand this more.

include_once "connection.php";

$userId = "TQWE213QWE123";

$sql_read = "SELECT TRUE FROM users WHERE userId = ? LIMIT 1";
$stmt = $pdo->prepare($sql_read);
$stmt->execute($userId);
$exists = $stmt->fetch();

if ($exists) {
    echo 'found';
} else {
    $now = date('Y-m-d H:i:s.') . gettimeofday()['usec'];
    $sql_add = 'INSERT INTO users (userId,useremail,time) VALUES (?,?,?)';
    $stmt_add = $pdo->prepare($sql_add);
    $stmt_add->execute(array($userId,$useremail,$now));
    echo 'not found, new data inserted';
}

I’t always says not found even if the record is there, and it prints a duplicate :S

The whole idea is wrong. What you do is set as unique constraint on the column, then attempt the insert catching any duplicate error. Your suggested approach would build in a race condition.

The LIMIT on your query is pointless. There should only be one record in the DB with any given username enforced by a unique constraint on the column.

There is no need for you to create and insert a timestamp value. MySQL can do it automatically for you.
https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

Your database must enforce uniqueness. When you try to execute a SELECT query to find if data doesn’t exist, a race condition exists where multiple concurrent instances of your script will all find that the value doesn’t exist and attempt to insert it.

The simple solution to prevent duplicates is to define the column(s) that must be unique (userId and useremail) as unique indexes, then just attempt to insert the data and detect if a duplicate index error occurs.

The reason your existing code doesn’t work is because you must supply an array to the ->execute() call. This is either treating the string as an array of characters and is using the first character as the value or it is producing php errors. Do you have php’s error reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, and have you set the PDO error mode to use exceptions for errors?

1 Like

I tought they where set to on, since everytime i have errors I get them on a log file then I check them and solve them, but this particuraly dosen’t show up in a log.

Ok so I’ve checked E_ALL and they are set to [Off], i’ll turn them [ON]. And pdo mode to exceptions set to: PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

Ok so now I’ll be able to see this errors right?

Thank you guys for your answers.

I get the id and username from a json api when the user enters this interface I’ve made, and I’m making like a silly panel where you can check your personal info, and I’m tryng to store this data so I can add the posibility of making a bigger database table where you can choose like having the website in other colors, having some options disabled if you don’t wan’t them, reordering your main panel so you can see your stuff like you would like it, etc.
I just explained to enter in context xD