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?

It's just the number of rows that were affected by the last query, it's not cumulative. — Barmar, Oct 03 '19 at 21:46
@Barmar, okay, thank you. Is there a way to see cumulative rows affected? — Hmerman6006, Oct 03 '19 at 21:48
@Barmar, I am using it at the moment. But if there is an alternative one liner function, then I would rather use that. Is there such a Mysqli built in function or method? — Hmerman6006, Oct 03 '19 at 21:50
I am not sure I understand your question, but each execution of your prepared statement will insert only 1 record, so `affected_rows` will always be 1. I doubt there exists any cumulative number, but this is interesting question IMO. — Dharman, Oct 03 '19 at 21:53
If you inserted multiple rows in a single query, instead of separate queries for each row, it would contain the total number of rows. — Barmar, Oct 03 '19 at 21:56
@Barmar, thanks for your comment it helped a lot. That means a shift in thought and technique of execution of the inserts. Will have to think a bit on how to do multiple inserts in one single query. — Hmerman6006, Oct 03 '19 at 22:04
It's much easier with PDO than mysqli, since you can provide all the parameters as an array. — Barmar, Oct 03 '19 at 22:05
IMHO, *everything* is easier with PDO than mysqli, I don't know why mysqli is used more. Probably just because it looks like mysql, which was the normal API for years. — Barmar, Oct 03 '19 at 22:06
@Barmar Some people claim that mysqli is faster than PDO, but certainly it is not the reason why it is used more. In my opinion if mysqli was gone, no harm would be done. — Dharman, Oct 03 '19 at 22:08
@Barmar I use it because of inexperience as I only started learning php in May 2019. As my first website progressed I did start to notice a certain more complex methodology with MySqli when I read this forum. But as I have never tried to use PDO and will only try it on my next project I have to find a solution within my current project scope. — Hmerman6006, Oct 03 '19 at 22:17

score 2 · Accepted Answer · answered Oct 03 '19 at 22:33

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.

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

1 Answers1