CODEWITHSUNDEEP
mysql
rubberchicken
rubberchicken

Reputation: 1322

mysql if exists giving error wrong syntax?

I am trying to use IF EXISTS in MySQL but i keep getting syntax errors and I have researched for correct syntax but everything isnt working...

What i need is: If query exists then UPDATE else INSERT new...

$queryString =  "IF EXISTS (SELECT * FROM $ONCALL_TABLE WHERE uid='$contextUser' AND submitid='$submitid' AND submitstatus=3) THEN UPDATE $ONCALL_TABLE SET uid='$contextUser', start_time='$onStr', end_time='$offStr', amount='$amount' ELSE INSERT INTO $ONCALL_TABLE (uid, start_time, end_time, amount) VALUES ('$contextUser','$onStr', '$offStr', '$amount') END IF";

Error message: Can't perform query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS (SELECT * FROM timesheet_oncall WHERE uid='admin' AND submitid='136545' at line 1

Upvotes: 2

Views: 379

Answers (1)

Bart
Bart

Reputation: 17361

REPLACE INTO is what you need. http://dev.mysql.com/doc/refman/5.0/en/replace.html

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

In your case

REPLACE INTO 
$ONCALL_TABLE (uid, start_time, end_time, amount) 
VALUES ('$contextUser','$onStr', '$offStr', '$amount')
WHERE uid='$contextUser';

Assuming uid is a PRIMARY KEY or UNIQUE KEY

NOTE: Since the code in your question contains SQL injection flaws I would recommend you to read this article. http://php.net/manual/en/security.database.sql-injection.php

Upvotes: 2

Related Questions