PHP PDO returns false count of columns

Question

So what I'm trying to do, is that I want to return the number of columns in a where clause. Currently this is working:

$total = $pdo->query('
    SELECT
         COUNT(*)
    FROM
         _TABLENAME_
')->fetchColumn();

echo $total .' Rows Found';
// Outputs 10 if, 10 rows are found.

But when I try to do it with a WHERE LIKE cause, it won't work.

$gr = '%'.$_GET['genres'].'%';
$total = $pdo->query('
    SELECT
         COUNT(*)
    FROM
         _TABLENAME_
    WHERE
         genres
    LIKE
         '.$gr.'
')->fetchColumn();

echo $total . ' Rows Found';
// Outputs 1, even if more are found

But obviously that won't work, when that's not how you make a PDO Execute statement, so I tried to do this:

$gr = '%'.$_GET['genres'].'%';
$sql = 'SELECT COUNT(*) FROM `_TABLENAME_` WHERE genres LIKE :gr ';
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':gr', $gr, PDO::PARAM_STR);
$stmt->execute();
$total = $stmt->rowCount();

echo $total . ' Rows Found';

But yet again the result is only 1.

SELECT COUNT will only return a single row, with a single "column" entry containing the count value; you need to get that column value returned, not the rowcount — Mark Baker
– Mark Baker, Commented Sep 29, 2016 at 16:51
@PhiterFernandes what do you mean, by my code is ugly? It's total standard, so wtf — Kymetica
– Kymetica, Commented Sep 29, 2016 at 17:01

Anass · Accepted Answer · 2016-09-29 17:27:10Z

1

As PHP manual says :

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement.

Instead

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.

Check out it here : Counting rows returned by a SELECT statement. In the PHP manual you can learn much more use cases :)

I hope this help you to understand the behavior.

answered Sep 29, 2016 at 17:27

Anass

592 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP PDO returns false count of columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related