We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Return last updated ID

I am performing an update to a row dependent on a condition and need to return the ID of the row affected.

This is what I have tried

$update =   "UPDATE table_1
SET delete_flag = 1,
id = LAST_INSERT_ID(id)
WHERE other_id = $otherId;
SELECT LAST_INSERT_ID();";

$result = $this->db->query($update);

$result returns nothing

$update =   "UPDATE table_1
SET delete_flag = 1
WHERE other_id = $otherId;";

$result = $this->db->query($update);
$id = $this->db->lastInsertId();

$id returns nothing

What am I missing?



6.9k

Have you tried using execute instead of query?

$update =   "UPDATE table_1
SET delete_flag = 1
WHERE other_id = $otherId;";

$result = $this->db->execute($update);
$id = $this->db->lastInsertId();


13.7k

Hi,

Unfortunately $id returns 0;

It works fine for inserts.

Tim



98.9k

Update statements does not return a "last insert id", database drivers only return it for inserts



13.7k

Sure, but it is possible and very useful to avoid unncessary queries.

Here are a few strategies:

https://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql

But I guess you've answered my question... It is not possible to do using Phalcon.



98.9k

No, it's not possible with PDO



13.7k

Very helpful!



13.8k

Hello! I'm wondering about which is the ODM way to do what the OP does.