How to Limit mySQL queries per hour per IP?

bookbuyer2000

I want to generate an error message that says "Sorry visitor, You're only allowed to do 25 mysql lookups per hour on our XYZ table!":p

Well, I already have a table called "XYZ" which my visitor wants to query. But I don't want them querying it more than 25 times an hour.....
So I'm guessing that I need ANOTHER mySQL table to hold 3 things:
1.) the visitor's IP address.
2.) the stuff the visitor is trying to look up

3.) a timestamp, so we can count the queries per hour

CREATE TABLE Limit_this_Visitor (
Visitors_IP_address varchar(20) NOT NULL,
Stuff_Visitors_Lookin_Up varchar (20) NOT NULL,
What_Time_it_is timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

);

So my question is: what's the PHP code that will check the new table (the "Limit_this_Visitor" table), and count how many queries a specific visitor from a specific IP address has looked up within the past hour. If less than 25 queries, then great: go ahead and do the lookup in the main XYZ table! However, if MORE than 25 queries, generate that error message.

I KNOW this seems like a really complicated question, but I don't know how else to make it simpler.😕 I've tried researching this on the net, but can't seem to find an answer. I know this type of "limiter" script is used a lot -- e.g. "sorry, you can't post more than 5 comments per day," or, "sorry, you've entered the wrong password too many times, come back tomorrow, etc."

Thank you!!🙂

bradgrafelman

You do realize, however, that some users have dynamic IPs that vary even from one request to another, and some groups of users (even hundreds or thousands at a time) all share a single IP (e.g. at a corporate office)?

bookbuyer2000

bradgrafelman;10987206 wrote:
You do realize, however, that some users have dynamic IPs that vary even from one request to another, and some groups of users (even hundreds or thousands at a time) all share a single IP (e.g. at a corporate office)?

Yes, I know. The page I would build would be accessed only by those with static/non-changing IP's.

traq

bookbuyer2000;10987208 wrote:
Yes, I know. The page I would build would be accessed only by those with static/non-changing IP's.

...and how do you know that? :queasy:

Derokorian

$onehour = 60*60;
$onehourago = time() - $onehour;
$userip = $_SERVER['REMOTE_ADDR'];

$sql = sprintf("SELECT COUNT(*) as count FROM Limit_this_Visitor WHERE ip='&#37;s' AND timestamp > %d",$userip,$onehourago);
$result = mysql_query($sql);
$count = mysql_fetch_array($result);
$count = $count['count'];
if( $count > 25 ) {
   echo 'Sorry you cannot perform any queries right now.';
} else {
   // the query you want to limit and its stuff
}

Simplified, without error checking. HTH

bookbuyer2000

traq;10987232 wrote:
...and how do you know that? :queasy:

I'm sorry, I didn't understand your question. It didn't come through very clearly. Would you mind typing it out again a number of times? Thank you.

bookbuyer2000

Derokorian;10987256 wrote:

$onehour = 60*60;
$onehourago = time() - $onehour;
$userip = $_SERVER['REMOTE_ADDR'];
.........Simplified, without error checking. HTH[/QUOTE] Thank you very much! I don't know if it works, yet, or not, but I will give it a try now. Thank you again! (This thread took a while longer for a reply, and I almost gave up hope!)