Transaction Table Help needed

[Guest_imported]

Hello

Im just reading up on using Transaction Tables in the InnoDB format, but am having problems.

In the MySQL command line I do the following:
connect mydatabase;
begin;
SELECT * FROM mytable FOR UPDATE;

Then within another terminal window I enter:
connect mydatabase;
begin;
SELECT * FROM mytable FOR UPDATE;

In this second terminal window, MySQL waits until "commit;" has been entered into the first terminal window. So far so good.

But then within PHP I setup the following (simplified):

<?PHP

$thisdb=@mysql_connect(&quot;localhost&quot;,&quot;&quot;,&quot;&quot;

if (!@mysql_select_db(&quot;mydatabase&quot;,$thisdb)){
exit (&quot;Error: Unable to connect to the database.&quot;
}

mysql_query(&quot;BEGIN&quot;

mysql_query(&quot;SELECT * FROM mytable FOR UPDATE&quot;

print (&quot;I could have printed the results here&quot;

<form submit button here to stop program continuing, and when submit selected commit command is issued>

?>

I open two browser windows, and access the php file from the first, and as expected it prints &quot;I could have printed the results here&quot;.

BUT when I access the same PHP file from the second browser window it still prints the text, I would have expected it to pause at the Mysql_query command until the first browser's submit button was pressed.

What I am trying to do is to display some data on screen, and allow the person to select which ones they want to delete (via checkboxes). But I'm trying to lock the database rows that are affected so when they continue the rows haven't, say, been deleted by someone else.

Andrew Blee.

 

[sleipnir214]

Are you doing all this through a web server?

 

[Guest_imported]

Hello

Thanks for replying.

I found the answer out late last night, from someone in another forum.

I forgot that PHP is server side, so as soon as the current program completed, the transaction automatically closed. Therefore I have to re-write the code in a different way to obtain what it is I'm trying to achieve.

Regards

Andrew Blee.