CODEWITHSUNDEEP
phptransactionsmysqliautocommit
Programster
Programster

Reputation: 12784

php mysqli - burying transactions in a single query

MySQli keeps stating that there is a syntax error in the first select part of my 'combined' query as shown below:

$my_massive_query =
    'START TRANSACTION WITH CONSISTENT SNAPSHOT;
    SELECT d.id AS `Location ID`, ...
    SELECT d.id AS `Location ID`, ...
    SELECT d.id AS `Location ID`, ...
    SELECT d.id AS `Location ID`, ...
    SELECT d.id AS `Location ID`, ...
    COMMIT';
$mysqli->query($my_massive_query);

Is this 'not allowed' in php? Does one have to use the mysqli functions like follows:

$mysqli->autocommit(FALSE);
$mysqli->query("SELECT ...");
$mysqli->query("SELECT ...");
$mysqli->query("SELECT ...");
$mysqli->commit();

I have executed the query in mysql (after using var_dump to fetch it as-is), and it is executing perfectly so I don't think there really is a syntax issue and that maybe one is not allowed to 'combine' queries in a single string?

I am worried that START TRANSACTION WITH CONSISTENT SNAPSHOT; has some extra conditions that will not be met by just setting autocommit to false.

Upvotes: 0

Views: 397

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157893

Using API functions you can do everything you can do in console.
However, every command have to be called separately. This is very simple rule to follow

$mysqli->query("START TRANSACTION WITH CONSISTENT SNAPSHOT");
$mysqli->query("SELECT ...");
$mysqli->query("SELECT ...");
$mysqli->query("COMMIT");

Upvotes: 1

Damien Overeem
Damien Overeem

Reputation: 4529

mysqli::query can only deal with 1 query at a time. To do multiple queries in one string, use mysqli::multi_query.

Upvotes: 1

Related Questions