CODEWITHSUNDEEP
sqlmysqlsql-like
Nik
Nik

Reputation: 2484

Active User Tracking, PHP Sessions

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?

Upvotes: 0

Views: 334

Answers (4)

Marsio
Marsio

Reputation: 66

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

Upvotes: 0

Vladimiroff
Vladimiroff

Reputation: 473

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";

Upvotes: 0

Artefacto
Artefacto

Reputation: 97805

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

Upvotes: 2

chryss
chryss

Reputation: 7519

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

Upvotes: 0

Related Questions