PHP Prepared Statement Returns -1

Question

I've been using prepared statements for a little while now and I've never had any problems.

Now I'm trying to:

$sql="SELECT PhotoID,Caption FROM Photos WHERE EntityID=? AND TypeID=? LIMIT ?,?";

$iDB = new mysqliDB(); // Extends mysqli

$stmt = $iDB->prepare($sql);

$stmt->bind_param('iiii',$entityID,$typeID,$minRange,$maxRange);

$stmt->execute();

$stmt->bind_result($photoID,$caption);

echo("Affected={$stmt->affected_rows}");

This prints -1. I have triple tested that all 4 values in bindParam are set, and that the sql query works when pasted into myAdmin with the respective values.

Any idea what may be causing this?

Edit:: I found the answer online, apparently I need to use $stmt->store_result(); after executing.. but I am not sure it's needed now and never before..

Answer 1

From the documentation of mysqli_stmt->affected_rows:

This function only works with queries which update a table. In order to get the number of rows from a SELECT query, use mysqli_stmt_num_rows() instead.

So you can only use this method with an UPDATE or DELETE query.

Furthermore:

Return Values An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE/DELETE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query has returned an error. NULL indicates an invalid argument was supplied to the function.

But this could also mean that it returns -1 because you try to use it for a SELECT statement. Try mysqli_stmt_num_rows().

Update:

Example form the page I linked to:

$query = "SELECT Name, CountryCode FROM City ORDER BY Name LIMIT 20";
if ($stmt = $mysqli->prepare($query)) {

    /* execute query */
    $stmt->execute();

    /* store result */
    $stmt->store_result();

    printf("Number of rows: %d.\n", $stmt->num_rows);

    /* close statement */
    $stmt->close();
}

Answer 2

Each mysqli function/method can fail. Depending on how you have extended class mysqli you probably have to test each and every return value. If a method return false an error occurred and the error message is stored in a property of either the mysqli or the statement object.

$sql="SELECT PhotoID,Caption FROM Photos WHERE EntityID=? AND TypeID=? LIMIT ?,?";

$iDB = new mysqliDB(); // Extends mysqli
if ($iDB->connect_error) {
  printf('connect error (%d) %s', $iDB->connect_errno, htmlspecialchars($iDB->connect_error));
  die;
}

$stmt = $iDB->prepare($sql);
if ( false===$stmt ) {
  printf('prepare failed: %s', htmlspecialchars($iDB->error));
  die;
}

$rc = $stmt->bind_param('iiii',$entityID,$typeID,$minRange,$maxRange);
if ( false===$rc ) {
  printf('bind_param failed: %s', htmlspecialchars($stmt->error));
  die;
}

$rc = $stmt->execute();
if ( false===$rc ) {
  printf('execute failed: %s', htmlspecialchars($stmt->error));
  die;
}

$rc = $stmt->bind_result($photoID,$caption);
if ( false===$rc ) {
  printf('bind_result failed: %s', htmlspecialchars($stmt->error));
  die;
}

// echo("Affected={$stmt->affected_rows}");