Prepared Statements - Number of Rows
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.
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;
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.