Count rows from an SQL statement and store in PHP variable-CodePudding

I have the following SQL code in my PHP file. I want to count the number of rows returned and store it in a variable so I can output it on the page.

I've tried a few solutions I found but none of them worked.

$stmt = $con->prepare('SELECT id, owner_id, hs_name, hs_address FROM hotspots WHERE owner_id = ?');
$stmt->bind_param('i', $_SESSION['id']);

$stmt->execute();
$stmt->bind_result($hsid, $ownerid, $hsname, $hsaddress);

while($stmt->fetch()) {
    // print results in loop
}

Any help would be much appreciated.

CodePudding user response：

MySQLi provides a nice way of doing this, you can simply use the num_rows property on the statement after it has been executed. This will immediately return the number of rows returned by the statement.

$stmt->execute();
$count = $stmt->num_rows;

CodePudding user response：

I fixed this by adding

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

After the execute, so the entire thing now looks like

$stmt = $con->prepare('SELECT id, owner_id, hs_name, hs_address FROM hotspots WHERE owner_id = ?');
$stmt->bind_param('i', $_SESSION['id']);
$stmt->execute();
$stmt->store_result();
$count = $stmt->num_rows;
$stmt->bind_result($hsid, $ownerid, $hsname, $hsaddress);

CodePudding user response：

If you are trying to echo out the count you can do this.

$i = 1; //start count with one.
while($stmt->fetch()) {
    echo $i;
    $i= 1; //add one per loop.
}