Get count of all affected rows when executing multiple statements in MySql

Question

In MySql, how can I get the count of all affected rows when executing multiple queries simultaneously?

I currently do like this:

$stmt = ' update prodb set buyer = buyer+1 where userId = 1; update prodb set seller = seller+1 where userId = 2; '; $update = $dbc->prepare($stmt); $update->execute(); $queryCount = $update->rowCount(); echo 'This is QueryCount '.$queryCount;

But, even though there are 2 rows being affected, I get $queryCount as only 1. Is there a way to get the query count as 2. i.e the count of first statement, and the count of the second one? I can confirm that there are two rows being affected here.

I'm doing all this using MySql PDO.

You can't. rowCount() only gets the results of the LAST query you execute. if you execute two queries, whichever of those finishes last will be the one rowCount() reports on. rowCount() is essentially just calling the mysql_affected_rows() api function. — Marc B
– Marc B, Commented Oct 2, 2014 at 16:15
Someone said something about advancing the cursor... You'd know what that is? — jmenezes
– jmenezes, Commented Oct 2, 2014 at 16:17
you're not using cursors. they're only available in stored procedures. — Marc B
– Marc B, Commented Oct 2, 2014 at 16:20

Bill Karwin · Accepted Answer · 2014-10-02 16:23:57Z

https://dev.mysql.com/doc/refman/5.6/en/c-api-multiple-queries.html says:

Executing a multiple-statement string can produce multiple result sets or row-count indicators. Processing these results involves a different approach than for the single-statement case: After handling the result from the first statement, it is necessary to check whether more results exist and process them in turn if so. To support multiple-result processing, the C API includes the mysql_more_results() and mysql_next_result() functions.

The way to do this via PDO is to call the PDOStatement::nextRowset() function.

Here's an example. I run a multi-query in which I insert one row in the first query, and two rows in the second.

$sql = "insert into foo () values () ; insert into bar () values (), ()"; $stmt = $dbh->prepare($sql); $stmt->execute(); do { echo "row count: ".$stmt->rowCount()."\n"; } while ($stmt->nextRowset());

Output:

row count: 1 row count: 2

Suppose I just needed 2 without the do {} while thing? Would that be possible?
Sure, just call rowCount() then call nextRowset() then call rowCount() again.

Collectives™ on Stack Overflow

Get count of all affected rows when executing multiple statements in MySql

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related