check if a user registered within the last hour

Question

I have this piece of code to check if a user has already created an account the last hour:

$result = mysql_query("SELECT * FROM accounts WHERE registration_ip = '$_SERVER[REMOTE_ADDR]' AND created > ".(time() - 3600));

if (mysql_num_rows($result) > 0)
    exit('Blablal')

It does not exit as I want it do, i can make how many accounts i want.

You see any obivious problem? My db tablesandfields are correct

Answer 1

Test it by removing the registration_ip = '$_SERVER[REMOTE_ADDR]' clause.

Answer 2

Also your sql query AND created > ".(time() - 3600)); is not correct to get the last hour. You want something like this:

SELECT ... WHERE ... 
AND created > DATE_SUB(now(), INTERVAL 1 HOUR)

Answer 3

I tested this locally and for me, it behaves as expected. I would say that the problem is not in your query, but somewhere else. Check your data and make sure it is being inserted as expected.

Also, you might want to use SELECT COUNT to count the columns instead of SELECT * - it should be faster.

  $result = mysql_query("SELECT COUNT(*) FROM accounts WHERE registration_ip = '$_SERVER[REMOTE_ADDR]' AND created > ".(time() - 3600));

  $count = mysql_fetch_array($result);
  if ($count[0] > 0) exit('BLAH');

Answer 4

Yada's answer is correct. The only other comment I would make is that you escape $_SERVER[REMOTE_ADDR] using mysql_real_escape to protect against a SQL inject in the off chance that the variable gets overridden

Answer 5

Your problem is probably this, $_SERVER[REMOTE_ADDR]

You are embedding that directly into a double-quoted string. When you want to access an array in a double-quoted string you need to put the variable array access inside a pair of curly braces.

Corrected string:

"SELECT * FROM accounts WHERE registration_ip = '{$_SERVER['REMOTE_ADDR']}' AND created > ".(time() - 3600)

Also, alway quote indexes, you used REMOTE_ADDR instead of 'REMOTE_ADDR' or "REMOTE_ADDR"

Hope this helps.

Answer 6

You need to do use { } around the things that you are embedding into the SQL that are PHP. I think this should work. I always do this as I have found it saves me time debugging as it seems to pop up somewhat frequently.

$result = mysql_query("SELECT COUNT(*) FROM accounts WHERE registration_ip = '{$_SERVER[REMOTE_ADDR]}' AND created > ".({time()} - 3600));

If this doesn't work, save your query into a variable, echo it to the page to see what it is sending.

$sql = "SELECT COUNT(*) FROM accounts WHERE registration_ip = '{$_SERVER[REMOTE_ADDR]}' AND created > ".({time()} - 3600)";

echo $sql;