,Commit, Rollback in PHP.
In order to use transactions in MySQL your tables must be of type InnoDB or BDB.
This example uses InnoDB tables. I have set up a php object called MySQL which I
include in any page that requires a connection to MySQL. Here is the Object.
class mysqldb {
//set up the object
var $host;
var $db;
var $dbuser;
var $dbpassword;
var $sql;
var $numberrows;
var $dbopenstatus;
var $dbconnection;
var $qry;
var $result;
var $InsertSwitch;
/*
Use these functions to get and set the values of this object's
variables. This is good OO practice, as it means that datatype
checking can be completed and errors raised accordingly.
*/
// Property Get & Set
function gethost() {
return $this->dbhost;
}
function sethost($req_host) {
$this->dbhost = $req_host;
}
function getdb() {
return $this->db;
}
function setdb($req_db) {
$this->db = $req_db;
}
function getdbuser() {
return $this->dbuser;
}
function setdbuser($req_user) {
$this->dbuser = $req_user;
}
function getdbpassword() {
return $this->dbpassword;
}
function setdbpassword($req_password) {
$this->dbpassword = $req_password;
}
function getsql() {
return $this->sql;
}
function setsql($req_sql) {
$this->sql = $req_sql;
}
function getnumberrows() {
return $this->numberrows;
}
function setnumberrows($req_numberresults) {
$this->numberesults = $req_numberresults;
}
function setdbconnection($req_dbconnection) {
$this->dbconnection = $req_connection;
}
function getdbconnection() {
return $this->dbconnection;
}
function setInsertSwitch($switch) {
$this->InsertSwitch = $switch;
}
function getInsertSwitch() {
return $this->InsertSwitch;
}
/*
This is the constructor for the object. In this case I have set
the initial values of a number of the object properties to those
values declared in the global constants.inc. By doing this, I
only need to change the values of these properties for specific
operations, which we will not need to do throughout this example
*/
function mysqldb() {
global $HOST, $DB, $WEBUSER, $WEBPASSWORD;
global $TRUE, $FALSE;
$this->sethost($HOST);
$this->setdb($DB);
$this->setdbuser($WEBUSER);
$this->setdbpassword($WEBPASSWORD);
$this->setdbconnection($FALSE);
}
/*
These are the methods for the object. They provide for opening a
connection to the database, closing a connection and executing a
SELECT query. Of course, these can be expanded upon to allow for
INSERT's, UPDATE's and DELETE's etc...
*/
function opendbconnection() {
global $TRUE, $FALSE;
$this->dbconnection = mysql_connect("$this->dbhost", "$this->dbuser", "$this-
>dbpassword");
if ($this->dbconnection == $TRUE) {
$this->db = mysql_select_db("$this->db");
$this->setdbconnection($TRUE);
} else {
$this->setdbconnection($FALSE);
return false;
}
return true;
}
function closedbconnection() {
if ($this->dbconnection = $TRUE) {
mysql_close($this->dbconnection);
}
}
function begin( ) {
if ($this->dbconnection == $FALSE) {
$this->opendbconnection();
}
$this->setsql("BEGIN");
$this->qry = mysql_query($this->sql);
if (!$this->qry) {
return false;
} else {
return true;
}
}
function rollback( ) {
if ($this->dbconnection == $FALSE) {
$this->opendbconnection();
}
$this->setsql("ROLLBACK");
$this->qry = mysql_query($this->sql);
if (!$this->qry) {
return false;
} else {
return true;
}
}
function commit( ) {
if ($this->dbconnection == $FALSE) {
$this->opendbconnection();
}
$this->setsql("COMMIT");
$this->qry = mysql_query($this->sql);
if (!$this->qry) {
return false;
} else {
return true;
}
}
function selectquery() {
global $TRUE, $FALSE;
if ($this->dbconnection == $FALSE) {
$this->opendbconnection();
}
$this->qry = mysql_query($this->sql);
if (!$this->qry) {
return false;
} else {
$this->numberrows = mysql_num_rows($this->qry);
if ($this->numberrows > 0) {
for($x = 0; $x < $this->numberrows; $x++) {
$this->result[$x] = mysql_fetch_row($this->qry);
}
} else {
echo("[Error:] Retrieving data");
return false;
}
return true;
}
}
function insertquery() {
global $TRUE, $FALSE;
if ($this->dbconnection == $FALSE) {
$this->opendbconnection();
}
$this->qry = mysql_query($this->sql);
if (!$this->qry) {
return false;
} else {
return true;
}
}
}
?>
An example of using this would be :
$db1 = new mysqldb(); //create new instance of object.
$sql = "Select * test";
$db1->begin(); //let MySQL know that you are begining the transaction and that
nothing will
//be written to your tables until it recieves
the "Commit" command.
$db1->setInsertSwitch("true"); //I use a switch to determine if all inserts
worked.
for ($i = 0; $i < count($cart); $i++){
$insert_query = "INSERT INTO test VALUES('$var[$i]'...etc)
$db1->setsql($insert_query); //set the MySQL object sql string.
if (!$db1->insertquery()) //if the insert query fails...
$db1->setInsertSwitch("false"); //set it to false, a rollback will
need to be done.
}//end for
if($db1->getInsertSwitch() == "false") {
$db1->rollback(); //do the rollback.
echo "";
} else {
$db1->commit(); //commit the changes to the table.
echo "";
}
}//end if selectquery.
}
}
So that takes care of the php part, now to set up InnoDB in MySQL.
If you are using Linux open my.cnf, on windows open my.ini.
Add the following lines: //for windows...for linux just changes the dir paths to be
your own.
a note: the dir must be created, MySQL will not create them for you.
# You can write your other MySQL server options here
# ...
#
innodb_data_home_dir = c:\ibdata
# Datafiles must be able to
# hold your data and indexes
innodb_data_file_path = ibdata1:200M;ibdata2:200M
# Set buffer pool size to 50 - 80 %
# of your computer's memory
set-variable = innodb_buffer_pool_size=100M
set-variable = innodb_additional_mem_pool_size=10M
innodb_log_group_home_dir = c:\iblogs
# .._log_arch_dir must be the same
# as .._log_group_home_dir
innodb_log_arch_dir = c:\iblogs
innodb_log_archive=0
set-variable = innodb_log_files_in_group=3
# Set the log file-size to about
# 15 % of the buffer pool size
set-variable = innodb_log_file_size=10M
set-variable = innodb_log_buffer_size=8M
# Set ..flush_log_at_trx_commit to
# 0 if you can afford losing
# a few last transactions
innodb_flush_log_at_trx_commit=1
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
Now shut down the MySQL server and restart it using
your-path-to-mysqld>mysqld-max --standalone --console
MySQL will now create the appropriate files...and thats it. A note for heavy traffic
sites you will
need to increase the size of the ibdata file in the following line found in your
my.ini, or my.cnf
file.
"innodb_data_file_path = ibdata1:200M;ibdata2:200M"
And thats it! for more info here's the mysql page I used to get started.
http://www.mysql.com/doc/I/n/InnoDB_start.html