0
DELETE FROM     banned_users 
WHERE datetime  < (NOW() - INTERVAL '$ban' MINUTE) 
AND ban_length  != 'INF';

INF is of course a perm ban, but intervals are as follows

60 - 1 hour
and
1440 - 24 hours

However it seems as though this query (which is run from PHP at the top of page load) is deleting all items that aren't 'INF' with a minute, not immediately but from what I can tell within a minute.

Why on earth is this happening? No wonder I was having problems with repeat offenders!

$ban = $row['ban_length'];

db:

CREATE TABLE IF NOT EXISTS `banned_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip` varchar(200) NOT NULL,
  `username` varchar(22) NOT NULL,
  `ban_length` varchar(200) NOT NULL,
  `reason` text NOT NULL,
  `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip` (`ip`)
)

echo the query

DELETE FROM banned_users 
WHERE datetime < (NOW() - INTERVAL 60 MINUTE) 
AND ban_length != 'INF';

Also, here is the PHP code

$result = mysql_query("SELECT * FROM banned_users WHERE  ip = '$ip'")or die(mysql_error());
$is_banned = FALSE;
while($row = mysql_fetch_assoc($result)) {
        $ban = $row['ban_length'];
        $is_banned = TRUE;
        $reason = $row['reason'];    

        mysql_query("
            DELETE FROM banned_users
            WHERE datetime < (NOW() - INTERVAL $ban MINUTE)
            AND ban_length != 'INF';")or die(mysql_error());
    }

    if($is_banned) {
        echo "banned";
    }
Broak
  • 4,161
  • 4
  • 31
  • 54
  • What is $ban? Please show the whole thing. Also db field types – Jompper Dec 23 '13 at 23:53
  • the query is ok the problem should be $ban – Emilio Gort Dec 23 '13 at 23:54
  • $ban is the interval, as above '60' and '1440' – Broak Dec 23 '13 at 23:55
  • if you make `echo $query` what you get? and runinng it from mysql, also you need `datetime` field be close between `backtick` – Emilio Gort Dec 23 '13 at 23:56
  • added to post the echo ^ – Broak Dec 23 '13 at 23:59
  • are you enclosing `datetime` between `backtick` – Emilio Gort Dec 23 '13 at 23:59
  • in the query? no? i've never heard of such a thing, datetime is just a column in the db – Broak Dec 24 '13 at 00:00
  • 1
    see [backticks](http://stackoverflow.com/questions/18157243/grave-accents-instead-of-in-mysqli-query/18157263#18157263) – Emilio Gort Dec 24 '13 at 00:03
  • datetime is not a reserved keyword? – Broak Dec 24 '13 at 00:10
  • datetime isn't a reserved word but is a data type, if you run the query as it form mysql, what happen?? – Emilio Gort Dec 24 '13 at 00:13
  • Works as expected, oddly, nothing is deleted that shouldnt be – Broak Dec 24 '13 at 00:29
  • it's better to name the column `datetime` something more descriptive anyways - try `bannedOn`or `bannedAt`. Backticks are MySQL's way to escape strings used for something else - the problem with using it for things like table and column names in most systems is that you have to get things like casing identical (well, if they were created that way); it's best to just avoid all reserved words/datatypes as identifiers for that reason. You're potentially open to SQL Injection. Oh, and this should be a scheduled job, _not_ something that runs on page-load - it's running too often. – Clockwork-Muse Dec 24 '13 at 02:07

0 Answers0