Group Rows With the Same Value in a Certain Column

I have a table called visitors with the following values:

id    ip             date                    time
1    98.112.43.45   2011-01-11 14:00:10     5
2    98.112.43.45   2011-01-11 11:49:00     1040
3    192.150.3.7    2011-01-11 12:06:38     2
4    98.112.43.45   2011-01-11 12:06:23     188

Is there a way to group them by IP when using select and get something like:

IP 98.112.43.45

• 5 second visit on 2011-01-11 14:00:10
• 1040 second visit on 2011-01-11 11:49:00
• 188 se开发者_JAVA技巧cond visit on 2011-01-11 12:06:23

IP 192.150.3.7

• 2 second visit on 2011-01-11 12:06:38

In SQL, all the rows you get back have the same structure of columns. So you can't produce that outline view directly from SQL. But if you simply sort the rows by IP and DATE (ORDER BY IP, Date), you'll get the four rows you want in more-or-less the order you want. You then need to present them as an outline in a report generator or on a web page using code.

The only sorting issue you have is that to get exactly the order you specified, you'll need to disassemble the IP addresses into four integer values and ORDER BY those four integers.

SELECT * FROM `visitors` ORDER BY ABS('ip')

This will sort the ip address properly. Then depending what language you are using you can display the other data. I could provide examples in PHP