0

I am writing some PHP / MySQL to detect excessive site visits. While I find it simple enough to detect, for any given IP address, how many times that person has visited in say 24 hours and so whether they have exceeded some number that is the visit maximum, what I am struggling with is some sql that will pick out the overall trend - how many IP's have each visited more than the threshold number of times in the period. Please consider the following simplified database table which I hope will explain more clearly.

 VisidID        VisitIP      VisitDate
 ---------------------------------- 
    1           n.n.n.01     (yesterday) 
    2           n.n.n.02     (today) 
    3           n.n.n.01     (today) 
    4           n.n.n.03     (today) 
    5           n.n.n.03     (today) 
    6           n.n.n.03     (today) 
    7           n.n.n.04     (today) 
    8           n.n.n.05     (today) 
    9           n.n.n.04     (today) 
    10          n.n.n.06     (today) 
    11          n.n.n.05     (today) 
    12          n.n.n.07     (today)
     ------------------------------------

So, assuming I set the maximum allowed visit threshold to 2 (just to keep my example table here small) the result I am looking for from this table query is '3' since there were three IP addresses (n.n.n.03, n.n.n.04 and n.n.n.05) whose visits today each equalled or exceeded the visit threshold of 2 visits.

How would I capture that in a MySQL query, and would it be a fast and efficient or burdonsome query to run?

Frankie
  • 596
  • 3
  • 24

2 Answers2

2

Using the HAVING clause, this query will return those having a count higher than 2 - 

SELECT `VisitIP`, COUNT(`VisitIP`) AS `CountIP`
FROM `table`
GROUP BY `VisitIP`
HAVING `CountIP` > 2

To count the total meeting the condition you can do something like this - 

SELECT COUNT(*) AS `TotalOverTwo` FROM
    (SELECT `VisitIP`, COUNT(`VisitIP`) AS `CountIP`
     FROM `table`
     GROUP BY `VisitIP`
     HAVING `CountIP` > 2 
    ) `table`
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
  • Hi Jay. Your answer appears to be correct but before I mark it as such, a couple of questions. At the moment it returns a number of rows that I then need to count to get the final answer. Am I dreaming to think there is a way to get it to return the integer that reflects the number of matching cases? And lastly, how efficinent would you say this query is? I might have to use it every single time my script runs you see. Thank you. – Frankie Feb 17 '15 at 19:38
  • First: this query is pretty efficient and shouldn't cause a big load unless the table get unwieldy and it is not indexed properly. The index should be on VisitP. I'll edit to add a way to count the rows meeting the criteria. – Jay Blanchard Feb 17 '15 at 19:42
  • 1
    Hi Jay! On multiple fronts I have to say a big big thank you. Thank you for the advice on which column of the table to makethe Index, thanks for the SQL Fiddle steer (will take some mulling over for a slowcoach like me!) and most of all, thank you for both flavours of your solution. You are a gentleman and a scholar. Cheers Jay! – Frankie Feb 17 '15 at 20:01
  • 1
    @JayBlanchard Gotta love HAVING good company for sure matey ;-) – Funk Forty Niner Feb 20 '15 at 13:30
  • 1
    Roger that @Fred-ii- Good company is worth HAVING! ;-) – Jay Blanchard Feb 20 '15 at 13:32
  • Si signore @JayBlanchard – Funk Forty Niner Feb 20 '15 at 13:32
-1

just write this simply query and your problem will be solved !

select count(`VisitIP`) as totalVisits from table group by(`VisitDate`) HAVING `totalVisits` > 2;
israr ahmad
  • 1
  • 4
  • is `VisitIP` a string? – Jonathan Kuhn Feb 17 '15 at 18:36
  • Yes, a (varchar) string. – Frankie Feb 17 '15 at 18:57
  • @FrankAnderson Jonathan is asking if Israr intended to use a string instead of a column name which, for all intents and purposes in a query, will not work the way it is written. – Jay Blanchard Feb 17 '15 at 19:11
  • I think I see, sorry for my misunderstanding. Yes it doesn't seem capable of working since there is no evidence of where I might specify the threshold value (2 in my example). I can't see what he was thinking though. Sorry my understanding of SQL is a bit basic and clunky so forgive me if I'm slow to catch on. – Frankie Feb 17 '15 at 19:21
  • Thank you Israr, but unfortunately I simply can't get this to work, even after the edit. Probably my bad, but thank you none the less. – Frankie Feb 17 '15 at 19:58