mysql transactions

mysql transactions php

[cheethebee]

Hi,

In my php code, I am doing

// New records for eventwines table
$insertString = "BEGIN;";
for ($i = 1; $i <= $maxWines; $i++)
{
$wineid[$i] = scrub(trim($_POST['wine'.$i]));
if (is_numeric($wineid[$i]))
{
$insertString .= "INSERT INTO eventwines VALUES ($wineid[$i], $eventid);";
}
}
$insertString .= "COMMIT;";
$insert = mysql_query($insertString, $connection);

The query fails to execute and I dont understand why. Because if I print out the SQL statement at the end, its works fine if i type it striaght into the mysql command line.

It also works without the BEGIN and COMMIT, any ideas?

Cheers

[ukgimp]

have you tried taking the printed out sql statement and inserting it into phpmyadmin or another command line program and see if it works? Then you will know if your sql syntax is correct and you can subsequently change it.

[cheethebee]

"Because if I print out the SQL statement at the end, its works fine if i type it striaght into the mysql command line."

Thats wot i meant here.

I am having problem trying it in phpmyadmin, it keeps adding slashes to my SQL statement.

[cheethebee]

Remember that if i take out the BEGIN and COMMIT it works!

[killroy]

Have you tried doing thre seperate mysql_query($insertString, $connection); for each command?

SN

[ukgimp]

I think transactions only work in certain table types in mysql.

[cheethebee]

It works if i separate it ofcos, since it works without the begin and commit. But i need it to be in a transaction to ensure no invalid data is inserted.

My table type is INNODB, which i read supports transactions (correct me if i am wrong), and it works on my command line!

[killroy]

Well then eparate it, I don'T think you can send more then one statement at a time. the mysql.exe shell actually splits them and sends them off individually

SN

[cheethebee]

So wot your saying is that ... i should do the equvilent of the following:

mysql_query("BEGIN", $connection);
.
.
.
mysql_query("INSERT ... ", $connection);
.
.
.
mysql_query("COMMIT", $connection);

Is this correct?

Cheers

[killroy]

yup, but no reason you have to do the begin before you build the query string. They can be on three consecutive lines.

SN

[cheethebee]

thank you

[killroy]

Just to clarify:

// New records for eventwines table
$insertString = "";
for ($i = 1; $i <= $maxWines; $i++)
{
$wineid[$i] = scrub(trim($_POST['wine'.$i]));
if (is_numeric($wineid[$i]))
{
$insertString .= "INSERT INTO eventwines VALUES ($wineid[$i], $eventid);";
}
}
$insert = mysql_query("BEGIN;", $connection);
$insert = mysql_query($insertString, $connection);
$insert = mysql_query("COMMIT;", $connection);

SN

PS don't know much php, this gotta be it though

[jatar_k]

you could also take the semi colon out of the string

$insertString .= "INSERT INTO eventwines VALUES ($wineid[$i], $eventid);";

to

$insertString .= "INSERT INTO eventwines VALUES ($wineid[$i], $eventid)";