Syntax issue writing conditional mySQL query in PHP

Question

I have the following mySQL query written in the format of a PHP PDO script. I made sure to verify that all of the columns I refer to in the query exist.

So the issue seems to do with the syntax of the query itself. When executing the query in POSTMAN I see the issue seems to be where the if statement beings.

The following is the query:

   $stmt = $conn->prepare('IF EXISTS (SELECT * `Table1` WHERE `code`= :code )
    UPDATE `Table1`
    SET `code_stat` = 2
    WHERE code = :code
ELSE
    INSERT INTO `Table1` (`code`,`code_stat`)
    VALUES (:code, 2 ) ' );

                $stmt->execute([
                'code' => $_POST['code']
                ]);

You could try `REPLACE INTO` instead of `INSERT INTO` and simplify your query quite a bit. https://dev.mysql.com/doc/refman/5.7/en/replace.html — Hayden, Aug 17 '19 at 05:41
@Dharman I get the following error: `SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS (SELECT * FROM `Table1 WHERE `code`= ? ) UPDATE `Table1' at line 1` — John, Aug 17 '19 at 20:05
Possible duplicate of [Insert into a MySQL table or update if exists](https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) — Dharman, Aug 17 '19 at 20:09
Alot of people are referring me to the ON DUPLICATE KEY method the problem with this is the column `code` is not the primary key it is an index key..how would this method work with index key? — John, Aug 17 '19 at 20:14
@Dharman Does the query look for duplicates in either the primary key or the index key columns? — John, Aug 17 '19 at 20:19
RTM: https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html *"UNIQUE index or PRIMARY KEY"* — Dharman, Aug 17 '19 at 20:20

score 0 · Answer 1 · edited Aug 17 '19 at 08:11

0

Think you're just missing the ‘FROM’.

SELECT * `Table1` WHERE

should be

SELECT * FROM `Table1` WHERE

edited Aug 17 '19 at 08:11

Dharman

30,962
25
85
135

answered Aug 17 '19 at 05:45

user2144363

37
2
5

score 0 · Answer 2 · answered Aug 17 '19 at 05:50

Alternative query:

$stmt = $conn->prepare("REPLACE INTO `Table` (`code`,`code_stat`) VALUES (:code,2)");

$stmt->execute(['code' => $_POST['code']);

If the row does not exists, it will be inserted otherwise it will be updated.

https://dev.mysql.com/doc/refman/5.7/en/replace.html

Syntax issue writing conditional mySQL query in PHP

2 Answers2