php - 执行更新查询后受影响的行在 mysql 中返回 0

Question

我正在尝试更新 MySQL 表中的一行中的单个列。执行 UPDATE 查询后，MySQL 表中将发生 UPDATE。但 $stmt->affected_rows 返回 0。为什么它返回零？

function updateSignUp($status,$recordId){
            $prepareStmt='UPDATE <DBNAME>.<TABLENAME> SET status=? WHERE id=?;';
            $mysqli=$this->connectDB(<DBNAME>);#connectDB user written function
            if ($stmt=$mysqli->prepare($prepareStmt)){
                $stmt->bind_param('ii', $status, $recordId);
                if (!$stmt->execute()) {
                    $stmt->close();
                    $mysqli->close();
                    return $this->errormsg('FAILURE!','Staff SignUp cannot Perform at this moment.');#errormsg user written function
                }elseif($stmt->affected_rows>0){
                    $stmt->close();
                    $mysqli->close();
                    return $this->errormsg('SUCCESS!','Staff SignUp Done.',2);
                }else{
                    $stmt->close();
                    $mysqli->close();
                    return $this->errormsg('WARNING!','Staff SignUp Not Done.',4);
                }
            }else{ return $this->errormsg('PREPARE FAILED:','(' . $mysqli->errno . ') ' . $mysqli->error); }
        }

        echo updateSignUp(0,15);

Result Displayed: WARNING! Staff SignUp Not Done.

Expecting Result: SUCCESS! Staff SignUp Done.

注意:

• PHP 版本 5.5.12
• MySQL 5.6.17
• Apache 2.4.9
• WAMP服务器2.5

实际表格如下:

+------------------------+---------------+------+-----+-------------------+-----------------------------+
|         Field          |     Type      | Null | Key |      Default      |            Extra            |
+------------------------+---------------+------+-----+-------------------+-----------------------------+
| id                     | int(11)       | NO   | PRI | NULL              | auto_increment              |
| ldapusername           | varchar(30)   | YES  |     | NULL              |                             |
| email                  | varchar(50)   | NO   |     | NULL              |                             |
| firstname              | varchar(20)   | YES  |     | NULL              |                             |
| lastname               | varchar(20)   | YES  |     | NULL              |                             |
| designation            | varchar(30)   | YES  |     | NULL              |                             |
| username               | varchar(30)   | NO   |     | NULL              |                             |
| role                   | int(5)        | NO   |     | NULL              |                             |
| manager                | int(11)       | NO   |     | NULL              |                             |
| currency               | int(11)       | NO   |     | NULL              |                             |
| country                | int(11)       | YES  |     | NULL              |                             |
| payee_id               | varchar(50)   | YES  |     | NULL              |                             |
| bank_acc_no            | varchar(30)   | YES  |     | NULL              |                             |
| bank_name              | varchar(50)   | YES  |     | NULL              |                             |
| bank_branch            | varchar(50)   | YES  |     | NULL              |                             |
| bank_swift_code        | varchar(30)   | YES  |     | NULL              |                             |
| ext_no                 | int(10)       | YES  |     | NULL              |                             |
| department             | int(5)        | NO   |     | NULL              |                             |
| marital_status         | int(5)        | NO   |     | NULL              |                             |
| monthly_limit          | decimal(15,2) | YES  |     | NULL              |                             |
| communication_limit    | decimal(15,2) | YES  |     | NULL              |                             |
| medical_per_bill       | decimal(15,2) | YES  |     | NULL              |                             |
| medical_per_annum      | decimal(15,2) | YES  |     | NULL              |                             |
| medical_cur_year_total | decimal(15,2) | YES  |     | NULL              |                             |
| dental_per_bill        | decimal(15,2) | YES  |     | NULL              |                             |
| dental_per_annum       | decimal(15,2) | YES  |     | NULL              |                             |
| dental_cur_year_total  | decimal(15,2) | YES  |     | NULL              |                             |
| doj                    | date          | YES  |     | NULL              |                             |
| status                 | int(5)        | NO   |     | 2                 |                             |
| members_link_id        | int(11)       | YES  |     | NULL              |                             |
| created_on             | timestamp     | NO   |     | CURRENT_TIMESTAMP |                             |
| modified_by            | int(10)       | YES  |     | NULL              |                             |
| modified_on            | timestamp     | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
+------------------------+---------------+------+-----+-------------------+-----------------------------+

Answer 1

您是否使用 SELECT 语句检查了要更新的行是否存在，并且在更新之前和更新之后是否有另一个值？因为当更新前后的值相同时，mysql将返回零受影响的行。请参阅this similiar SO question 。

编辑:如果您想获取找到的所有行(而不仅仅是真正更改的行)，您可以尝试连接标志FLAG_FOUND_ROWS(请参阅 MYSQL Doc )。

edit2:好的新想法:错误可能在 if/elseif/else 语句中，受影响的行可能没有被读取

尝试:

$success = $stmt->execute();

if(!success) {
   $stmt->close();
  $mysqli->close();
  return $this->errormsg('FAILURE!','Staff SignUp cannot Perform at this moment.');#errormsg user written function   
} else {
  if($stmt->affected_rows>0) {
    $stmt->close();
    $mysqli->close();
    return $this->errormsg('SUCCESS!','Staff SignUp Done.',2);
  } else {
    $stmt->close();
    $mysqli->close();
    return $this->errormsg('WARNING!','Staff SignUp Not Done.',4);
  }
}