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

}

Are you actually updating anything? MySQL can report 0 rows affected if the `UPDATE` results in no change — Phil, Sep 05 '17 at 03:38
For update query You need to check affected_rows . not num_rows — JYoThI, Sep 05 '17 at 03:39
@Phil yes i am updating different values everytime which is visible in phpmyadmin — GeekWithGlasses, Sep 05 '17 at 03:41
After execute check like this `$row_count= $stmt2->affected_rows;` If query executed successfully but no changes in data means it will return 0 . — JYoThI, Sep 05 '17 at 03:41
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. — Phil, Sep 05 '17 at 03:41
Please check this answer https://stackoverflow.com/questions/28253407/store-result-and-get-result-in-mysql-returns-false — Manisha Sharma, Sep 05 '17 at 04:06

JYoThI · Accepted Answer · 2017-09-05T03:49:40.160

5

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

edited Sep 05 '17 at 03:49

answered Sep 05 '17 at 03:43

JYoThI

11,977
1
11
26

score 2 · Answer 2 · edited Sep 05 '17 at 03:43

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

edited Sep 05 '17 at 03:43

Phil

157,677
23
242
245

answered Sep 05 '17 at 03:42

Bill Karwin

538,548
86
673
828

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

2 Answers2