datetime stuff

[almightyegg]

is it possible to do something like:
mysql count row blah where last_login has been within the past 10 minutes...

how would i do time thing ??? sorry if ive confused you all  :-X

[ToonMariner]

mysql count row blah where last_login > NOW() - INTERVAL 1O MINUTES

Or something like!

[almightyegg]

hmm...
[code=php:0]
$sql2 = mysql_query("SELECT * FROM users WHERE last_login > $time INTERVAL 10 MINUTES");
$mem2 = mysql_fetch_array($sql2);
$numr = mysql_num_rows($sql2);
[/code]
are you sure thats a real code :P

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/lordofth/public_html/index.php on line 16

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/lordofth/public_html/index.php on line 17

[leeming]

[quote author=almightyegg link=topic=114205.msg464618#msg464618 date=1162944744]
hmm...
[code=php:0]
$sql2 = mysql_query("SELECT * FROM users WHERE last_login > $time INTERVAL 10 MINUTES");
$mem2 = mysql_fetch_array($sql2);
$numr = mysql_num_rows($sql2);
[/code]
are you sure thats a real code :P

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/lordofth/public_html/index.php on line 16

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/lordofth/public_html/index.php on line 17

[/quote]

you put...
[code]
$mem2 = mysql_fetch_array($sql2);
$numr = mysql_num_rows($sql2);
[/code]

its
[code]
$mem2 = mysql_fetch_array($sql2);
$numr = mysql_num_rows($mem2);
[/code]
your using the query.. not the sql

[almightyegg]

same errors  ???

[ToonMariner]

You missed out teh '-' sign too! ;)

[almightyegg]

still same errors.....:(
[code=php:0]
$time = gmdate("Y-m-d H:i:s");

$sql2 = mysql_query("SELECT * FROM users WHERE last_login > $time - INTERVAL 10 MINUTES");
$mem2 = mysql_fetch_array($sql2);
$numr = mysql_num_rows($mem2);
[/code]

[stewart715]

what unit of time is in your database? it's minutes right?

[almightyegg]

no its the whole Y-m-d H:i:s....aka 2006-11-07 00:22:47 or whatever ...

[ToonMariner]

so why use $tme when NOW() is more appropriate?

try putting ( ) around teh calculaion

[almightyegg]

[code=php:0]
$time = gmdate("Y-m-d H:i:s"); //this is my time
[/code]
NOW() wont work as it isnt GM time...

[stewart715]

hold on i completely misread lol

[almightyegg]

no...its to see how many members are online (online meaning having clicked on site within the last 10 minutes)

and toonmariner...there is no such thing as INTERVAL.... >:(

[stewart715]

[code]
<?php
$time = mktime(0, 0, 0, 0, 0, 0, date("m"), date("d"),  date("Y"), date("H"), date("i")-10, date("s"));

$sql2 = mysql_query("SELECT * FROM users WHERE last_login < $time");
$mem2 = mysql_fetch_array($sql2);
$numr = mysql_num_rows($mem2);
?>[/code]

maybe?

[leeming]

[code]$sql2 = "SELECT * FROM users WHERE last_login > ".gmdate("Y-m-d H:i:s")." - INTERVAL 10 MINUTES";[/code]

how about that?

i remember looking up the same thing ages ago myself (i use php's time() now.. easy to manipulte)
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
more info on date and interval

[stewart715]

[quote author=stewart715 link=topic=114205.msg464642#msg464642 date=1162946835]
[code]
<?php
//date("i")-10 means 10 minutes ago
$time = mktime(0, 0, 0, 0, 0, 0, date("m"), date("d"),  date("Y"), date("H"), date("i")-10, date("s"));

$sql2 = mysql_query("SELECT * FROM users WHERE last_login < $time");
$mem2 = mysql_fetch_array($sql2);
$numr = mysql_num_rows($mem2);
?>[/code]

maybe?
[/quote]

[almightyegg]

nah, that got multipul errors... i am thinking somethingmlike:
[code=php:0]
$time = gmdate("Y-m-d H:i:s",strtotime(-0010));


$sql2 = mysql_query("SELECT * FROM users WHERE last_login > $time");
$mem2 = mysql_fetch_array($sql2);
$numr = mysql_num_rows($mem2);
[/code]
the -0010 was me trying to do -10 minutes :( but it takes off 10 hours instead

[Orio]

You can do instead of-
$time = gmdate("Y-m-d H:i:s",strtotime(-0010));

This-
$time = gmdate("Y-m-d H:i:s",time()-60*10);

Orio.

[almightyegg]

i echoed $time out and got: 1969-12-31 23:59:59 apart from that it is 6.30pm gm time it is also not 1969 :P that made me giggle  :)

is there a function like 'i'-10 ????

[Orio]

Running this-
[code]<?php
echo gmdate("Y-m-d H:i:s",time()-60*10);
?>[/code]

Outputed on my server - 2006-11-08 18:23:18

Orio.

[almightyegg]

i missed something out  :-\ oops :P that sorts that problem....

but i get some errors counting rows...
[quote]
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/lordofth/public_html/index.php on line 17

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/lordofth/public_html/index.php on line 18

[/quote]

[code=php:0]
$time = gmdate("Y-m-d H:i:s",time()-60*10);


$sql2 = mysql_query("SELECT * FROM users WHERE last_login > $time");
$mem2 = mysql_fetch_array($sql2); //line17
$numr = mysql_num_rows($mem2); //line18
[/code]

[Orio]

I suppose you connected to the DB, right?

First, try chaning the line in the following way:
$sql2 = mysql_query("SELECT * FROM users WHERE last_login > '$time'");

If that doesnt work, let's see if there's any error:
$sql2 = mysql_query("SELECT * FROM users WHERE last_login > '$time'") or die(mysql_error());

Orio.

[almightyegg]

half worked...
i only get one error now:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/lordofth/public_html/index.php on line 18
???

[Orio]

This-
$numr = mysql_num_rows($mem2);

Should be-
$numr = mysql_num_rows($sql2);

Orio.

[almightyegg]

YAY!!! thanks :)