PHP's stmt affected_rows intermittently returning 0 when it should return a positive integer

suggest change
<?php
    // if device exists, update timestamp
    $stmt = $mysqli->prepare("UPDATE new_devices SET nd_timestamp=? WHERE nd_deviceid=?");
    $stmt->bind_param('ss', $now, $device);
    $stmt->execute();
    //echo "Affected Rows: ".$stmt->affected_rows; // This line is where I am checking the status of the update query.

    if ($stmt->affected_rows < 1){ // Because affected_rows sometimes returns 0, the insert code runs instead of being skipped. Now I have many duplicate entries.
        
        $ins = $mysqli->prepare("INSERT INTO new_devices (nd_id,nd_deviceid,nd_timestamp) VALUES (nd_id,?,?)");
        $ins -> bind_param("ss",$device,$now);
        $ins -> execute();
        $ins -> store_result();
        $ins -> free_result();
    }
?>

VALUES (nd_id, ?, ?) does not insert a row: nd_id is read as a column name, and MySQL reports an unknown column. If prepare() fails, the following bind_param() call is on false. Separately, $stmt->affected_rows is 0 when the UPDATE matches a row but changes nothing, and -1 when the statement failed. Both are < 1, so this branch runs. store_result() applies to a result set, not to an INSERT.

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:



Table Of Contents