PHP And MySQL Search Based on two fields

Question

Alright, so I understand how to do a mysql query to return results based on one search parameter. But I have a table that contains usernames and data. The data should be unique to each username, but I want to return any instances where that data might overlap over multiple usernames. An example would be

    Username | Data Field
    ----------------------------------------------
    Test     | Abcd1234
    Test2    | efgh5678
    Test3    | Abcd1234
    Test4    | efgh5678

I want my php script to return all instances where duplicate entries are found in the data field. Note that, neither the data field nor username field are unique in this table. The table gets populated whenever the user completes an action, so there should be many entries for each username, but each time they should have the same data field. I only want to check when two different usernames have the same data field. Does anyone have any idea how this can be done in php or a mysql select statement? It may take more than one query and that is okay. I've tried searching on how to find duplicate emails based on usernames, but the results I found were more on preventing duplicate registrations in the first place.

Are you trying to fix this table to remove duplicates? BEcause you have a valid answer below on trying to find duplicates, but if your intent is to try to fix the duplicates, there is another approach to take. — Mike Brant
– Mike Brant, Commented Aug 25, 2014 at 19:09

Gordon Linoff · Accepted Answer · 2014-08-25 19:52:55Z

1

Basically, you want to count the number of unique users for each data field. This is an aggregation query:

select data, count(distinct username) as numusers
from table t
group by data;

You should be able to get the unique user names with this query:

select data, count(distinct username) as numusers,
       group_concat(distinct username) as users
from table t
group by data
having count(distinct username) > 1;

This will create a comma separated list of users "using" the same data.

edited Aug 25, 2014 at 19:52

answered Aug 25, 2014 at 19:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

swigg256 Over a year ago

Thanks, that looks like it will work. Now I just need to make phpmyAdmin accept it.

Collectives™ on Stack Overflow

PHP And MySQL Search Based on two fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related