MySql - get possible error results using PHP before query execution

Question

I already reviewed most of the topics related to my concern

detect mysql errors without executing it

Most of the examples are just using Select statements. Mine is about UPDATE and DELETE.

Is there a way to get the possible error return using PHP to check the update/delete query before execution?

My concern is about foreign key constraint.

I already have Php script before but now I guess will try to use EXPLAIN.

Before I was using mysql_error() to get the errors but of course the query must be executed to get errors and translate it to understandable message.

Lets say, how I can I use "Explain Delete ..." statement to get some information about errors?
or is "Explain" the right tool?

Query: Delete From project .

I have another table called project_assignment with index FK to Project_ID of project table and has data on it.

Right now, I only got this

id  | select_type | table | type |  possible_keys | key |   key_len |   ref |   rows |  Extra
1   |SIMPLE       |NULL   | NULL |  NULL          | NULL|   NULL    |   NULL|2       |  Deleting all rows

How can something know if it will get an error until it tries? — Barmar, May 05 '15 at 03:56
`EXPLAIN` is for showing how the query optimizer plans to perform the query. Why would you expect it to have anything to do with detecting errors? — Barmar, May 05 '15 at 03:57
@Barmar .. I was thinking that as well but as I've mentioned above, some of the comments on related topics states about using Explain.. and that confuses me on how to apply or use it. So, you mean its not possible to use Explain? thnx — Raf, May 05 '15 at 04:00
The other question is just talking about detecting syntax errors, not errors related to the data. — Barmar, May 05 '15 at 04:05
okay.. got it. Explain is not the right tool to do my concerns. — Raf, May 05 '15 at 04:28

score 3 · Accepted Answer · edited May 23 '17 at 10:26

EXPLAIN is not the right tool for the job.

Take a look at using MySQL Transactions. This allows you to execute a series of queries 'optimistically' then commit the transaction. When you do so, you will be informed of any errors. If an error occurs, the entire transaction would be rolled back.

I imagine most uses for 'explain' before an UPDATE or DELETE query could be solved by transactions.

In pure SQL, this looks like:

START TRANSACTION;
SELECT ...;
UPDATE ...;
DELETE ...;
COMMIT;

Using PHP mysqli, you can use mysqli::begin_transaction, mysqli::commit, and mysqli::rollback.

This excellent StackOverflow answer, PHP + MySQL transactions examples shows how to use transactions in mysqli. The answer explains how to write a transaction in PHP mysqli like so:

try {
    $db->beginTransaction();

    $db->query('SELECT ...');
    $db->query('UPDATE ...');
    $db->query('DELETE ...');

    $db->commit();
} catch (Exception $e) {
    $db->rollback();
}

MySql - get possible error results using PHP before query execution

1 Answers1