shaddf Posted December 3, 2015 Share Posted December 3, 2015 is it possible to insert and delete from the same table using the same transaction in mysql. something like: START TRANSACTION; DELETE FROM Al where vc='frdehy' ; INSERT INTO Al(vc,zzz ,subj_id) VALUES('bgo' ,'mij','rey'); commit; Quote Link to comment Share on other sites More sharing options...
dalecosp Posted December 3, 2015 Share Posted December 3, 2015 (edited) Yes. Here's one way; $Q is an array of queries. function transaction ($con, $Q){ mysqli_query( $con, "START TRANSACTION" ); for ( $i = 0; $i < count( $Q ); $i++ ) { if ( !mysqli_query ( $con, $Q[$i] )) { echo 'Error! Info: <' . mysqli_error ($con) . '> Query: <' . $Q[$i] . '>'; break; } } if ( $i == count ( $Q )) { mysqli_query( $con, "COMMIT" ); return 1; } else { mysqli_query( $con, "ROLLBACK" ); return 0; } } Edited December 3, 2015 by dalecosp Quote Link to comment Share on other sites More sharing options...
Solution gizmola Posted December 3, 2015 Solution Share Posted December 3, 2015 Yes you can enclose whatever you want in a transaction. That is the point of them -- and they are of course not limited to one table. You could insert in table A, Delete from Table B, and update table C, all inside one transaction. The important thing to know is that with mysql your tables need to be of an engine type that supports transactions. Most people have traditionally used Innodb engine tables for this requirement. Quote Link to comment Share on other sites More sharing options...
shaddf Posted December 4, 2015 Author Share Posted December 4, 2015 Yes. Here's one way; $Q is an array of queries. function transaction ($con, $Q){ mysqli_query( $con, "START TRANSACTION" ); for ( $i = 0; $i < count( $Q ); $i++ ) { if ( !mysqli_query ( $con, $Q[$i] )) { echo 'Error! Info: <' . mysqli_error ($con) . '> Query: <' . $Q[$i] . '>'; break; } } if ( $i == count ( $Q )) { mysqli_query( $con, "COMMIT" ); return 1; } else { mysqli_query( $con, "ROLLBACK" ); return 0; } } both answers are good Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.