John Davidson

Message:

Working on Phalcon 4.x with PHP 7.x, I am trying to do a PHQL query for INSERT.

$phql = "INSERT INTO example

(

    user_id,

    answer,

    date_last

) VALUES (

    :user_id:,

    :answer:,

    NOW(),

) ON DUPLICATE KEY UPDATE answer = :answer:, date_last = NOW()";

Using modelsManager

$records  = $this->modelsManager->executeQuery(

    $phql,

    [

        'user_id'       => $parameters['user_id'],

        'answer'       => $parameters['answer']

    ]

);

However, Phalcon is throwing the following error.

Syntax error, unexpected token ON, near to ' DUPLICATE KEY UPDATE
answer = 1, date_last = NOW()', when parsing: INSERT INTO

Anyone from Phalcon community that might know if this is possible?

Thanks

---

My Answer:
Phalcon's PHQL does not support the ON DUPLICATE KEY UPDATE syntax for INSERT queries. You will need to handle this functionality using raw SQL queries or by implementing the logic in your application code.

You can use the query builder or raw SQL queries to achieve the desired functionality. Here is an example using raw SQL query:

php
$sql = "INSERT INTO example (user_id, answer, date_last)
VALUES (:user_id, :answer, NOW())
ON DUPLICATE KEY UPDATE answer = :answer, date_last = NOW()";

$connection = $this->getDI()->get('db');
$connection->execute($sql, [
'user_id' => $parameters['user_id'],
'answer' => $parameters['answer']
]);


This way, you can achieve the desired functionality of inserting a new record or updating an existing record if a duplicate key is found.