Active User Tracking, PHP Sessions

Question

Alright, I'm trying to work on a function for active user counting in an AJAX based application. I want to express the below SQL Query in correct syntax but I'm not sure how to write it. The desired SQL Query is below:

SELECT count(*)
FROM active WHERE timestamp > time() - 1800
     AND nick=(a string that doesn't contain [AFK])

Now, I do understand that time() - 1800 can be assigned to a variable, but how is timestamp > variable and nick that doesn't contain a string written in SQL?

Answer 1

Do you mean time() to refer to the current time? And can we assume timestamp to be of type datetime? Then this should work:

SELECT
    count(*)
FROM
    active
WHERE
    timestamp > NOW() - 1800

Answer 2

SELECT count(*)
FROM active WHERE timestamp > unix_timestamp() - 1800
     AND nick NOT LIKE '%[AFK]%'

Answer 3

About the username, you should filter each variable before putting it into a sql query(SQL Injection).

$nick =  mysql_escape_string($nick);

and then in the sql query:

$sql = "SELECT count(*) FROM `active` WHERE `timestamp` > (time() - 1800) AND `nick` = '{$nick}' AND `nick` NOT LIKE '%[AFK]%' LIMIT 1";

Answer 4

SELECT COUNT(*) FROM active WHERE timestamp > DATE_ADD(NOW(), INTERVAL -1800 SECOND) AND nick NOT LIKE '%[AFK]%'