How can I write a query to get a list of users who have registered in the last 24 hours?

Question

I'm trying to use PHP to generate a query that fetches the number of users who have registered in the last 24 hours.

Based on the code for the "who's online" block:

$authenticated_count = db_query("SELECT COUNT(DISTINCT s.uid) FROM {sessions} s WHERE s.timestamp >= :timestamp AND s.uid > 0", array(':timestamp' => $interval))->fetchField();

I modified it as follows (based on this old D6 Stackoverflow answer:

$users_registered = db_query("SELECT COUNT(DISTINCT u.uid) FROM {users} u WHERE u.created >= :timestamp AND u.uid > 0", array(':timestamp' => ($_SERVER['REQUEST_TIME'] - 86400)));

However, this results in an error:

Notice: Object of class DatabaseStatementBase could not be converted to int in format_plural() (line 1801 of /var/www/om/includes/common.inc).

I'm not great with SQL, so a brief explanation of where I'm going wrong would be much appreciated.

Answer 1

It seems to me that you aren't doing anything wrong; it's just that the object you will get back from db_query() in your case is not something you can use as is.

Invoke ->fetchField() method after db_query() so that Drupal's DB API returns you one single value.

Use this:

$users_registered = db_query("SELECT COUNT(DISTINCT u.uid) FROM {users} u WHERE u.created >= :timestamp AND u.uid > 0", array(':timestamp' => (REQUEST_TIME - 86400)));

Also, use REQUEST_TIME constant to get current timestamp.

Hope this helps.