CODEWITHSUNDEEP
phpmysql
Peon
Peon

Reputation: 8030

Check if multiple queries were successful in mysql_*

I'm executing two different mysql queries in php,
but I also need to check if both of them where successful. If one of them failed, I need to throw an error and recall the changes made.

Is there a simple way to doing this?

PS: One is and update and the other one is an insert.

EDIT Currently I have two queries:

INSERT INTO table1 ( warning ) VALUES ( 0 ) WHERE con_id = 1
UPDATE table2 SET no_alerts = 1 WHERE con_id = 1

I'm using basic mysql_ inserts and updates, no PDO.

Upvotes: 1

Views: 1606

Answers (4)

PeeHaa
PeeHaa

Reputation: 72709

Is there a simple way to doing this?

Transactions is what you are looking for.

I'm using plain inserts and updates, no PDO.

There is no such thing as "plain inserts / updates". If you are talking about using the ancient mysql_* function please stop using them. They are no longer maintained and the community has begun the deprecation process. See the red box? Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide, this article will help to choose. If you care to learn, here is a good PDO tutorial.

If you are using PDO / mysqli you can make queries in transactions. This way you can easily rollback changes instead of committing them.

UPDATE

If you are worried about old systems (although PDO is 5.1.0+ and mysqli is 5+) there is something which you can do, but it is a hack and is prone to errors and things might get messed up. You could store the last insert id of the query and if the update query fails you can manually delete the inserted rows however as stated this is very much prone to errors on a concurrent system.

What could be the reason a query fails? Maybe there is a better way of doing things?

Upvotes: 2

Micah
Micah

Reputation: 96

Two ways I think. First off, If there's a chance of not being able to insert or update, I would check the condition of the table first to see if it's possible. If you require an atomic operation, you can lock the table during this process to make sure another process doesn't change the table in-between the time you check the table and do the updates. If you need high levels of concurrency this may slow things down though.

Alternatively, If you're using a transactional engine (InnoDB is transaction capable, MyISAM is not) you can start a transaction, then monitor the return values of your update and insert. 

START TRANSACTION

..then check the results of your operation. From the php docs:

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.

Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

If you are not successful you can ROLLBACK your changes:

ROLLBACK

Finally, without knowing more about your application I can't be sure, but you may be able to design the data set and application so that you won't be in this situation in the first place which would be faster. Transactions and locking tables are both slow, so I avoid them unless it's necessary.

Upvotes: 0

Fawad Ghafoor
Fawad Ghafoor

Reputation: 6217

echo($resultQuery1 && $resultQuery2) ? 'No error' : 'Error';

Upvotes: 0

Erik
Erik

Reputation: 2264

I'm guessing that you're looking for something like this

$result1 = mysql_query('INSERT INTO table1 ( warning ) VALUES ( 0 ) WHERE con_id = 1');
$result2 = mysql_query('UPDATE table2 SET no_alerts = 1 WHERE con_id = 1');

if(!$result1 || !$result2) {
  echo 'error';
}

You should be using PDO... mysql_query is deprecated.

Upvotes: 0

Related Questions