Find Multiple user's accounts in mySQL with PHP

Question

Can you help me, please, to solve this. There is a table (user's access log) in mysql: "user_id" "Ip" "access_date".
Is it possible to display all User's double accounts (multiaccounts, users with different ID, but used same IP), without huge load on mysql & server resources?

Like:
"10" "155.166.11.2" "2018-01-22 13:08:36"
"122" "127.0.0.1" "2018-01-22 13:19:00"
"13" "144.11.11.4" "2018-01-31 17:16:56"
"10" "127.0.0.1" "2018-01-31 17:26:35"
"99" "155.166.11.2" "2018-01-31 17:26:55"
"13" "12.11.22.4" "2018-01-31 17:43:56"
"18" "145.106.11.2" "2018-01-31 18:50:18"
"11" "144.11.11.4" "2018-01-31 18:54:18"
"10" "155.166.11.2" "2018-01-31 19:08:26"

Result:
"10, 99, 122" - same user
"11, 13" - same user.

score 1 · Accepted Answer · answered Jan 01 '19 at 19:13

you could use a subquery with count group by ip > 1

if you want the user on same result

select m.ip, group_concat(m.user_id)
from my_table  m.ip
inner join (
  select ip 
  from my_table 
  group by ip 
  having count(*) > 1 

) t on t.ip = m
group by m.ip

otherwise if you want user_id on different row

select m.user_id
from my_table  m.ip
inner join (
  select ip 
  from my_table 
  group by ip 
  having count(*) > 1 

) t on t.ip = m

Find Multiple user's accounts in mySQL with PHP

1 Answers1