How to get number of affected_rows in mysqli OOP? Mysqli OOP affected_rows returning -1

Question

What is the exect way to get number of affected rows in mysqli OOP. I am creating crud class using msqli OOP. I am getting int -1.

    $query = "SELECT * FROM `sk_courses`";
    $stmt = $this->_mysqli->prepare($query);
    $stmt->execute();
    $stmt->affected_rows ;

    var_dump($stmt->affected_rows);    // output is int -1

Output of var_dump($stmt) is:

   object(mysqli_stmt)[7]
   public 'affected_rows' => null
   public 'insert_id' => null
   public 'num_rows' => null
   public 'param_count' => null
   public 'field_count' => null
   public 'errno' => null
   public 'error' => null
   public 'error_list' => null
   public 'sqlstate' => null
   public 'id' => null

That's simple. Just read the **manual page** for the function you are using. — Your Common Sense, Dec 13 '13 at 10:35
It's a shame to upvote such questions. Shame for the very art of programming. — Your Common Sense, Dec 13 '13 at 10:35
Duplicate of http://stackoverflow.com/questions/1018336/getting-row-count-for-a-table-in-mysql — Your Common Sense, Dec 13 '13 at 10:42
@YourCommonSense .. Did you try to properly understand my problem before comment..? output of $stmt->affected_rows is -1 — Zohaib, Dec 13 '13 at 10:49
Yes, I quite understand *both* your problems. Either one with function you have no idea what it is used for, and one which is your *real* problem — Your Common Sense, Dec 13 '13 at 10:55
Do you have solution of my real problem...? Then please share. — Zohaib, Dec 13 '13 at 10:59

score 2 · Accepted Answer · answered May 07 '14 at 22:44

This is an issue with understanding the mysqli_stmt documentation, which includes pages for each of the functions below.

A SELECT query will not affect any rows.

Looking in the manual at [php docs for mysqli affected-rows]. Under Return values:

-1 indicates that the query returned an error.

However, this is really a mixed question.

To get the number of rows, try:

$query = "SELECT * FROM `sk_courses`";       // or
$query = "SELECT lastName, firstName, ... FROM `sk_courses`";

$stmt = $mysqli->prepare($query);
$stmt->execute();
$stmt->store_result();                      // without this line, num_rows = 0
print $stmt->num_rows;

Then, if interested in the result set set, add:

// bind result variables. next statement 'binds' them in the order of the select query
$stmt->bind_result($last, $first, .... );  // must have variable for EACH column

while ($stmt->fetch()) {
    printf ("%s (%s)\n", $first, $last);
}

How to get number of affected_rows in mysqli OOP? Mysqli OOP affected_rows returning -1

1 Answers1