Prepared Statements - Number of Rows

Question

I'm just trying to figure out how to determine the number of rows and then make that number display in the HTML.

My prepared statement looks like this:

if($stmt = $mysqli -> prepare("SELECT field1, field2, field3 FROM table WHERE id= ?ORDER BY id ASC")) 
    {
    /* Bind parameters, s - string, b - blob, i - int, etc */
    $stmt -> bind_param("i", $id);
    $stmt -> execute();
    
    /* Bind results */
    $stmt -> bind_result($testfield1, $testfield2, $testfield3);
    
    /* Fetch the value */
    $stmt -> fetch();

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

I understand that I'm supposed to first save the results, then use num_rows, like this:

$stmt->store_result();
$stmt->num_rows;

However, I'm running, and issue with the page bugging out when I put that code in there. I haven't even been able to get to the next step of how to display the number of rows

What am I missing in terms of calculating the number of rows inside the prepared statement, then how would I display it with a <?php echo '# rows: '.$WHATGOESHERE;?>

Answer 1

num_rows returns the number, you have to store it in a variable.

/*.....other code...*/
$numberofrows = $stmt->num_rows;
/*.....other code...*/
    
echo '# rows: '.$numberofrows;

So full code should be something like this:

$stmt = $mysqli -> prepare("SELECT field1, field2, field3 FROM table WHERE id= ? ORDER BY id ASC");
/* Bind parameters, s - string, b - blob, i - int, etc */
$stmt -> bind_param("i", $id);
$stmt -> execute();
$stmt -> store_result();

/* Bind results */
$stmt -> bind_result($testfield1, $testfield2, $testfield3);

/* Fetch the value */
$stmt -> fetch();
$numberofrows = $stmt->num_rows;

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

echo '# rows: '.$numberofrows;

Answer 2

If you are only interested in the row count instead of the actual rows of data, here is a complete query block with a COUNT(*) call in the SELECT clause.

$conn = new mysqli("host", "user", "pass", "db");
$stmt = $conn->prepare("SELECT COUNT(*) FROM `table` WHERE id= ?");
$stmt->bind_param("s", $id);
$stmt->execute();
$stmt->bind_result($num_rows);
$stmt->fetch();
echo $num_rows;

Or if you want to know the row count before iterating/processing the rows, one way is to lump the entire resultset (multi-dimensional array) into a variable and call count() before iterating.

$conn = new mysqli("host", "user", "pass", "db");
$sql = "SELECT field1, field2, field3
        FROM table
        WHERE id= ?
        ORDER BY id";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $id);
$stmt->execute();
$result = $stmt->get_result();
$resultset = $result->fetch_all(MYSQLI_ASSOC);
echo "<div>Num: " , count($resultset) , "</div>";
foreach ($resultset as $row) {
    echo "<div>Row: {$row['field1']} & {$row['field2']} & {$row['field3']}</div>";
}

*I have tested both of the above snippets to be successful on my localhost.

Answer 3

This works as of Feb 2020:

$number_of_records = $stmt->rowCount();
echo $number_of_records;

From php.net manual:

PDOStatement::rowCount() returns the number of rows affected by a DELETE, INSERT, or UPDATE statement.

Here is an example from their website:

<?php
/* Delete all rows from the FRUIT table */
$del = $dbh->prepare('DELETE FROM fruit');
$del->execute();

/* Return number of rows that were deleted */
print("Return number of rows that were deleted:\n");
$count = $del->rowCount();
print("Deleted $count rows.\n");
?>

The above example will output:

Return number of rows that were deleted:
Deleted 9 rows.

Answer 4

Check out the example #2 here: PHP.net

Use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.