Pdo returns true for multiple requests, even if one of them fails

Question

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

The documentation says true if false false on error. So what happens and fails in MySQL?

Here is my current question: I send this request together in one PDO execution and return 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 returns correctly.

NB NULL is for testing purposes only, which are usually replaced with @id.

I tried to see the PDO behavior in more detail:

 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 does not work, and PDO returns true. I think PDO returns true when only one request is successfully completed.

Can you explain this to me, so I know how to deal with my database. because I based my entire 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') 

Answer 1

I see that PDO does not support the fact that it always returns true if the first statement is independent of other requests. I still see that the error is for correction, because in my point of view coding will be easier and probably faster if we give what php for php and what for sql for mysql. I will give the following example here to see where it would be easier to code many requests together, if 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 prepares the statement here, executes it and displays an error (if any for debugging will be disabled later). and when all requests succeed, he commits them. otherwise rollback

Well, to be true, this cannot be done. due to which each of these requests must be sent and checked individually with a lot of 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 required from the first request to the second. with php it needs to be requested with extraction, and then inserted back into the second line. I am not an expert in php or sql, maybe I'm the only one who thinks so. I just like to have a common function that I can rely on to do everything together so that I can pass on other problems. This may not be a very good way, but I came up with this job:

  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 the transaction, then loop the skipped table and send each request .. if there is any failure, it will break and roll back. If everything goes well, he commits.

I look forward to suggesting how effective this is.