How do I count unique rows in php pdo?

0
votes

Here's my usual way of counting rows...

$query = "SELECT * FROM users";
$stmt = $db->prepare($query);
$stmt->execute();
$count = $stmt->rowCount();

This will count all rows, even if I use a WHERE clause, it'll still count every row that meets that condition. However, let's say I have a table, we'll call it tokensEarned (that's my actual table name). I have the following data...

user_id = 1,2,4,5,8,8,2,4,3,7,6,2 (those are actual rows in my table - clearly, user 1 has 1 entry, 2 has three entries, etc.) In all, I have 12 entries. But I don't want my query to count 12. I want my query to count each user_id one time. In this example, my count should display 8.

Any help on this? I can further explain if you have any specific questions or clarification you need. I would appreciate it. Thank You.

3
phpmysqlpdorowcount
Are you saying you want to do SELECT *, but then retrieve the distinct user count, all in a single query? - Tim Biegeleisen
I'll expand on this and you can choose to read it. Might give you a little insight. I have a pch-type of website. Scratch cards, etc. to possibly when real money. You can spend your tokens on sweepstake entries. In the sweepstakes entry table, I want to automatically count the number of entries (that is easy) but also the number of UNIQUE users that have entries in the database. So if there's 250,000 entries (rows) and 5,000 users with entries, I want to be able to display the number of users with entries, not all 250,000 rows. - Donald Faulknor
@TimBiegeleisen yes, I believe that's what I am looking for. - Donald Faulknor
If all you need are counts, then don't select the full records, just select the counts. - Tim Biegeleisen

3 Answers

1
votes

The following query will yield the distinct user count:

$query = "SELECT COUNT(DISTINCT user_id) AS cnt FROM users";
$stmt = $db->prepare($query);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo "distinct user count: " . $row['cnt'];

It isn't possible to get all records and the distinct count in a single query.

Whether you use the query above or you return all the actual distinct rows really depends on whether you need the full records. If all you need are the counts, then it is wasteful to return the data in the records, and what I gave above is probably the best option. If you do need the data, then selecting all distinct rows might make more sense.

0
votes

You can use distinct in mysql to select only unique fields in your table.

$query = "SELECT distinct user_id FROM users";
$stmt = $db->prepare($query);
$stmt->execute();
$count = $stmt->rowCount();
0
votes

Change your query to the following, this way you only shows the unique user_id:

$query = "SELECT DISTINCT user_id FROM users";