Returning the Duplicated ID

Question

Having this query

$query = 'INSERT INTO users(name) VALUES (:name)';
$stmt = $pdo->prepare($query);
$stmt->execute(['name' => $_POST['name']]);

INSERTing into The Table: Users where column name is UNIQUE.

Instead of doing two queries

$check = 'SELECT EXISTS (SELECT name FROM users WHERE name = :name);'
$stmt = $pdo->prepare($check);
$stmt->execute(['name' => $_POST['name']]);
if ($stmt->fetchColumn() == 0) {
    $query = 'INSERT INTO users(name) VALUES (:name)';
    $stmt = $pdo->prepare($query);
    $stmt->execute(['name' => $_POST['name']]);
}

Is it possible using PDO to FETCH the Duplicated row id if $query failed to INSERT? Something like

$query = 'INSERT INTO users(name) VALUES (:name)';
$stmt = $pdo->prepare($query);
$stmt->execute(['name' => $_POST['name']]);
if ($stmt->duplicated() > 0) {
    echo "Name already exists by the id number ".$stmt->duplicated()."";
}

In case it is impossible to return the Duplicated ID, Can I just tell if there is a Duplication without returning anything?

Example:

      users
    [id - name]
    [1  - MARX]
    [2  - MATH]

$query = 'INSERT INTO users(name) VALUES ('MARX')';
$stmt = $pdo->prepare($query);
$stmt->execute();
if ($stmt->duplicated() > 0) {
    echo "Name already exists by the id number ".$stmt->duplicated()."";
} else {
    echo "Name was Inserted";
}

Result: Name already exists by the id number 1

