CRISHK Corporation CRISHK Corporation - 2 months ago 10
MySQL Question

Cannot commit when use mysqli_multi_query with multiple queries

I'm trying to commit a mysql multi query, but I can't...

When the mysqli_multi_query "query" has multiple queries cannot commit it. But, when the mysqli_multi_query "query" has an unique query, it is committed correctly. In both cases the execution is OK.

For example:

// No commits
$query = "insert into table(id) values (3); insert into table(id) values (4); insert into table (id) values (5);";
$connection = getConnection( ... );
mysqli_multi_query( $connection, $query );
mysqli_commit( $connection );


This code commits:

// This will commits
$query = "insert into table(id) values (3)";
$connection = getConnection( ... );
mysqli_multi_query( $connection, $query );
mysqli_commit( $connection );

Answer

You don't need to issue multiple queries for this, one INSERT query can contain many rows of values to insert at once. It's also much faster than issuing multiple separate queries.

INSERT INTO table (id) VALUES (3), (4), (5)

Then you can use mysql_query/mysqli_query instead of the rarely used multi query stuff.

Comments