[SOLVED] SIMPLE MYSQL QUERY

[Goose87]

Hey everyone,

 

I have a quick problem with my script.

 

Basically I'm creating an admin page so that some people that moderate my site can check things like multiple registrations from the same IP.

 

My query which selects the IP and counts the number of times that one is registered is:

 

SELECT ip, COUNT(id) FROM a_users GROUP BY ip

 

This works fine and displays:

 

ip       COUNT( id )

*IP ONE*          14

*IP TWO* 4

*IP THREE* 1

*IP FOUR* 1

 

Basically, I want to change the query so that is only brings back results that have a COUNT(id) value that is higher than 2.

 

Thanks a lot,

 

Goose.

 

 

[roopurt18]

SELECT ip, COUNT(id) FROM a_users GROUP BY ip WHERE COUNT(id) > 2

 

Be warned though that users in public locations (libraries, computer labs, college campuses, etc.) will all come from the same IP until that location receives a new IP address from it's ISP.

[Goose87]

I'm still getting the error i got when i tried to do that before.

 

The error is:

 

#1064 - 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 'WHERE COUNT( id ) > 2

LIMIT 0, 30' at line 1

 

when i typed:

 

SELECT ip, COUNT( id ) 
FROM users
GROUP BY ip
WHERE COUNT( id ) >2
LIMIT 0 , 30

[roopurt18]

Oops, I made a booboo.

SELECT ip, COUNT( id ) 
FROM users
WHERE COUNT( id ) >2
GROUP BY ip
LIMIT 0 , 30

[Goose87]

i've tried that too, and i get the error:

 

#1111 - Invalid use of group function

[roopurt18]

How about this:

SELECT 
  `ip`, COUNT( * ) AS `n` 
FROM `users`
WHERE `n`>2
GROUP BY `ip`
LIMIT 0 , 30

[sasa]

SELECT ip, COUNT(id) FROM a_users GROUP BY ip HAVING COUNT(id) > 2

[Goose87]

EXCELLENT!!

 

It works!  Thanks a lot sasa I shall remember the use of that syntax and pass my knowledge onto others.

 

Thanks again

 

Goose.