PHP and MySQL All or noting

Ancel

I'm updating multiple tables and don't know how make sure all of my queries go through. If one out of ten queries failed there would be database integrity problems.
How do i make sure none of the queries go through if one failed?

ChRoMe

Ancel wrote: »

I'm updating multiple tables and don't know how make sure all of my queries go through. If one out of ten queries failed there would be database integrity problems.
How do i make sure none of the queries go through if one failed?

Read about MySQL transactions, they do exactly what you are looking for.

Ancel

ChRoMe wrote: »

Read about MySQL transactions, they do exactly what you are looking for.

Perfect Thanks

seamus

The mysql_query() function returns a value.

Regardless of the type of query, it returns FALSE if the query failed.

So for every query, execute

$result = mysql_query($query, $link);

Then immediately after, check

if($result) {
// Start on the next query
} else {
// Handle the error
}

There is also a function mysql_error() which will retrieve the error text from the last query. If the query was successful, mysql_error() gives an empty string. So that's another way of checking if the last query worked.


Edit:

Transactions are more appropriate in stored procedures and triggers. You typically wouldn't use transactions within PHP code because mysql_query() can only execute one statement at a time and not an entire block.

Ancel

@seamus

How would i rollback the changes if I'm 4/10 queries down and the 5th fails?

EDIT:
http://www.daniweb.com/web-development/php/threads/152659
I think R0bb0b has the idea.

ocallagh

@seamus, there are quite a few ways to use transactions within PHP.

mysqli which is generally available is a simple way to use it but also available with mysql. You still use a bunch of connections to the server though (which is why stored procedures or triggers are better), but they will still work fine.

seamus

It's probably just personal preference on my part.

I prefer to do error/flow control exclusively in PHP or MySQL, or both (if you need high integrity), but not a mix of SQL code in PHP.

In the above scenario where 10 tables need to be updated or rollback on failure, I'd be inclined to implement a stored procedure and let MySQL manage any issues.

But as I say, just personal preference probably.

[Edit: And yes, I missed the last line of the OP, so transactions are the way to go with this, but I'd use SPs]

ChRoMe

seamus wrote: »

It's probably just personal preference on my part.

I prefer to do error/flow control exclusively in PHP or MySQL, or both (if you need high integrity), but not a mix of SQL code in PHP.

In the above scenario where 10 tables need to be updated or rollback on failure, I'd be inclined to implement a stored procedure and let MySQL manage any issues.

But as I say, just personal preference probably.

Agreed, where possible the most amount of work should be done on the database.