CODEWITHSUNDEEP
phpsqlpdo
cole
cole

Reputation: 15

sql count() example on PHP manual

I'm working on a pagination function with PHP pdo. So if you want to count a number of rows from a select statement you have to use the query() method and use the SQL count() function

https://www.php.net/manual/en/pdostatement.rowcount.php

That all makes sense but what if I need to count a number of rows from a select statement where I'm using prepare() and bound parameters? e.g. if I'm taking sensitive user data to insert into the select statement?

Upvotes: 0

Views: 69

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157883

This particular manual page is almost complete rubbish. It does confuse two absolutely different tasks that never could be interchanged.

Hence, the SQL count() query is just irrelevant to PDO::rowCount() for select queries and should never be used for the purpose.

Instead of that superfluous and useless query you have to use the result itself:

  • In case you are selecting only one row, then fetch that row and use the fetched array anywhere the row count was intended to be used:

    $stmt = $pdo->prepare("SELECT * FROM users WHERE id=?");
$stmt->execute([$id]); 
$user = $stmt->fetch();
if ($user) {
    ...
}

  • in case you are selecting multiple rows - fetch them as well and use the fetched data:

    $stmt = $pdo->prepare("SELECT * FROM users LIMIT :limit, :offset");
$stmt->execute(['limit' => $limit, 'offset' => $offset]); 
$data = $stmt->fetchAll();
if ($data) {
    ...
}

I cannot imagine the case when you would need to get the actual count of returned rows, but in such a case just use PHP's count():

 $count = count($data);

Upvotes: 2

Related Questions