22

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;?>

Dharman
  • 30,962
  • 25
  • 85
  • 135
Kevin
  • 1,685
  • 7
  • 28
  • 55

4 Answers4

27

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;
Dharman
  • 30,962
  • 25
  • 85
  • 135
Kevin Pei
  • 5,800
  • 7
  • 38
  • 55
  • Where does it go in the prepared statement I posted? After execute()? After bind_result, or fetch? Does it matter? – Kevin May 24 '13 at 00:34
  • @Kevin I don't believe so. Just make sure it goes after execute and it should be fine – Kevin Pei May 24 '13 at 00:34
  • Oh and by the way you forgot a space between the `?` and `ORDER BY`. I'll update my answer to fix that – Kevin Pei May 24 '13 at 00:35
  • @kp, thanks. This is at least not bugging out right now. However, it's giving me a result of 1 row, when the query actually yields 2 results. Do you see anything obvious I'm missing? Otherwise, I'll dig into this as another issue. Thanks for your help – Kevin May 24 '13 at 00:38
  • 8
    Also, I think this '$stmt->store_result();' needs to be added before the num_rows line. – Kevin May 24 '13 at 00:39
  • @Kevin oh ok you're probably correct that it has to go after that. Also for the query returning 1 row, it's probably because you're only querying one id - you only call that once (You call `bind_param` and `execute` only once). `num_rows` gives you the number of rows returned for that one specific query with the id substituted in, and referencing a unique id will give you only one result – Kevin Pei May 24 '13 at 00:43
  • got it. I thought it was something like that. I'll hit the books again and figure out what to do. Thanks for helping me get the num_rows working. – Kevin May 24 '13 at 00:57
  • @Kevin No Problem. Have fun coding! – Kevin Pei May 24 '13 at 01:14
3

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.

mickmackusa
  • 43,625
  • 12
  • 83
  • 136
1

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.
Devner
  • 6,825
  • 11
  • 63
  • 104
  • The OP is not asking about the affected rows from a delete query. This is the correct answer to a different question. – mickmackusa May 31 '21 at 05:33
-3

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.

EvWill
  • 392
  • 1
  • 4
  • 13