0

I'm trying to delete any existing rows with a specified column value if they exist and Insert a new row directly after in a single prepared statement. I have tried both binding param values and passing an array to execute();

$sql = "DELETE FROM table WHERE id = :id;
        INSERT INTO table (col1,col2,col3,col4,col5) VALUES (:col1,:col2,:col3,:col4,:col5);";

I have also tried:

$sql = "DELETE FROM table WHERE id = ?;
        INSERT INTO table (col1,col2,col3,col4,col5) VALUES (?,?,?,?,?);";

They work fine separately, but wont work in one statement. The exact error being thrown is:

PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO ...

The syntax error is pointing to the start of the second statement, so is it possible that I'm not supposed to be running a delete and insert statement after each other? I only have one version of mysql/mariadb installed and it's always been up to date, and the syntax works in separate statements. Operating system is Windows.

SakoMurad
  • 1
  • 1
  • 2
  • See https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd – Don't Panic Jun 22 '22 at 20:50
  • It is possible that way. If you need to be certain that they both run together, you should still use a transaction, though. – Don't Panic Jun 22 '22 at 20:53

1 Answers1

0

@Don'tPanic commented a link with a good answer: PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)

In my Database config model I had set the ATTR_EMULATE_PREPARES to false for some reason, and setting it to true fixed the issue and the statement executed just fine. The post in the link also advises to specify the encoding type in the dsn if you do choose to set the attribute to true.

SakoMurad
  • 1
  • 1
  • 2