WordPress Plugin Tip – MySQL Transactions with $wpdb

This tip is more for WordPress theme and plugin developers. I’ve been working on a client project that required MySQL transactions, as I needed to perform multiple deletions on multiple tables, where the data was interdependent. Since I was writing a WordPress plugin, I wanted a way of doing a MySQL database transaction, using the WordPress database object $wpdb.

When you create a connection to a MySQL database, you end up with a connection handle. This is essentially a way of communicating with the database down the same pipe. When you render a page in WordPress (front end or admin area), the $wpdb database object has already been initialised and opened up a connection to the database. Therefore we can recycle this database connection for our own needs.

The $wpdb object saves the database handle as $wpdb->dbh (dbh = Database Handle). Therefore we just use $wpdb->dbh in place of our normal database handle that we’d otherwise use in PHP.

Code Example

<?php
global $wpdb;
 
// @ prefix used to suppress errors, but you should do your own
// error checking by checking return values from each mysql_query()
 
// Start Transaction
@mysql_query("BEGIN", $wpdb->dbh);
 
// Do some expensive/related queries here
$wpdb->query("DELETE FROM table WHERE form_id = '1' ");
$wpdb->query("DELETE FROM data WHERE form_id = '1' ");
 
if ($error) {
    // Error occured, don't save any changes
    @mysql_query("ROLLBACK", $wpdb->dbh);
} else {
   // All ok, save the changes
   @mysql_query("COMMIT", $wpdb->dbh);
}
 
?>

Based on this, you can do your usual PHP and WordPress magic to do what you need.