CODEWITHSUNDEEP
sqlpdocount
jdepypere
jdepypere

Reputation: 3553

PDO SQL issue displaying multiple rows when using COUNT()

To display my results from PDO, I always use following PHP code for example:

$STH = $DBH->prepare("SELECT logo_id, guess_count, guessed, count(id) AS counter FROM guess WHERE user_id=:id");
$STH->bindParam(":id",$loginuser['id']);
$STH->execute();
while($row = $STH->fetch()){
    print_r($row);
}

Now the issue is that I only get one result. I used to use $STH->rowCount() to check the amount of rows returned, but this method isn't really advised for SELECT statements because in some databases it doesn't react correctly. So I used the count(id) AS counter, but now I only get one result every time, even though the value of $row['counter'] is larger than one.

What is the correct way to count the amount of results in one query?

Upvotes: 0

Views: 85

Answers (2)

spencer7593
spencer7593

Reputation: 108410

If you want to check the number of rows that are returned by a query, there are a couple of options.

You could do a ->fetchAll to get an array of all rows. (This isn't advisable for large result sets (i.e. a lot of rows returned by the query); you could add a LIMIT clause on your query to avoid returning more than a certain number of rows, if what you are checking is whether you get more than one row back, you would only need to retrieve two rows.) Checking the length of the array is trivial.

Another option is to run a another, separate query, to get the count separately, e.g.

SELECT COUNT(1) AS counter FROM guess WHERE user_id=:id

But, that approach requires another round trip to the database.

And the old standby SQL_CALC_ROUND_ROWS is another option, though that too can have problematic performance with large sets.

You could also just add a loop counter in your existing code:

$i = 0;
while($row = $STH->fetch()){
  $i++
  print_r($row);
}
print "fetched row count: ".$i;

If what you need is an exact count of the number of rows that satisfy a particular predicate PRIOR to running a query to return the rows, then the separate COUNT(1) query is likely the most suitable approach. Yes, it's extra code in your app; I recommend you preface the code with a comment that indicates the purpose of the code... to get an exact count of rows that satisfy a set of predicates, prior to running a query that will retrieve the rows.

If I had to process the rows anyway, and adding LIMIT 0,100 to the query was acceptable, I would go for the ->fetchAll(), get the count from the length of the array, and process the rows from the array.

Upvotes: 1

peterm
peterm

Reputation: 92795

You have to use GROUP BY. Your query should look like 

SELECT logo_id, guess_count, guessed, COUNT(id) AS counter 
  FROM guess 
 WHERE user_id=:id
 GROUP BY logo_id, guess_count, guessed

Upvotes: 1

Related Questions