dongmei8071
2015-06-25 06:18
浏览 22
已采纳

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

I'm trying to update single column in a row from MySQL table. After the UPDATE query is executed an UPDATE is happening in MySQL Table. But $stmt->affected_rows is returning 0. Why is it returning zero?

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.

Note:

  • PHP Version 5.5.12
  • MySQL 5.6.17
  • Apache 2.4.9
  • WAMPSERVER 2.5

Actual Table Looks Like:

+------------------------+---------------+------+-----+-------------------+-----------------------------+
|         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 |
+------------------------+---------------+------+-----+-------------------+-----------------------------+
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • donglian3055 2015-11-27 06:45
    已采纳

    Thank you for all who tried to solve my issue. I was using jQuery AJAX and bootstrapvalidator to render HTML form and submit form using POST. This same issue simulated in another project and noticed that AJAX call triggered twice for single form submit.

    Form Submit JS:

    .on('success.form.bv', function(e){
        e.preventDefault();     
        var $form = $("#signupapprovalform"), url = $form.attr('action'); 
        $.post(url,$form.serialize()).done(function(dte){ $("#signup-content").html(dte); });
    });
    

    To Solve issue: I just added e.stopImmediatePropagation(); next to e.preventDefault();.

    Actually no issue with PHP and mysql.

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • dpmp9359 2015-06-25 09:17

    Did you check with a SELECTstatement if the row you want to update exists and has another value before the update than after it? Because when the value is the same before and after the update mysql will return zero affected rows. See this similiar SO question.

    edit: If you want to get all rows found (not only the real changed rows) you could try the connection flag FLAG_FOUND_ROWS (see MYSQL Doc).

    edit2: Okay new idea: The mistake maybe in the if/elseif/else statement that the affected rows is not read probably

    Try:

    $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);
      }
    }
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题