There's the ON DUPLICATE KEY UPDATE https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html — user3647971, Jan 31 '18 at 20:48
Also this question is possible duplicate of this: https://stackoverflow.com/questions/778534/mysql-on-duplicate-key-last-insert-id — user3647971, Jan 31 '18 at 20:49
Possible duplicate of [MySQL ON DUPLICATE KEY - last insert id?](https://stackoverflow.com/questions/778534/mysql-on-duplicate-key-last-insert-id) — user3647971, Jan 31 '18 at 20:50
@user3647971 I want to return the `ID` to use it in `PHP`, using `ON DUPLICATE KEY UPDATE` is to `UPDATE` it as I understood. — Toleo, Jan 31 '18 at 20:53
Note: Try and get out of the habit of declaring SQL statements in throw-away variables that are used only once. It's a lot easier to follow code where the query is supplied directly to the function, and there's no longer a chance of messing up and sending in `$sql3` instead of the visually similar `$sql8`. Good job using placeholders, though. Nice to see. — tadman, Jan 31 '18 at 20:56
You can use `SELECT COUNT(*) FROM users WHERE name=:name` just the same and see if that value comes back as zero, which is no match, or anything else which means a duplicate. Ensure you have a `UNIQUE` constraint on that column, then you'll be prevented from inserting duplicates. — tadman, Jan 31 '18 at 20:57
@Toleo Yes that's correct. If you check the manual tho it will explain how to get the id. Also you should reformat the question since if you insert a row, it will have auto_increment value of the auto_increment field as id, if it fails there's no row to retreive the id from. You CAN allthough retrieve the current auto_increment value — user3647971, Jan 31 '18 at 20:58
@tadman Yes, I use `SELECT EXISTS()` instead because it is faster to check existence, But what i want to do addtionally is to get the duplicated row ID with it. — Toleo, Jan 31 '18 at 21:04
What I believe you need is the` "SELECT id, name FROM users WHERE name = :name"` Then just fetch the results and use them as you see fit. — user3647971, Jan 31 '18 at 21:04
I've never use `SELECT EXISTS()` in over 20 years of programming. — ficuscr, Jan 31 '18 at 21:04
@user3647971 That what i'm using right now to fetch the `id` with `LIMIT 1` for better performance, But isn't there any function to get that duplicated id? i mean the SQL already checked that it is exists, Why not return it too? — Toleo, Jan 31 '18 at 21:06
@ficuscr As i read about it, They say that it is faster than using `COUNT(*)` — Toleo, Jan 31 '18 at 21:07
Why you need to duplicate it? If you do "SELECT id, name FROM users WHERE name = :name LIMIT 1" You'll get a row, or you don't get a row. Insert if you don't and $pdo->fetch(PDO::FETCH_ASSOC); if you do — user3647971, Jan 31 '18 at 21:07
If you have a `UNIQUE` constraint then it should be impossible to get more than one result unless your database is seriously bugged out. For situations like this I usually do an `INSERT`, then if that bounces back with a constraint collision, do a `SELECT` to find out what caused the collision. This handles race conditions fairly well. Two simultaneous calls with both get the same result even if one "wins". — tadman, Jan 31 '18 at 21:07
@Toleo Most databases are pretty good at returning counts. Most modern engines just do a quick check of the index if that data's indexed and return a number, they don't even touch the raw data. — tadman, Jan 31 '18 at 21:08
That also works. For moderate database loads, which is <100 queries/sec. these days, that'll perform just fine. Be **extremely careful** with [SQL injection](http://bobby-tables.com/) and use placeholder values whenever possible. — tadman, Jan 31 '18 at 21:12
@tadman But i really find it confusing how the `MYSQL` is able to tell if the `name` already exists But not being able to return the `name` or the `id` of that already existing row if needed. — Toleo, Jan 31 '18 at 21:15
EXISTS is a MySQL built-in function, in a select you can always do the actualy lookup too, "SELECT EXISTS(...) as column1, id FROM ..." I think, not sure tho haven't used exists — user3647971, Jan 31 '18 at 21:21
@user3647971 `EXISTS()` as i remember stops at the first result, Which makes it efficient, But after all, It depends on the `EXPLAIN` to achieve performance. — Toleo, Jan 31 '18 at 21:26
@Toleo It's worth noting that a more standards compliant database like Postgres has better support for this. MySQL has evolved from a very *minimal* interpretation of the SQL standard and has a number of quirky, irregular extensions like `ON DUPLICATE KEY` that are handy, but not necessarily feature complete. — tadman, Jan 31 '18 at 21:27
According to https://stackoverflow.com/questions/1676551/best-way-to-test-if-a-row-exists-in-a-mysql-table there's one answer that circles around the exists and it seems it returns a row **in every case**, You need to change your rowCount logic too — user3647971, Jan 31 '18 at 21:31
Seems like https://stackoverflow.com/questions/7004409/is-there-mysql-insert-on-duplicate-key-select could also fit this case — user3647971, Feb 01 '18 at 00:35
@user3647971 Oh, correct, sorry, Didn't notice i used `rowCount()` with `EXISTS` until now, I always use `fetchColumn()` with it, I always made this mistake, using `rowCount()` with `EXISTS` it was a big fault kept following me. — Toleo, Feb 02 '18 at 11:40

score 0 · Answer 1 · answered Jan 31 '18 at 20:51

0

Create a unique index on users(name).

Then, catch the error when the unique index is violated.

This has nothing to do with on duplicate key update.

A unique index on name is highly, highly recommended for this purpose. Your code is not thread-safe. Two threads could have the same name. Each could check that it doesn't exist. And each could try to insert it. All that effort -- and you'll get duplicates anyway.

Let the database do what it is supposed to be doing -- protecting your data. Define the unique index/constraint.

answered Jan 31 '18 at 20:51

Gordon Linoff

1,242,037
58
646
786

I understand, But the `PDOException` throws its `message` which I want to customize it to use `Name already exists` Instead of its Message, Which i suppose it is not changable?, Also I would like to return the Duplicated row ID in case It exists – Toleo Jan 31 '18 at 20:56
Can look at this. https://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql Think I would just use a transaction with proper locking, do the select to check if exists, gets you the ID. Then if it did exist, serve your message with that returned ID. – ficuscr Jan 31 '18 at 21:00

Returning the Duplicated ID

Example:

1 Answers1