[SOLVED] query help - select distinct and sort

[sandrob57]

ok this is my current query. Not a good start, because there is already a syntax error, but it should give you an idea of what I want to do:

 

$result = dbquery("SELECT DISTINCT ip FROM fusion_ip LIMIT $rowstart,20");

 

Basicaly my website records every ip a user has logged on from and keeps it saved in a table called fusion_ip.

 

The columns for the table are ip_id (a auto increment id assigned to each ip), ip (the ip) and user_id (the id of the user the ip belongs to).

 

As an example, the db may look like this

 

ip_id__user_id___ip

1_____33______111.212.32.32

2_____1_______111.212.32.32

3_____1_______43.12.1.3

4_____5_______67.86.54.2

5_____2_______72.65.5.4

 

What I am trying to do is make a list of every IP and sort them by the most active. So the list would look like this:

 

[____IP____][_Users Using This Ip__]

43.432.424.2________4

5.4.32.1____________2

 

etc. etc.

 

So how the hell would I make a query capable of accomplishing this?

 

EDIT: im guessing a i need a SUM() somewhere in there, or an order by?

[sandrob57]

Ok I came up with this (it doesnt work, but I feel it's a step in the right direction)

 

$result = dbquery("SELECT DISTINCT ip,SUM(ip) AS ip_count FROM fusion_ip LIMIT $rowstart,20 ORDER BY ip_count, DESC");

 

edit: still cant get it to work  ??? 

[per1os]

Why are you summing the IP, that will essentially add up the ips together...

 

$result = dbquery("SELECT ip, COUNT(ip) AS ip_count FROM fusion_ip GROUP BY ip ORDER BY ip_count DESC LIMIT $rowstart,20");

 

Not sure if my sql is correct, but that is the idea, you want to use the GROUP BY Expression with the COUNT expression

[sandrob57]

Ahh ok that makes sense! However,

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '20' at line 1

[sandrob57]

figured it out, i hadn't yet set $rowstart