Hi Folks,
Can someone please take a look at the following and explain to me why the first query doesn't work, and the second set does?:

1. 

$begin="SET AUTOCOMMIT=0; BEGIN;";
mysql_query ($begin, $connection) or die( mysql_errno($connection) . ": " . mysql_error($connection)). "\n";

-which throws the syntax error:
1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN' at line 1

and 2.

$begin1="SET AUTOCOMMIT=0;";
$begin2="BEGIN;";
mysql_query ($begin1, $connection) or die( mysql_errno($connection) . ": " . mysql_error($connection)). "\n";
mysql_query ($begin2, $connection) or die( mysql_errno($connection) . ": " . mysql_error($connection)). "\n";

This may sound strange, but the 1st set worked fine yesterday, then we had a test environment database update (only the content, not the client/server)
and now I can only start a transaction by splitting the :
$begin="SET AUTOCOMMIT=0; BEGIN;";
statement.

The tables I use the connection for during the transaction are all Innodb, and as a test I'm able to change the autocommit flag just fine using the mysql client terminal. When i make the change using the second option i can view the auto commit change happening during the script by using something like the following:

        $query = "SELECT @@autocommit as theStatus;";
        $result = mysql_query ($query, $connection);
        while ($row = mysql_fetch_assoc($result)) {
                echo "AUTOCOMMIT? : " . $row[theStatus] . "<br>";
        }

Any suggestions, or comment you all have would be greatly appreciated!
Robin.

    The mysql functions in PHP don't support multiple queries AFAIK. The second one works because each SQL statement gets executed separately.

      robinjohn wrote:

      This may sound strange, but the 1st set worked fine yesterday

      It sounds beyond 'strange,' since the outdated [man]mysql[/man] library can't execute multiple queries at once like you're trying to do.

      Instead, you could use a more recent library such as [man]MySQLi[/man]'s [man]mysqli_multi_query/man function/method.

        Write a Reply...