Pdo count rows from SELECT query with POST params

Question

From manual:

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

So PDOStatement::rowCount() isn't working for me with SELECT queries. I have query to which I must be able to add AND conditions to WHERE clause like this:

$sql = "SELECT * FROM customers WHERE 1=1";

if ($name)
    $sql .= " AND `name` LIKE :name";
if ($type)
    $sql .= " AND `type` = :type";
if ($category)
    $sql .= " AND `category` = :category";
if ($group)
    $sql .= " AND `group` = :group";
if ($area)
    $sql .= " AND `area` = :area";

$query = $pdo->prepare($sql);

if ($name)
    $query->bindValue(':name', '%' . $name . '%');
if ($type)
    $query->bindValue(':type', $type);
if ($category)
    $query->bindValue(':category', $category);
if ($group)
    $query->bindValue(':group', $group);
if ($area)
    $query->bindValue(':area', $area);

$query->execute();

Do I need to do it like following code or is there some easier way to do it?

$sql = "SELECT * FROM customers WHERE 1=1";
$count = "SELECT COUNT(*) FROM customers WHERE 1=1";

if ($name) {
    $sql .= " AND `name` LIKE :name";
    $count .= " AND `name` LIKE :name";
}
if ($type) {
    $sql .= " AND `type` = :type";
    $count .= " AND `type` = :type";
}
if ($category) {
    $sql .= " AND `category` = :category";
    $count .= " AND `category` = :category";
}
if ($group) {
    $sql .= " AND `group` = :group";
    $count .= " AND `group` = :group";
}
if ($area) {
    $sql .= " AND `area` = :area";
    $count .= " AND `area` = :area";
}

$query = $pdo->prepare($sql);

if ($name)
    $query->bindValue(':name', '%' . $name . '%');
if ($type)
    $query->bindValue(':type', $type);
if ($category)
    $query->bindValue(':category', $category);
if ($group)
    $query->bindValue(':group', $group);
if ($area)
    $query->bindValue(':area', $area);

$query->execute();

possible duplicate of [Row count with PDO](http://stackoverflow.com/questions/883365/row-count-with-pdo) — Gerald Schneider, Jul 13 '15 at 10:32
@GeraldSchneider, our questions are little bit different and I'm just looking if there is some easier way to do this. For example can I add things to both `$sql` and `$count` using the same concatenating assignment operator `.=` — lingo, Jul 13 '15 at 10:38
You are asking exactly the same. The second answer (not the accepted one) explains the possibilities pretty good. — Gerald Schneider, Jul 13 '15 at 10:41
possible duplicate of [How to get the total found rows without considering the limit in PDO?](http://stackoverflow.com/questions/4596155/how-to-get-the-total-found-rows-without-considering-the-limit-in-pdo) — andrew, Jul 13 '15 at 10:52
With SQL you either return information such as counts about 'groups' of data, or, you return the individual data rows. It is not easy to do both in one query, in one pass of the data. — Ryan Vincent, Jul 13 '15 at 15:27

Your Common Sense · Answer 1 · 2015-07-13T10:49:19.027

0

Although rowCount() most likely working, you don't need it anyway. That's most useless function for selects.

You either need your data OR count, but not both. Since you're selecting data, you don't need rowCount() then. Just count your data.

In case you need only count but no data, then you ought to select the very count right from database. Means you don't need rowCount() again.

edited Jul 13 '15 at 10:49

answered Jul 13 '15 at 10:36

Your Common Sense

156,878
40
214
345

Do you mean that I don't need an extra query for counting rows? I need number of rows because of pagination. – lingo Jul 13 '15 at 10:46

score -1 · Answer 2 · answered Jul 13 '15 at 15:18

-1

Add the count to your query:

SELECT *, COUNT(*) as rowCount FROM customers`

Then when you fetch:

get the value $row['rowCount'];

answered Jul 13 '15 at 15:18

meda

45,103
14
92
122

Pdo count rows from SELECT query with POST params

2 Answers2