Find Multiple user's accounts in mySQL with PHP [duplicate]
This question already has an answer here:
Finding duplicate values in MySQL
23 answers
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.
php mysql algorithm sorting web
asked Jan 1 at 19:09
MichaphpMichaphp
63
marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 1 at 19:15
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
Finding duplicate values in MySQL
23 answers
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.
php mysql algorithm sorting web
asked Jan 1 at 19:09
MichaphpMichaphp
63
marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 1 at 19:15
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
Finding duplicate values in MySQL
23 answers
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.
php mysql algorithm sorting web
asked Jan 1 at 19:09
MichaphpMichaphp
63
This question already has an answer here:
Finding duplicate values in MySQL
23 answers
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.
This question already has an answer here:
Finding duplicate values in MySQL
23 answers
php mysql algorithm sorting web
php mysql algorithm sorting web
asked Jan 1 at 19:09
MichaphpMichaphp
63
asked Jan 1 at 19:09
MichaphpMichaphp
63
edited Jan 1 at 19:13
Michaphp
asked Jan 1 at 19:09
MichaphpMichaphp
63
asked Jan 1 at 19:09
MichaphpMichaphp
63
asked Jan 1 at 19:09
MichaphpMichaphp
63
63
marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 1 at 19:15
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Shadow mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 1 at 19:15
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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
answered Jan 1 at 19:13
scaisEdgescaisEdge
95.3k105071
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Jan 1 at 19:13
scaisEdgescaisEdge
95.3k105071
add a comment |
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
answered Jan 1 at 19:13
scaisEdgescaisEdge
95.3k105071
add a comment |
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
answered Jan 1 at 19:13
scaisEdgescaisEdge
95.3k105071
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
answered Jan 1 at 19:13
scaisEdgescaisEdge
95.3k105071
answered Jan 1 at 19:13
scaisEdgescaisEdge
95.3k105071
answered Jan 1 at 19:13
scaisEdgescaisEdge
95.3k105071
answered Jan 1 at 19:13
scaisEdgescaisEdge
95.3k105071
95.3k105071
add a comment |
add a comment |
