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.