PHP PDO REPLACE works in phpadmin but not in php-CodePudding

Trying to update the record (timestamp) if it exists or insert a new record if it doesn't exist.

Table is:

id = int 12 primary key, auto increment
userid = int 12
viewerid = int 12
viewDateTime = TIMESTAMP

This sql works in phpmyadmin but not in php

SELECT @id := id FROM `profileViews` WHERE `userid` = 31 AND `viewerid` = 30 LIMIT 1;
REPLACE INTO `profileViews`(id, `userid`, `viewerid`, `viewDateTime`)
VALUES (@id, 31, 30, now());

Here is the php version:

$INSERTViewSQL = "SELECT @id := id FROM `profileViews` WHERE `userid` = ? AND `viewerid` = ? LIMIT 1;
REPLACE INTO `profileViews`(id, `userid`, `viewerid`, `viewDateTime`)
VALUES (@id, ?, ?, now());";

try{
    $DBConnection->prepare($INSERTViewSQL)->execute([$profileid, $_SESSION["id"], $profileid, $_SESSION["id"]]);
} catch(PDOException $e) {
    file_put_contents($ErrorLogFileForPDO, 'update view : ' .$e->getMessage()."\n", FILE_APPEND);
}

Here is the error message:

update view : SQLSTATE[42000]: Syntax error or access violation: 1064 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 'REPLACE INTO profileViews(id, userid, viewerid, viewDateTime) VALUES (@i' at line 2

Thanks‼️

CodePudding user response：

MySQL document says:

SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by ; characters).

So you need to fetch id value first, execute replace statement after that.

$stmt = $DBConnection
  ->prepare("SELECT id FROM ...");
$stmt->execute([$profileid, $_SESSION["id"]]);
$id = $stmt->fetchColumn();

$DBConnection
  ->prepare("REPLACE INTO ...");
  ->execute([$id, $profileid, $_SESSION["id"]]);