Can not get correct count of grouped rows

lpa

$search = 'text';

$ko = mysqli_query($db1,"select count(uid) as total from users    
where txt like '$search%' group by uid limit 0,20") or die('Error');
    $far = mysqli_fetch_array($ko);

$total1 = $far['total'];

An example:
text, uid

text 1
text 2
text 2
text 2
text 3
text 4

$total1 should return number 4 because there are 3 rows with the same uid. But... it returns 6, so it counts all rows instead of counting grouped rows. I want to get the total number of grouped rows. Any help?

laserlight

lpa wrote:
$total1 should return number 4 because there are 3 rows with the same uid. But... it returns 6, so it counts all rows instead of counting grouped rows

That sounds strange to me. Because of the group by, instead of a single row with 6, I would expect a result set like this:

Since you only want a count of the number of distinct groups of uids, perhaps you could try:

select count(distinct uid) as total from users where txt like '$search%'

lpa

Yes, with distinct it works correctly, however I have read that it's not recommended to use distinct...
Thanks, anyway,

laserlight

lpa wrote:
Yes, with distinct it works correctly, however I have read that it's not recommended to use distinct...

Possibly, but if so, then it is your database design that is at fault: DISTINCT is appropriate here. For example, why does your users table not have unique uid values?

What article did you read that recommended against the use of DISTINCT? A link would be good.

lpa

My users table have unique uid values. This is another table just for searching purposes - to find people by name, surname, name and surname or nickname.
The main user table there are 3 columns for names/surnames/nicknames: name, surname, nickname. To find people it's necessary to run a comlicated query:
select * from users where name='$search%' or surname='$search%' or nickname='$search%' ... and search term can be not just a single word but also name+surname or surname+name combination...
so, I created one more table that contains all names, surnames and nicknames. Now I can search easier

select from table2 where txt like '$search%'

name surname - ID 1
surname name - ID 1
nickname - ID 1
name surname - ID 2
surname name - ID 2
nickname - ID 2

etc

I don't remember where dod I read that DISTINCT is not recommended. But Google finds a lot of questions about DISTINCT. For example here

laserlight

lpa wrote:
My users table have unique uid values. This is another table just for searching purposes - to find people by name, surname, name and surname or nickname.
The main user table there are 3 columns for names/surnames/nicknames: name, surname, nickname. To find people it's necessary to run a comlicated query:
select * from users where name='$search%' or surname='$search%' or nickname='$search%' ... and search term can be not just a single word but also name+surname or surname+name combination...
so, I created one more table that contains all names, surnames and nicknames. Now I can search easier

select from table2 where txt like '$search%'

Ah, then you have an alternative that is directly derived from your usual query:

SELECT COUNT(*) FROM users WHERE uid IN (SELECT uid FROM table2 WHERE txt LIKE '$search%');

However, I am inclined to think that this remains simpler:

SELECT COUNT(DISTINCT uid) FROM table2 WHERE txt LIKE '$search%';

lpa wrote:
I don't remember where dod I read that DISTINCT is not recommended. But Google finds a lot of questions about DISTINCT. For example here

The fact that there are many such articles does not mean much: if you actually read those articles, you would see that they do not apply to you. The article that you gave as an example notes that this has to do with a bug in MySQL, so whether or not it applies to you depends on whether you are affected by the bug: changing your code to preemptively avoid such a bug would be foolish as you could well run into another bug, so you should test if you are concerned.

lpa

Thanks so much.