How to return number rows affected on multiple inserts with Mysqli Php?

Question

I have a function which inserts multiple rows using the MySqli library with prepared statements. The inserts works great, the problem is the build in $stmt->affected_rows method always returns the number of affected rows as 1.

Now to move around the affected row issue I created a counter which counts each executed statement. This solution is accurate. But I enjoy using built in methods and functions, so why is the $stmt->affected_rows always returning one, even though I inserted multiple rows? Is my code defective in some way or form? Maybe there is a pure Sql solution.

Here is my code:

try {
    $query = "INSERT INTO dryenrolltb(enroll_id,id_entity,bin_type,tara_weight,dtetime_created,enrollprint_status) VALUES(?,?,?,?,?,?)";
    $stmt = $db->prepare($query);
    $stmt->bind_param('iiidsi', $enroll,$ent,$bin,$tara,$dte_create,$enr_status);
    $result['rows']['rowerrors'] = array();
    $result['rows']['rowsaffected'] = [];
    $cnt = 0;
    foreach ($arr as $value) {
        $enroll = $value['enroll'];
        $ent = $value['entid'];
        $bin = $value['bin_t'];
        $tara = $value['tara'];
        $dte_create = $value['dtecreat'];
        $enr_status = $value['enr_status'];
        if($stmt->execute()) {
            $cnt++;
        } else {
            array_push($result['rows']['rowerrors'],$value['enroll']);
        }

    }
    if ($stmt->affected_rows > 0) {
        echo "Affectionately yours";
        array_push($result['rows']['rowsaffected'], $stmt->affected_rows);
        array_push($result['rows']['rowsaffected'], $cnt);
        return $result;
    } else {
        return false;
    }
} catch (Exception $e) {
    echo "Danger exception caught";
    return false;
}

Can someone please give me a clue on why the $stmt->affected_rows always returns one on multiple inserts?

Answer 1

No. It seems like MySQLi statement class has no way of storing a running total of affected rows. After I thought about it, it makes total sense. Let me explain.

Every time you execute the statement it will affect a given number of rows. In your case you have a simple INSERT statement, which will add records one by one. Therefore, each time you call execute() the affected_rows value is one.

The query could be something different. For example INSERT INTO ... SELECT or UPDATE could affect multiple rows.
You could also have INSERT INTO ... ON DUPLICATE KEY UPDATE. If the key exists in DB, then you are not inserting anything. If the values are the same, you are not even updating anything. The affected rows, could be 0 or more.

The reason why it would be unwise for the statement to keep a running total of the affected rows is that each execution affects certain rows, irrespective of the previous executions. They could be even the same records. Consider the following example:

$stmt = $mysqli->prepare('UPDATE users SET username=? WHERE id=?');
$stmt->bind_param('si', $name, $id);

$id = 102;

$name = 'Affected rows 1';
$stmt->execute();
echo $stmt->affected_rows; // 1

$name = 'Affected rows 2';
$stmt->execute();
echo $stmt->affected_rows; // 1

Both update statements updated the same row. If mysqli kept a running total it would report 2 affected rows, but in reality only 1 row was changed. If the number was summed, you would be losing information.

So, for your simple scenario, it is fine to keep the total on your own, for example by summing up the $stmt->affected_rows after each execution. Anything more than that, it would probably not make much sense.