Code Examples -> Setting up InnoDB on MySQL and using Transactions Begin, Commit, Rollback in PHP.

	Submit Your Code
	SEO Monitor
	UpTime Monitor
	PHP Code Contest
	My Favorite Examples
	My Favorite Articles
	Update Your Profile

More Weber Sites
	PHP Code Search
	Web Development Forums
	Learn MySQL Playing Trivia
	PHPBB2 Templates
	Web Development Index
	PHP Web Logs (BLogs)
	Web Development Resources
	Web Development Content

Recommended Links
	PHPClasses
	PHP Editor
	PHP Jobs
	Vision.To Design
	Ajax Tutorials
	PHP Programming Help
	PHP/MySQL Programming
	Webmaster Resources
	Webmaster Forum
	XML meta language
	website builder

Recommended
	Submit Site
	Forex Trading Online forex trading platform


BACK		ADD A COMMENT		SEND TO A FRIEND		ADD TO MY FAVORITES		ADD CODE EXAMPLES		PRINT

PHP Code Examples

Title :	Setting up InnoDB on MySQL and using Transactions Begin, Commit, Rollback in PHP.
Categories :	PHP Classes, Databases, PHP, MySQL, InnoDB	Steve Bradwell
Date :	Jun 24th 2002
Grade :	4 of 5 (graded 9 times)
Viewed :	15670
File :	No file for this code example.
Images :	No Images for this code example.

Search :	More code by Steve Bradwell
Action :	Grade This Code Example
Tools :	My Examples List

Code

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 "<script language=\"JavaScript\">alert(\"Order could not be
created, please
try again.\")</script>";
} else {
$db1->commit(); //commit the changes to the table.
echo "<script language=\"JavaScript\">alert(\"Your Order has been
placed
Successfully.\")</script>";
}
}//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

Related Code Examples

	Powerful php/mysql Pagination for up to 6 URL Params Categories : PHP, PHP Classes, Databases, MySQL, Navigation
	Password reminder Categories : PHP, PHP Classes, Databases, MySQL, Mail
	MySQL Class to ease Database connectivity Categories : MySQL, PHP Classes, Databases, PHP
	usercounter class Categories : PHP, PHP Classes, Databases, MySQL, Environment Variables
	MySQL Handler Categories : PHP, Databases, MySQL, Classes and Objects, PHP Classes
	PostGreSQL and MySQL 2 in 1 db Manager Categories : PHP, PHP Classes, Databases, PostgreSQL, MySQL
	Simple Mini Poll class library (SimPoll) Categories : PHP, PHP Classes, Databases, MySQL, Complete Programs
	Online Automatic Class Generator for MySQL Tables Categories : PHP, PHP Classes, Classes and Objects, Databases, MySQL
	Specify your connection settings and create a link to a MySQL database. Categories : PHP, PHP Classes, Databases, MySQL, Beginner Guides
	Simple database class Categories : PHP, PHP Classes, MySQL, Databases
	Simple usersOnline class - keep track of how many users are online on your site Categories : PHP, PHP Classes, Databases, MySQL
	Ajax PHP Tree (Left and Right) with MySQL Categories : PHP, Databases, MySQL, AJAX, PHP Classes
	YellowPages Content Grabber (PHP5 +) Categories : PHP, PHP Classes, Regexps, Databases, MySQL
	MySQL Connection/Query Class Categories : Databases, MySQL, PHP, PHP Classes
	This program allows you to upload an ODBC ressource - i.e. an MS-Access database to a MySQL server. Categories : Databases, MySQL, Complete Programs, PHP, Databases

User Comments

	Vamshidhar mt wrote :834 Hai Steve U R code is very intresting and I am very found of such cooding styles which U used and I have send this code to many of my friends and even they are impressed with this one. I have some problem steve I am unable to use mysql_connect() method and I am getting some message like this, "Fatal error: Call to undefined function: mysql_connect() in /var/www/html/DataBase.php on line 9" I am able to work with php using apache server from linux. So if U can help me solving this problem I would be thankfull to U. Is there any way I can contact U online Using Yahoo or such pl mail me the details.