issue using fetch() within a PDO SELECT COUNT(*) type

Question

Hey guys I have the following:

$sql = "SELECT COUNT(*) pk_ID FROM "._DB_NAME."."._DB_PREFIX."_Device
                  WHERE vc_Serial = :deviceID";
$sth=$DBH->prepare($sql);
$sth->execute(array(':deviceID' =>$device_id));

//give amount of results
$row = $sth->fetch(PDO::FETCH_ASSOC);
$count = $sth->rowCount();

With this code I use to see how many results are effected with the $count and than store a value $fk_DeviceID = $row['pk_ID']; The issue is I am not getting the correct ID returned. I don't know if the SELECT COUNT(*) is effecting anything or whatnot.

Suggestions and thoughts appreciated!

score 1 · Accepted Answer · edited May 23 '17 at 12:10

Firstly, it looks like you need to change your query to this:

SELECT  
    COUNT(*) as pkCount,
    pk_ID 
FROM 
    "._DB_NAME."."._DB_PREFIX."_Device 
WHERE 
    vc_Serial = :deviceID
group by
    pk_id

As it seems you are trying to get two columns, so I added a comma between the terms, then added a group by statement as you are counting on of the columns - therefore you need to group results based on aggregation.

Secondly, lets assume there is only a single pk_ID. The $count variable will only show 1, not the number of items counted in the database as it counts how many rows there are in the recordset.

You probably want to do this instead:

echo $row['pkCount'];

This will show the number of rows counted/aggregated in the query itself, which will likely be different to the number of rows actually returned by the query.

Edit (Per comment):

Firstly, the SQL itself was a little borked. While I can't really jump in and explain too much more than I did here without remaking the wheel, I can provide a link I think will be useful to you you which is a very lengthy Q&A that I wrote explaining a lot of SQL basics/intermediates with queries and aggregates and joins and all sorts of good things. Please, do read this, it will do more than sort out the first part of your comment.

As for the second part of my answer, when you do an aggregate in a query (such as count matching rows) the number is returned inside the row like this:

ID    Count
1     5
2     8

So when you fetch an array for a row, you will get something like this:

$array=('ID' => 1, 'Count' => 5)

However, when you run a num_rows() it counts all the rows in the recordset - which in the above would be 2, as there are two rows in the results shown.

I hope that helps further, if you still want something in more detail, let me know and I will see what I can do.

Could you explain this to me a little more as to what was wrong? — David Biga, Oct 09 '13 at 00:15

issue using fetch() within a PDO SELECT COUNT(*) type

1 Answers1