0

I need to get all matches in one column using PHP.

Example:

My table:

IP adress           UserID
192.168.2.1         1
192.168.2.1         2
192.168.2.2         3
192.168.2.1         3
192.168.2.3         4
192.168.2.3         5
192.168.2.4         6

(I log IP adress always when user log in, so there can be more IP adresses for each userid)

Output what I need:

IP adress: 192.168.2.1 uses UserID´s: 1, 2, 3
IP adress: 192.168.2.3 uses UserID´s: 4, 5

(only if same IP adress use more users)

Is it possible? Maybe grab all lines into PHP and then try to find matches, or can it be done by MySQLi? Thanks.

JackDavis
  • 117
  • 1
  • 4
  • 17
  • Possible duplicate of http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field/276951 – tsnorri Mar 15 '15 at 19:16
  • Hi tsnorri, sorry, but I dont think so. I need something other. I do not use multiple tables. – JackDavis Mar 15 '15 at 19:25
  • 1
    What have you tried? AS it stands, this question is *too broad* because there could be many potential approaches to what you want. What if someone posts code, and you say "Sorry I can't do it that way because of XYZ"? You need to make a start on doing this yourself, post your code, and then we can help with *your* code. – James Mar 15 '15 at 19:34
  • 1
    Actually what tsnorri suggest is correct. The solution is on one table and you can as well add count and where, so that you filter out addresses with just one UserID. – Fox Mar 15 '15 at 20:24
  • @JackDavis check the answer I posted – helloworld Mar 15 '15 at 20:33

1 Answers1

3

I don't know if this is the best answer (probably not) but this will get you ALL the ips in the format you described, plus the number of appearances for ids on distinct ip.

SELECT `ip` AS ip1,  GROUP_CONCAT(`id` SEPARATOR ', ') AS ids, 
     (SELECT COUNT(`ip`) FROM `table` WHERE `ip` = ip1) AS appearances 
FROM `table` GROUP BY ip
helloworld
  • 527
  • 6
  • 21