0

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.

cswebd3v
  • 13
  • 1
  • 1
    [Little Bobby](http://bobby-tables.com/) says [you may be at risk for SQL Injection Attacks](https://stackoverflow.com/q/60174/). Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) with [parameterized queries](https://stackoverflow.com/a/4712113/5827005). – GrumpyCrouton Aug 27 '20 at 15:03
  • 1
    Doing a completely separate query to see if it exists first would work. – GrumpyCrouton Aug 27 '20 at 15:04
  • 3
    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 Aug 27 '20 at 15:05
  • You can *not* guarantee duplicate prevention by doing a select first. – Alex Howansky Aug 27 '20 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? – cswebd3v Aug 28 '20 at 14:54

1 Answers1

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.

Barmar
  • 596,455
  • 48
  • 393
  • 495
  • Now present that as a prepared statement :D – IncredibleHat Aug 27 '20 at 15:09
  • 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. – Barmar Aug 27 '20 at 15:15
  • 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. – IncredibleHat Aug 27 '20 at 15:16