1

I'm getting an (SQLSTATE[HY000]: General error) trying to run the following query using PDO to MySQL.

$updateQuery = "UPDATE user SET email = '$email', id = LAST_INSERT_ID(id) WHERE other_id = '$otherId';
                SELECT LAST_INSERT_ID() as updated_id;"

        $user = $connection->query($updateQuery);
        $user->setFetchMode(PhDb::FETCH_ASSOC);
        $userRec = $user->fetchAll();

I understand that normally Update queries don't return anything so therefore the widely accepted solution is to not attempt to return the results.

However as you can see I am returning the last updated row ID and the query works correctly when I directly query the DB.

Is this a PDO bug?

Tim
  • 3,091
  • 9
  • 48
  • 64
  • 1
    Does it works if you executes the query's alone like: 1. `UPDATE user SET email = '$email', id = LAST_INSERT_ID(id) WHERE other_id = '$otherId';` and 2. `SELECT LAST_INSERT_ID() as updated_id;` so you don't call them together? – Rizier123 Jan 01 '15 at 23:32
  • What should your update query do ? id = LAST_INSERT_ID(id) doesn't make much sense. – Lorenz Meyer Jan 01 '15 at 23:35
  • @LorenzMeyer sure it does: http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql – Tim Jan 01 '15 at 23:36
  • MySQL doesn't allow this kind of queries in some cases. Read up here: http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd – Mouser Jan 01 '15 at 23:38
  • @Rizier123 it looks like it does work. But it means I need to do two separate database calls rather than just two transactions... right? – Tim Jan 01 '15 at 23:40
  • @Mouser Mysql supports this just fine... As i mentioned in my question, I can run this query directly on the DB and it works correctly. – Tim Jan 01 '15 at 23:41
  • That was not Mouser's point. Does it work if you issue just `SELECT 1; SELECT 2;` in the same call? Also, in your reply to Rizier123, writing "transaction" instead of "query" was a typo wasn't it? – RandomSeed Jan 02 '15 at 01:15
  • @RandomSeed, yes thanks that was a typo. And yes the PDO calls do work if I issue two SELECT statements together, just not when there is an UPDATE. I did try rewriting the PDO call to use exec but it would only return the number of affected rows, not the actual last updated id. – Tim Jan 02 '15 at 01:22
  • *the query works correctly when I directly query the DB* No, it actually doesn't. That's an illusion provided by the `mysql` command line client. It splits statements on the `;` delimiter and sends them to the server separately for execution. You should also read up on SQL injection, and on securing your configuration so that multiple statement queries is impossible. Imagine what happens if $otherId contains `0'; DROP TABLE some_important_table; --`. Any perceived convenience or efficiency gain is not worth the risk. – Michael - sqlbot Jan 02 '15 at 23:01
  • @Michael-sqlbot Interesting information. I've moved on and am using two separate queries. The code I posted is obviously not my real code. ANY user input in my application goes through a filtering process before it gets anywhere near the database call. – Tim Jan 02 '15 at 23:21

0 Answers0