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

Answer 1

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