PHP & MySQL - INSERT WHERE NOT EXISTS [duplicate]

Question

I need to insert data except if 2 fields exist in that row. Below is my current query that adds everything that works.

$sql = "INSERT INTO contact_sync_status (ghl_contact_email, ghl_contact_data, result, wp_user_id) VALUES ('$source->email', '$payload', 'success', $user->id)";

So I tried the following to make it conditional:

$sql = "INSERT INTO contact_sync_status (ghl_contact_email, ghl_contact_data, result, wp_user_id) VALUES ('$source->email', '$payload', 'success', $user->id) WHERE NOT EXISTS (SELECT 1 FROM contact_sync_status WHERE ghl_contact_email = '$source->email' AND wp_user_id = $user->id)";

But i receive a SQL syntax error because it seems that WHERE NOT EXISTS can't be used with INSERT. I've also tried a unique index in the DB but this is not an option.

Little Bobby says you may be at risk for SQL Injection Attacks. Learn about Prepared Statements with parameterized queries. — GrumpyCrouton
– GrumpyCrouton, Commented Aug 27, 2020 at 15:03
Doing a completely separate query to see if it exists first would work. — GrumpyCrouton
– GrumpyCrouton, Commented Aug 27, 2020 at 15:04
Why is a unique index not an option? This is the accepted (and really, the only effective) mechanism for doing what you're trying to do. — Alex Howansky
– Alex Howansky, Commented Aug 27, 2020 at 15:05
You can not guarantee duplicate prevention by doing a select first. — Alex Howansky
– Alex Howansky, Commented Aug 27, 2020 at 15:12
@GrumpyCrouton Any chance you can provide an example of how to do this? The first one would be a select, but then how would I use that? — vdsdsgzsfh
– vdsdsgzsfh, Commented Aug 28, 2020 at 14:54

Barmar · Accepted Answer · 2020-08-27 15:07:36Z

1

In order to use a WHERE clause, you have to use a SELECT query for the data, not VALUES.

$sql = "INSERT INTO contact_sync_status (ghl_contact_email, ghl_contact_data, result, wp_user_id)
        SELECT '$source->email', '$payload', 'success', $user->id
        FROM DUAL
        WHERE NOT EXISTS (SELECT 1 FROM contact_sync_status WHERE ghl_contact_email = '$source->email' AND wp_user_id = $user->id)";

But it would definitely be better to add a unique index on (ghl_contact_email, wp_user_id) and then use INSERT IGNORE.

answered Aug 27, 2020 at 15:07

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

IncredibleHat Over a year ago

Now present that as a prepared statement :D

Barmar Over a year ago

I don't feel like doing that every time I answer a SQL question. As far as I'm concerned, the comment above is sufficient.

IncredibleHat Over a year ago

I agree, I was jesting more than being serious. I had to actually think about how to do placeholders correctly with that kind of query above, began to self-doubt myself, ran some tests, feel better now.

Collectives™ on Stack Overflow

PHP & MySQL - INSERT WHERE NOT EXISTS [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related