0

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
James Grunshaw
  • 11
  • 1

2 Answers2

0

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);
James Grunshaw
  • 11
  • 1
-1

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;
vixducis
  • 1,010
  • 1
  • 8
  • 22
  • I had actually already tried that - it returns 0 for some reason. The actual return for my demo should be 2. It is definitely returning 2 results as I can print them. – James Grunshaw Sep 30 '21 at 00:26
  • @JamesGrunshaw Which SQL engine are you using? MySQL, SQLite,... ? – vixducis Sep 30 '21 at 01:56