3

It has long been known that PDO does not support COUNT(*) and a query like below would fail as it doesn't return any affected rows,

$q = $dbc -> prepare("SELECT COUNT(*) FROM table WHERE id = ?");
$q -> execute(array($id));
echo $q -> rowCount();

Doing some research I found that you can also get the row count using other methods of count and not using count at all, for example the following query is supposed be the same as above but will return correct for PDO,

$q = $dbc -> prepare("SELECT 1 FROM table WHERE id = ?");
$q -> execute(array($id));
echo $q -> rowCount();

There are various sources on the internet claiming that;

"SELECT COUNT(*)
"SELECT COUNT(col)
"SELECT 1

Are all the same as each other (with a few differences) so how come using mysql which PDO cannot properly return a true count, does 

"SELECT 1

work?

Methods of count discussion

Why is Select 1 faster than Select count(*)?

Community
  • 1
  • 1
NovacTownCode
  • 65
  • 1
  • 7
  • 3
    `SELECT 1` will only return a value of 1 for that column - the other options count not null values. I think you meant to write `COUNT(1)` – OMG Ponies Nov 22 '11 at 03:50
  • @OMG: Unless the OP is only doing an existence check, and then `select exists(select 1 ...` would be the my choice. – mu is too short Nov 22 '11 at 03:53
  • The SO question you refer to is for a different database -- there are duplicates of this which are currently escaping me but this is close: http://stackoverflow.com/questions/1697137/countid-vs-count-in-mysql – OMG Ponies Nov 22 '11 at 03:54
  • `id` is the primary key, right? Otherwise the two queries will have vastly different results. – Thilo Nov 22 '11 at 03:55
  • 1
    @mu is too short: Agreed – OMG Ponies Nov 22 '11 at 03:55
  • Actually I think `echo $q -> rowCount();` does work with `SELECT 1`. You don't want the `rowCount()` call with `SELECT COUNT(*)` though; you want the value returned. – Ray Toal Nov 22 '11 at 03:56
  • @RayToal Yes it does work and it is baffling me to why it does?!? – NovacTownCode Nov 22 '11 at 03:58
  • Well, according to [the docs](http://php.net/manual/en/pdostatement.rowcount.php) `rowCount` might work for SELECT statements -- it is just not guaranteed -- so maybe you got lucky? – Ray Toal Nov 22 '11 at 04:04
  • I am running a few tests now with other queries to see how they return, I'll keep you posted. – NovacTownCode Nov 22 '11 at 04:06
  • Check my answer, that's all the code you need. – xmarcos Nov 22 '11 at 04:35

3 Answers3

6

Oh. You are confusing everything.

  1. PDO do not interfere with SQL queries. It support EVERYTHING supported by SQL.
  2. When doing COUNT(*) you shouldn't use rowcount at all, as it just makes no sense. You have to retreive the query result instead.
  3. Dunno what "various sources" you are talking about but COUNT(*) and COUNT(col) (and even COUNT(1)) are the same and the only proper way to get count of records when you need no records themselves.

COUNT is an aggregate function, it counts rows for you. So, it returns the result already, no more counting required. Ad it returns just a scalar value in the single row. Thus, using rowcount on this single row makes no sense

SELECT 1 is not the same as above, as it selects just literal 1 for the every row found in the table. So, it will return a thousand 1s if there is a thousands rows in your database. So, rowcount will give you the result but it is going to be an extreme waste of the server resources.

there is a simple rule to follow:

Always request the only data you need.

If you need the count of rows - request count of rows. Not a thousand of 1s to count them later.
Sounds sensible?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
6

PDO does not support COUNT(*)

WTF? Of course PDO supports COUNT(*), you are using it the wrong way.

$q = $dbc->prepare("SELECT COUNT(id) as records FROM table WHERE id = ?");
$q->execute(array($id));    
$records = (int) $q->fetch(PDO::FETCH_OBJ)->records;

If you are using a driver other than MySQL, you might have to test rowCount first, like this.

$records = (int) ($q->rowCount()) ? $q->fetch(PDO::FETCH_OBJ)->records : 0;
xmarcos
  • 3,298
  • 2
  • 20
  • 27
  • there is no use for the rowcount here. you will get your 0 without it. – Your Common Sense Nov 22 '11 at 04:21
  • I use it because i've experience erratic behavior on different drivers, if i remember correctly, Postgre will return 0 on `rowCount`. If you are using MySQL, you can be safe using just: `$records = $q->fetch(PDO::FETCH_OBJ)->records;`. – xmarcos Nov 22 '11 at 04:31
0

Best way I think to test if a line exist in your database is to perform.

SELECT 1 FROM table WHERE condition LIMIT 1

If it find a row it will stop and tell you there is a line. If it don't and you have an index on your where clause column it will also goes very fast to see there are none available.

Nicolas Manzini
  • 8,379
  • 6
  • 63
  • 81