CODEWITHSUNDEEP
phpsqltimespam
Frank
Frank

Reputation: 1864

How can I detect if a row was inserted into the database x seconds ago

Basically, I'm working on a spam detection system and I would like to detect if a row was inserted less the x seconds ago. x could be 5 second, so I would want to see if a row was inserted into the table less then 5 seconds ago.

The table name for example purposes could be my_table. This is what I came up with...

$spam_validate = mysql_query("select * FROM my_table WHERE date_time < '3'");

Then I would do....

    if (mysql_num_rows($spam_validate)==0) {
    //keep going
    } else {
    echo 'Spam was detected';
    }

If any of you could point me into the right direction here, I'm obviously not correct so any help is much appreciated.

Upvotes: 0

Views: 1509

Answers (3)

Jonathon Reinhart
Jonathon Reinhart

Reputation: 137448

You need a timestamp field (can be of type DATETIME) in that table, which you set when you insert the row. 

INSERT INTO foo (spam, eggs, timestamp) VALUES (42, 88, NOW())

Then you can select against it. This query should work.

SELECT * FROM foo WHERE timestamp BETWEEN (NOW() - INTERVAL 5 SECOND) AND NOW()

Note the DATETIME arithmetic, which is similar to using DATEADD directly.

Upvotes: 3

DaveRandom
DaveRandom

Reputation: 88677

Your table should have a column (we'll call it inserted_at) of the type timestamp. You insert query would look like this:

INSERT INTO tbl (inserted_at, somecol, someothercol) VALUES (NOW(), 'whatever', 76)

...and your check code would look like

$interval = 5;
$sql = "SELECT count(*) AS denied FROM tbl WHERE inserted_at > NOW() - $interval";
$spam_validate = mysql_fetch_array(mysql_query($sql));

if ($spam_validate['denied']) {
  echo 'Spam was detected';
} else {
  // keep going
}

Upvotes: 0

Zack Zatkin-Gold
Zack Zatkin-Gold

Reputation: 824

Insert a timestamp into your database, then when you are taking in input from a user, grab the timestamp and check if their last post time plus X seconds (or throttle, perhaps would be a better word) is less than or equal to the current timestamp.

Here's a pseudo code example:

X = post_limit;
if((last_post_time + X) <= current_time)
{
    allow_to_post;
}
else
{
    do_not_allow_to_post;
}

Here's a snippet of code that I used in my imageboard software that might point you in the right direction as well:

<?php
$ip = $_REMOTE['SERVER_ADDR']; // client's IP address
$sql = "SELECT time FROM spam WHERE ip = '$ip' LIMIT 1";
$query = mysql_query($sql);
if(0 < mysql_num_rows($query))
{
    while($result = mysql_fetch_assoc($query))
    {
        if($result['time'] < $time)
        {
            $sql = "DELETE FROM spam WHERE ip = '$ip'";
            $return_val = mysql_query($sql);
        }
    }
}
else
{
    $sql = "INSERT INTO spam (ip,time) VALUES ('$ip','$time');";
    $return_val = mysql_query($sql);
}
?>

Upvotes: 1

Related Questions