[SOLVED] How many users online in the last 24-hours using time()?

[Kryptix]

I'm fairly new to PHP. I have a MySQL table called 'users' and a field called 'loggedin' which is in time() format.

 

I need a query that'll check how many players have logged in within the last 24-hours, but I'm not sure where to start. I already have the database connection setup, and just need the amount to be set to "$online24h"

 

Can anyone help me please?

[Mchl]

SELECT
COUNT(*) 
FROM players 
WHERE loggedin BETWEEN NOW() - '24:00:00' AND NOW()

[Kryptix]

$result = $db->query('SELECT COUNT(user) FROM '.$db->prefix.'rscd_players WHERE `loggedin` BETWEEN NOW() - '24:00:00' AND NOW()) or error('Unable to fetch total player count', __FILE__, __LINE__, $db->error());
$stats['online24'] = $db->result($result);

 

Parse error: syntax error, unexpected T_LNUMBER in C:\xampp\htdocs\index.php on line 127

 

Any idea?

 

Also, could I have the query for a week and a month too please?

[Mchl]

You use single quotes around the strin, so you can't use them IN the string.

$db->query('SELECT COUNT(user) FROM '.$db->prefix.'rscd_players WHERE `loggedin` BETWEEN NOW() - "24:00:00" AND NOW())

 

for a week it would look like this

 

SELECT
COUNT(*)
FROM players
WHERE loggedin BETWEEN DATE_ADD(NOW(), INTERVAL 7 DAY) AND NOW()

 

you should be able to figure the query for a month by yourself

[Kryptix]

Parse error: syntax error, unexpected T_STRING in C:\xampp\htdocs\index.php on line 127

 

$result = $db->query('SELECT user, username FROM '.$db->prefix.'rscd_players ORDER BY creation_date DESC LIMIT 1') or error('Unable to fetch newest registered player', __FILE__, __LINE__, $db->error());
$stats['last_player'] = $db->fetch_assoc($result);

[Mchl]

Seems forum is losing quotes now and then

$db->query('SELECT COUNT(user) FROM '.$db->prefix.'rscd_players WHERE `loggedin` BETWEEN NOW() - "24:00:00" AND NOW()');

[Kryptix]

Thanks. No errors now. However, it's showing ALL 5,000+ users as being online in the last 24-hours. Some have their 'login_date' set as '0' so something is wrong.

 

$db->query('SELECT COUNT(user) FROM '.$db->prefix.'rscd_players WHERE login_date BETWEEN NOW() - "24:00:00" AND NOW()');
$stats['online24'] = $db->result($result);

[Mchl]

I'm fairly new to PHP. I have a MySQL table called 'users' and a field called 'loggedin' which is in time() format.

 

 

Erm... TIME is not really good datatype for such column. You should use DATETIME.

 

http://dev.mysql.com/doc/refman/5.0/en/time.html

[Kryptix]

'login_date' is a int(10) using PHP's time() function, I believe.

 

An example is: '1256418479'

 

Any suggestions? I really need this to work. :'(

[Mchl]

'login_date' is a int(10) using PHP's time() function, I believe.

 

An example is: '1256418479'

 

Any suggestions? I really need this to work. :'(

 

So it's in fact unix timestamp... Ok then:

 

SELECT
COUNT(*)
FROM players
WHERE FROM_UNIXTIME(loggedin) BETWEEN DATE_ADD(NOW(), INTERVAL 1 DAY) AND NOW()

 

[Kryptix]

It's still displaying all of them. :-\

 

$db->query('SELECT COUNT(*) FROM_UNIXTIME(login_date) BETWEEN DATE_ADD(NOW(), INTERVAL 1 DAY) AND NOW()');
$stats['online24'] = $db->result($result);

 

Check the bottom right hand side: RuneScape Classic

[Mchl]

If anything, it should display none... it should be INTERVAL -1 DAY

[Kryptix]

I changed that. It's still displaying them all.

 

It's entered into 'login_date' which is a int(10). It updates 'login_date' with time() when they manage to login.

 

The exact code I have is:

 

$db->query('SELECT COUNT(*) FROM_UNIXTIME(login_date) BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW()');
$stats['online24'] = $db->result($result);

 

<dd><?php echo 'Game Characters Online Today: <strong>'. number_format($stats['online24']) ?></strong></dd>

 

[Mchl]

Doh!

We've lost WHERE somewhere along the line Thias query should not work at all, so make sure you're actually uploading your changes to the server

 

This should do better

$db->query('SELECT COUNT(*) FROM '.$db->prefix.'rscd_players WHERE FROM_UNIXTIME(login_date) BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW()');

[Kryptix]

Oops! I forgot to declare $result. :'(

 

Here's the code now:

 

$result = $db->query('SELECT COUNT(*) FROM_UNIXTIME(login_date) BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW()');
$stats['online24'] = $db->result($result);

 

This returns '0' (after being number_format()'d).

[Kryptix]

Code is now:

 

$result2 = $db->query('SELECT COUNT(*) WHERE FROM_UNIXTIME(login_date) BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW()');

$online24 = $db->result($result2);

 

Still returns '0'

[Mchl]

You still don't have table name in this query. See my previous post.

[Kryptix]

Woo, thank-you so much! So is it just -30 DAY for a month?

[Mchl]

Or -1 MONTH (this will take into account actual number of days in current and previous month)