CODEWITHSUNDEEP
phpmysql
Raf
Raf

Reputation: 708

MySql - get possible error results using PHP before query execution

I already reviewed most of the topics related to my concern

mysql explain delete?

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

Upvotes: 1

Views: 304

Answers (1)

EyasSH
EyasSH

Reputation: 3769

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();
}

Upvotes: 3

Related Questions