PHP- mysqli->num_rows always returns 0, Prepared statements

Question

First of all I would like to tell that I have already gone though ALL DUPLICATE questions. and tried the changes suggested there.

As far as now I have already tried changing num_rows to num_rows() And using store_result(); And using affected_rows().

Also calling store_result(); after execute()

I think there might be some other problem which I can't figure out

$conn->autocommit(false);   
if ($sucess){
    $stmt2 = $conn->prepare("UPDATE e_eventqueue SET e_urlfil=? WHERE e_id=? 
AND u_id=?");
    $stmt2->bind_param("iis",$e_urlfil,$e_id,$u_id);

    if($stmt2->execute()){
         $stmt2->store_result();
        echo "true";
        echo $stmt2->num_rows;  // <- this always return 0 even when its not
        $stmt2->close();
        $conn->commit();
    }
 else{
      $conn->rollback();
     echo "rollback";
  }

}

Nebulous

Are you actually updating anything? MySQL can report 0 rows affected if the UPDATE results in no change – Wineshop

For update query You need to check affected_rows . not num_rows – Return

@Wineshop yes i am updating different values everytime which is visible in phpmyadmin – Nebulous

After execute check like this $row_count= $stmt2->affected_rows; If query executed successfully but no changes in data means it will return 0 . – Return

FYI, for an UPDATE query, you should use $stmt2->affected_rows. You will not need to store the result, ie $stmt2 = $conn->prepare(...); $stmt2->bind_param(...); $stmt2->execute(); echo $stmt2->affected_rows;. Also, I'm not sure if affected_rows reports anything until the transaction is committed. – Wineshop

Please check this answer #28253907 – Binturong

Answer 1

1st : For update query You need to check affected_rows . not num_rows

2nd : After execute check like this $row_count= $stmt2->affected_rows; If query executed successfully but no changes in data means it will return 0 .

if($stmt2->execute()){

    echo $stmt2->affected_rows; 
 }

Affected_rows :

Affected_rows is for insert,update,delete

Num_rows :

Num_rows is for select

Return

Answer 2

As mentioned in the comments above, check the documentation:

http://php.net/manual/mysqli-stmt.affected-rows.php

Returns the total number of rows changed, deleted, or inserted by the last executed statement

http://php.net/manual/mysqli-stmt.num-rows.php

Return the number of rows in statements result set

Personalize