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