pdo returns true on multiple queries even if one of them fails

Question

The deep question here is to know when PDO returns true and when false.

Documentation says true if succeed false on fail. So what is succeed and fail in MySQL?

Here is my current question: I'm sending this query all together in one PDO execute and it returns true even foreign_id should not be NULL:

SET @id := NULL; SELECT id INTO @id FROM ? WHERE id = ? AND to_user = ?;
INSERT INTO hidden_notifications(table_name, foreign_id) VALUES (?, NULL);

but if I send only

INSERT INTO hidden_notifications(table_name, foreign_id) VALUES (?, NULL);

false is correctly returned.

N.B. NULL is only for testing purpose normally it's replaced by @id.

I tried further to see the behaviour of PDO:

INSERT INTO hidden_notifications(table_name, foreign_id) VALUES (?, ?);
INSERT INTO hidden_notifications(table_name, foreign_id) VALUES (?, NULL);

Here the first insert is correct the second not and PDO returns true.. I'm getting to think that PDO returns true when only one query succeeds.

Can you please explain that to me so I know once for all how to deal with my database. because I based all my program on this principle:

execute('BEGIN')
execute(myquery) // which can be many selections, insertions updates together in one string
if succed execute('COMMIT')
else execute('ROLLBACK')

I would assume that like any other database adaptor PDO only executes the first statement. The rest is ignored, thus does not throw an error. How else should parameter binding work? — arkascha, Aug 02 '15 at 08:41
Use PDO library for transactions http://php.net/manual/en/pdo.begintransaction.php — Mihai, Aug 02 '15 at 08:45
@arkascha actually it works we can send multiple query and all are executed. but pdo returns only false if the first query fails. — fekiri malek, Aug 02 '15 at 08:58
it seems to be a bug in pdo. i don't know if they fixed it. https://bugs.php.net/bug.php?id=61613.. can some one tell me if there is a work around? i think it should be faster to run query together instead of sending each one separate.. — fekiri malek, Aug 02 '15 at 09:12
I still doubt it is a good idea to accept multiple statements at once, although apparently it is not explicitly prevented, like in other database adapters. I wonder why. This opens security issues, especially it makes code vulnerable to serious sql injections if it is not escaped perfectly. I see no point in that. The overhead of sending the statements separate through an open and idle socket should be trivial against that security thread. Only my 2 cents... — arkascha, Aug 02 '15 at 09:27
Your assumption that sticking multiple queries in one go is faster than executing multiple ones one after another is **wrong**, therefore the problem you found is nonexistent. Suggestion #1 - post the actual code you use. Suggestion #2 - it's pointless to optimize with assumptions that you are not sure about. — N.B., Aug 02 '15 at 11:45

fekiri malek · Answer 1 · 2015-08-02T21:52:25.603

i see PDO does not support that it always return true if the first statement succeeds independent from the other queries.. i still see that a bug to correct because in my point of view coding will be lighter and probably faster if we give what to php to php and what to sql to mysql. i give here the following example to see where it could be easier to code many queries together if it was possible:

pdoQuery("INSERT INTO users SELECT * FROM temp_users WHERE user_id = ? LIMIT 0, 1;
          DELETE FROM temp_users WHERE user_id = ?;
          DELETE FROM sign_up_confirm_urls WHERE user_id = ?;
          INSERT INTO actions(user_id, code, foreign_id) VALUES (?, ?, ?);",
          Array($user_id, $user_id, $user_id, $user_id, $user_id, $actions_ini['create_account'], $user_id));

pdoQuery here prepares the statement executes it and displays error (if any for debug will be disabled later). and when all the queries succeeds it commits them. otherwise rollback

Well to good to be true, this can't be done. because of that each of those queries has to be submitted and checked individually with more code..

or in this case

SET @id := NULL; SELECT id INTO @id FROM ? WHERE id = ? AND to_user = ?;
                 INSERT INTO hidden_notifications(table_name, foreign_id) VALUES (?, NULL);

where the variable is needed from the first query to the second. with php it has to be requested with fetch and than inserted back in the second line. i'm not expert neither in php nor in sql may be i'm the only one thinking like. i just like to have general function that i can rely on to do stuff all together so i can pass on other problems.. may be it not a good way but i have come with this work around:

 pdoQuery(array(
            array("INSERT INTO users SELECT * FROM temp_users WHERE user_id = ? LIMIT 0, 1;", array($user_id)),
            array("DELETE FROM temp_users WHERE user_id = ?;", array($user_id)),
            array("DELETE FROM sign_up_confirm_urls WHERE user_id = ?;", array($user_id)),
            array("INSERT INTO actions(user_id, code, foreign_id) VALUES (?, ?, ?);", array( $user_id, $actions_ini['create_account'], $user_id))
);

pdoQuery will send a transaction begin then loop through the passed table and send each query.. if any fail it breaks and make a roll back.. if all goes good it commits.

i'm waiting for suggesstion about how efficient this is

score 0 · Answer 2 · answered Mar 16 '19 at 07:17

0

This (this link: PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)) solution helps you to run multiple statement together, If an incorrect a statement occurs, it does not execute any other statement

answered Mar 16 '19 at 07:17

Sajad Mirzaei

2,635
1
11
13

pdo returns true on multiple queries even if one of them fails

2 Answers2