Method for preventing a visitor from making more than 2 posts per day (PHP MySQL)

I've been searching for hours for a solution and either I can't find the right words to describe what I need in the search engines, or I'm just bad at finding things.

Here's the situation: I'm currently making a website and I have a section where visitors can post messages, kind of like a "guestbook". However, I want to limit an IP address to 2 posts per day. I thought it'd be easy at first, just insert the IP address, date, and time along with all the other data into the mysql table and then do a comparison of the times and dates from each ip. Something like that.... Then as I got to working on it, so many questions came to mind. How would I compare the entries if the IP has posted more than 2 messages over a number of days? How would you even start comparing the date and time accurately? What's the best time and date format for comparison? Is there a better way such as self expiring data that you can compare to? And so on.. Sorry if this seems like such a simple task but I am having a hard time finding the answers. Tried googling everything such as "mysql 开发者_StackOverflow中文版php time limit", "php mysql prevent spam timer", "php mysql timer like megavideo" etc.

Just to clarify, I need a good method for preventing a visitor from posting more than 2 message per day. This is a "guestbook" kind of thing so any visitor can post. No logins.

Thank you in advance!

---

Create a table called exceptions with structure id, ip, date

and when user posts something, execute a query like :

$ip = $_SERVER['REMOTE_ADDR'];
$query = "insert into exceptions set ip = '{$ip}',date = 'NOW()'";

and before let user post something add this:

$ip = $_SERVER['REMOTE_ADDR'];
$count = mysql_num_rows(mysql_query("select count(*) 
         from exceptions where ip = '{$ip}' 
         and date > DATE_SUB(NOW(), INTERVAL 24 HOUR)"));

if($count> 2) { 
  echo 'You have exceeded posting limits, please try again in 24 hours';
  exit; 
}

---

something like this:

posts_in_last_24_hours = select count(*) from posts where ip=? and date>(NOW()-86400) -- 24 hours ago
if that is 2:
    fail
else:
    post message

---

If you record in your database the IP on every post, you can check if it has posted more than 2 times in the last X days with a query counting the number of records with that IP in the last X days, something like this:

 SELECT COUNT (IP) FROM posts_log WHERE IP = 'the_user_ip' AND post_date > (NOW() - (X * (60 * 60 * 24)))

---

Using a trigger

Record the IP and store that in the guestbook table.

Then create a trigger like so:

DELIMITER $$

CREATE TRIGGER bi_guestbook_each BEFORE INSERT ON guestbook FOR EACH ROW
BEGIN
  DECLARE posts_today INTEGER;

  SELECT COUNT(new.ip) INTO posts_today FROM guestbook 
    WHERE ip = new.ip AND postdate = new.postdate;
  IF posts_today > 1 THEN BEGIN
    //Force error by selecting from non-existing table
    //this will prevent the insert from happening.
    SELECT no_more_than_2_post_per_day_allowed 
     FROM before_insert_guestbook_error;
  END; END IF;

END$$

DELIMITER ;

Now you can just do inserts in you php code with worrying about the daily limit.
The trigger will take do the checking for you automatically.

Using insert .. select

If you don't want to use triggers, use an insert ... select statement.

First make sure that field ip is defined in the table definition as NOT NULL with no default.
This will make the following insert fail when trying to insert a null value into the guestbook table.

<?php
  $name = mysql_real_escape_string($_GET['name']);
  $post = .....
  $ip = ....
$query = "INSERT INTO guestbook (id, name, post_text, ip, postdate) SELECT
            NULL AS id
            , '$name' AS name
            , '$post' AS post_text
            , IF(guestbook.count(*)>=2,null,ip) AS ip
            , CURDATE() AS postdate
          FROM guestbook WHERE ip = '$ip' AND postdate = CURDATE()";

Of course you can put the IF(count(*) >= 2,null,...) in the place of any column that is defined as NOT NULL in the table definition.

Links:
select...insert: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
triggers: http://dev.mysql.com/doc/refman/5.1/en/triggers.html

---

You could implement this simply with cookies. Anyone determined enough to get round the posting limit would be able to get round an IP block anyway, since proxies and dynamic IPs make it really easy. So blocking by cookie or by IP are about as useful as each other (not especially), but cookies are easier to implement:

If you store a counter in the cookie and set the expiry date to be 24 hours in the future. You can then increment the counter when the user posts an entry, and simply check the counter to see if they've reached their limit.

That way you don't have to worry about mucking about with a database and any performance issues it can introduce.

---

Building upon Neville K's captcha point. If spam is your main concern, try out a negative captcha - it doesn't impact the user's experience since they never see it.

---

You've already got plenty of reasonable looking answers, so I won't add anything to that - but I do think you need to be careful using IP address as the unique identifier of the visitor. It's not a reliable way of identifying users - for instance, most people within a single company/university/government installation will appear to come from the same IP address, so this implementation would limit everyone within that structure to 2 posts per day.

The best way of avoiding spam is to include CAPTCHA - it's a pain in the backside for users, but it reliably foils robots; unless your site is super high value, spammers won't be motivated enough to have human beings post spam.