Transactions not working for mysql using php

[realin]

Hiya all,

after my 1-2 weeks research i finally found a more consistent way to fire mysql queries in php i.e. transactions. I have two options to insert data in multiple tables ::

1) through procedures
2) through transactions

Of course i am going to prefer is the 2 way i.e. transactions. I went thru all the manuals of which Ronald gave me the links. But reading many forums and tutorials, i land up here again just to know why my code wont output the way i want. Just have a look at my code :
[PHP]
<?php
try {
$dbh = new PDO('mysql:host =localhost;port =3306;dbname=te st', 'root', '', array( PDO::ATTR_PERSI STENT => true));

$dbh->setAttribute(P DO::ATTR_ERRMOD E, PDO::ERRMODE_EX CEPTION);
$dbh->beginTransacti on();
$dbh->exec("update pop set age=1 where id=1");
$dbh->rollBack();
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
?>[/PHP]

It wont rollback, as written in code, it should not affect the age column of the table pop, because it meets the $dbh->rollback() function, but it will automaticaly commit.

please help me ..
I want the autoCommit thing to turn off using php.

cheers !!
Realin !

[realin]

i tried the conventional way, but even that wont help..
the change simply reflects in the database, it wont rollback :(
here is one more code, which i tried
[PHP]
<?php
$con=mysql_conn ect("localhost" ,"root","");
mysql_select_db ("test");

$res=mysql_quer y("START TRANSACTION",$c on);
if($res)
echo "transactio n started \n";

$res=mysql_quer y("update pop set age=90 where id=2");
if($res)
echo "Query Executed \n";

$res=mysql_quer y("ROLLBACK") ;
if($res)
echo "Rolled back";

?>[/PHP]

please help me thanks

[realin]

please guys i need to know this ..
please

[ronverdonk]

I think the AUTOCOMMIT statement has to do with this. The following sample works for me, so give it a try. I used a $debug variable to be able to switch between rollback and commit modes.[php]<?php
$debug=1;
$con=mysql_conn ect("localhost" ,"xxx","yyy" )
or die("Connect error: ".mysql_error() );

mysql_select_db ("zzz")
or die("Select db error: ".mysql_error() );

$res=mysql_quer y("SET AUTOCOMMIT=0",$ con)
or die("Set autocommit error: ".mysql_error() );

$res=mysql_quer y("START TRANSACTION",$c on)
or die("Start xact error: ".mysql_error() );
echo "transactio n started <br>";

$res=mysql_quer y("update a set yyyy='xest' where id > 5")
or die("Update error: ".mysql_query() );
echo "Query Executed <br>";

if ($debug) {
$res=mysql_quer y("ROLLBACK")
or die("Rollback error: ".mysql_query() );
echo "Rolled back<br>";
}

else {
$res=mysql_quer y("COMMIT")
or die("Commit error: ".mysql_query() );
echo "Committed<br>" ;
}
?>[/php]Ronald

[code green]

Are the tables InnoDB?

[realin]

hiya ronald,

thanks for the reply, will go home and check it out..

much of thanks :)

[ronverdonk]

hiya ronald,

thanks for the reply, will go home and check it out..

much of thanks :)

You are welcome. See you.

Ronald

[realin]

hiya again Ronald,

Sorry to bother you, but this piece of code aint working for me..

here my System specs.

OS :: Windows Vista 
Using XAMPP latest version so, 
 
PHP Version 5.2.5
MYSQL Client API version 5.0.51a

I pasted your code, changed DB Details, and table details.. It showed Rolled back in browser but actually affected the databse :(

I am not actually able to perform transactions on mysql straight a way without having to use PHP even ..

What could be the problem ?

thanks for ur replies :)
cheers !!

[realin]

Are the tables InnoDB?

hiya code green,

how do i check if my tables are innoDB or no..
cause i tried creating a new database and then executing the following command, and it should (as i believe) create a table under InnoDB engine,
please see if that is perfect ?

CREATE TABLE pop (id INT, age vachar (20), INDEX (id)) ENGINE=InnoDB;

thanks
Cheers!!
Realin !

############# EDIT ###############

hey hey,

i guess i got the issue here,

well i am using two tools to create tables and to manipulate mysql database, they are

Mysql query tools
phpmyadmin

Now when i fired the query (read from mysql website)

SHOW TABLE STATUS FROM test2 LIKE 'pop'

i could figure out that, the line of code written below does not create table under InnoDB engine, rather it creates table under MyISAM engine, which does not support TRANSACTIONS.

CREATE TABLE pop (id INT, age vachar (20), INDEX (id)) ENGINE=InnoDB;

pheeeeeeeeewwww w (deep breathe)

Well, i guess now i gotta search a way to make tables in InnoDB instead of MyISAM engine..
When i try altering the table, it just do not let me do that and instead gives an error saying

2014 Commands out of sync; you can't run this command now

well, if some gets to know, what this all is happening, i will be thankful :)

Ronald, i really thank you mann for every single LOC :0

thanks & cheers to every1 :)

Realin !

[realin]

All right,

I m trying it from command line, even then i am unable to create a table under InnoDB engine, well why so ?

trust me, i never bothered about the engine in the last 2 years :p

Now i come to know, why these things are brought into existence .. hehehe .. :)