Page 1 of 1

Timstamp PHP vs SQL

Posted: Fri Dec 10, 2004 5:14 am
by asterinex
I have a table : online users .

It contains the username & timestanp & world (world = where he was last ly located on the website, his location)

I have a HTML frame with a auto refresh every 30 sec.

In that refresh I want to delete all users that are not updated for 40 seconds in the table . How do I campare the Timstamp from MySQL with time. So I call the function beneath every 40 sec.


I thought of something Like this,
Note : Lastupdate is a Timestamp field !

Code: Select all

function PHP_Delete_Old_User_Online ($world)
{
		include ("conn.php");
		mysql_select_db($db1,$conn);
		$sql  = "DELETE FROM online_users WHERE now()-lastupdate > 40 AND world= $world;";
		$result = mysql_query($sql,$conn);
}

Posted: Fri Dec 10, 2004 10:10 am
by timvw
did you even bother to search... anywhere?

Posted: Fri Dec 10, 2004 2:05 pm
by asterinex
Yes, ofcourse I did!

Posted: Fri Dec 10, 2004 2:58 pm
by timvw
happy to see you found the first example in the mysql date and time functions manual then....

Posted: Sat Dec 11, 2004 3:52 am
by asterinex
Listen Smartass,

A forum is there for people to discuss about a certain subject.
Without questions, there would not be a forum.
Ofcourse you have to look to previous topics first.
But I aked this question after I did research and tried a lot of code. Instead of going trough forums to talk rubbish.,

But I´m here for my problem.

I have a Timestampfield. How do I set the uptions in Sql . How do I fill them rightly with PHP , and how can delete the older records.
I tried a lot of code.!
Wenn I write time() in my SQL Database it writes somethinh like 20041012201230

wenn I is time() in php and echo it 30 sec. later , it´s something like 1102760003 !?


Thanks in Advance!

Posted: Sat Dec 11, 2004 4:42 am
by kettle_drum
That is because they are not the same formats. time() in php is the number of seconds since 1970 whereas in mysql it returns the year.day.month etc 2004 10 12.

You need to choose which format you want to use first. Then make the query according to that.

Using the php time():

Code: Select all

$timeout = time()-40;

$query = "DELETE FROM table WHERE time < '$time'"; 
//where you have used php function time() to insert the time into the database to begin with or with the MySQL function UNIX_TIMESTAMP()
For the MySQL time stuff: http://dev.mysql.com/doc/mysql/en/DATETIME.html

Posted: Sat Dec 11, 2004 5:03 am
by asterinex
Thanks,

In your exemple, you use PHP time. Do you use in MySql a timestamp field or a Varchar field for it.
How long are your fields ?

Posted: Sat Dec 11, 2004 5:07 am
by kettle_drum
I tend to use int(10).

Posted: Sat Dec 11, 2004 5:19 am
by asterinex
Thanks, alot , using a Int(10) field was easy. I was to fixated on the timestampfield. Thanks alot!

Posted: Sat Dec 11, 2004 6:45 am
by timvw
now if you lazyass was actually looking in the manual, you would have found:

http://dev.mysql.com/doc/mysql/en/Date_ ... tions.html


mysql> SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

FROM_UNIXTIME(unix_timestamp)
UNIX_TIMESTAMP(date)


we are not the homework club