Undo MySQL Queries

Dev Shed Forums Sponsor:

Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!

May 7th, 2008, 05:17 AM

homerdooh

php newbie

Join Date: Nov 2001

Posts: 505

Time spent in forums: 3 Days 3 h 11 m 5 sec
Reputation Power: 11

PHP-General - Undo MySQL Queries

Hi,

If you have many mysql update/insert/delete queries in a page how do you make sure they are all not executed if one of them fails.

E.g.

You have mysql queries..

insert
update
insert
insert
insert
insert
update

What if the 4th query fails for some reason, e.g. timeout, server crash, is there a way to make the first 3 queries "undo".

I know you can do mysql_affected_rows() and die() if the queries fail and the subsequest queries will not run but is there a way to affect the queries run before that?

Thanks.

__________________
"They have the internet on computers now" - Homer

May 7th, 2008, 05:40 AM

SimonJM

Contributing User

Join Date: Mar 2006

Posts: 609

Time spent in forums: 1 Week 4 Days 23 h 38 m 37 sec
Reputation Power: 194

I think for that you'd need transactions, and that would require tables to be all innodb.

Comments on this post

ManiacDan agrees!

lnxgeek agrees!

__________________
"Do not meddle in the affairs of cats, for they are subtle and will pee on your computer" - Bruce Graham

May 7th, 2008, 05:50 AM

PranK

Magical Liopleurodon

Join Date: Mar 2004

Location: Sydney, Australia

Posts: 569

Time spent in forums: 1 Week 2 h 2 m 47 sec
Reputation Power: 79

As simon said, look into transactions.

Basically, before all your queries are run, you start a transaction. What happens within the transaction can be looked at like a 'dry run' or a 'cache'. You can see the results, but they havent been properly applied. If there is an error, the transaction automatically rolls back, otherwise you can try to catch an error and rollback yourself. If all goes well, you can commit the transaction to make the changes final.

clicky

Comments on this post

lnxgeek agrees!

__________________
Regards,

Christian Biggins - Blogger Extraordinaire | Skylines Australia Owner | PHPOnIn Developer* | Good Question Asker Appreciater

* Work in Progress.

Scheduled Version 1.0 release 01/04/2008

May 7th, 2008, 10:05 AM

homerdooh

php newbie

Join Date: Nov 2001

Posts: 505

Time spent in forums: 3 Days 3 h 11 m 5 sec
Reputation Power: 11

Thanks, much to read. Have any of you guys implemented this?

May 7th, 2008, 10:16 AM

Veamon

Blowing up trees with my mind.

Join Date: Feb 2005

Location: Neverland Ranch, minus the touching.

Posts: 2,604

Folding Points: 117515 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 3 Weeks 3 Days 7 h 41 m 41 sec
Reputation Power: 822

Quote:

Originally Posted by homerdooh

Thanks, much to read. Have any of you guys implemented this?

Yup. Works fine.

__________________

Raid1 in XP Pro

My open source projects:
------------------------
Blobber - Add images as blobs to SQL Server
------------------------

May 7th, 2008, 04:51 PM

PranK

Magical Liopleurodon

Join Date: Mar 2004

Location: Sydney, Australia

Posts: 569

Time spent in forums: 1 Week 2 h 2 m 47 sec
Reputation Power: 79

Quote:

Originally Posted by homerdooh

Thanks, much to read. Have any of you guys implemented this?

Easy.

sql Code:

Original - sql Code
ALTER TABLE tablename ENGINE=InnoDB; START TRANSACTION; UPDATE tablename SET col='val' WHERE criteria='true'; SELECT * FROM tablename WHERE criteria='true'; -- Check your rows. -- If all good; COMMIT; --If not so good; ROLLBACK;

 
ALTER TABLE tablename ENGINE=InnoDB;
 
START TRANSACTION;
 
UPDATE tablename SET col='val' WHERE criteria='true';
 
SELECT * FROM tablename WHERE criteria='true';
 
-- Check your rows.
-- If all good;
COMMIT;
 
--If not so good;
ROLLBACK;

I know a lot of the features in MySQL can be daunting. But just get your hands dirty, have a play. No better way to learn and its a lot easier than it seems.

May 7th, 2008, 07:04 PM

Hammer65

Web Developer/Musician

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Nov 2004

Location: Lincoln Nebraska

Posts: 1,927

Time spent in forums: 3 Weeks 19 h 8 m 50 sec
Reputation Power: 500

There are several PHP based database abstractions that make this as easy as it is going to get.

__________________
"Strange women lying in ponds distributing swords is no basis for a system of government. Supreme executive power derives from a mandate from the masses, not from some farcical aquatic ceremony! Well, but you can't expect to wield supreme executive power just 'cause some watery tart threw a sword at you! I mean, if I went 'round saying I was an emperor just because some moistened bint had lobbed a scimitar at me, they'd put me away!"

May 8th, 2008, 02:30 AM

SimonJM

Contributing User

Join Date: Mar 2006

Posts: 609

Time spent in forums: 1 Week 4 Days 23 h 38 m 37 sec
Reputation Power: 194

Quote:

Originally Posted by homerdooh

Thanks, much to read. Have any of you guys implemented this?

Yes, but only in IDMS/R on a mainframe

May 8th, 2008, 09:23 AM

ManiacDan

Beware

Join Date: Oct 2006

Location: Texas, USA

Posts: 1,435

Time spent in forums: 1 Week 6 Days 16 h 53 m 15 sec
Reputation Power: 176

The ADODB database library for PHP supports transactions in MySQL, oracle, and a number of others. Transactions are as easy as:

PHP Code:


		
			
 $db->begin();

$db->query();

$db->query();

$db->query();

$db->query();

if ( $db->failed() ) {

   $db->rollback();

} else {

   $db->commit();

}

Or something like that. Been a while since I've used it.

-Dan

__________________
"They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

"The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

Viewing: Dev Shed Forums > Programming Languages > PHP Development > PHP-General - Undo MySQL Queries

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!