CODEWITHSUNDEEP
phpmysqlmysqli
Syed Nizamudeen
Syed Nizamudeen

Reputation: 440

After Update Query Executed Affected rows returning 0 in mysql

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:

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 |
+------------------------+---------------+------+-----+-------------------+-----------------------------+

Upvotes: 0

Views: 2482

Answers (2)

Syed Nizamudeen
Syed Nizamudeen

Reputation: 440

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.

Upvotes: 0

bish
bish

Reputation: 3419

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);
  }
}

Upvotes: 1

Related Questions