rollback mysql transactions 1

[mufka]

I have a form that is used to insert 34 rows into one mysql table. I need some trick that will back out all of the inserts if any one has an error.

Here is my current insert code:

$rownumber = 34;
$count = 1;

while ($count <= $rownumber) {
				$firstname = $_POST["firstname-$count"];
				$lastname = $_POST["lastname-$count"];
				$writetable = "name_list";
				$writefields = "name_list_firstname, name_list_lastname";
				$writevalues = "'$firstname','$lastname'";
				$sql="INSERT INTO $writetable ($writefields)
				VALUES
				($writevalues)";
				if (!mysql_query($sql))
					{
						die('Error: ' . mysql_error());
				}
print ("Record $count entered");
print ("<br />");
				$count = ++$count;
}

As it is currently, it will insert rows until it hits an error and then stop. I need it to undo all the previous inserts if there are any errors.

 

[southbeach]

I would start by logging your inserts in a 2nd table or an array and in the event of error, loop through stored inserts references and delete them.

Of course, part of the logging would have to include the unique ID for the inserted row.

Hope this points you in the right direction!



--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[feherke]

Hi

I would say, this is not related to PHP. Read MySQL Transactional and Locking Statements in the MySQL documentation and do it as described there.

Feherke.

http://rootshell.be/~feherke/

 

[jpadie]

following feherke's link, ensure that you are using InnoDB for your database tables, and not MyISAM.