Timstamp PHP vs SQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
asterinex
Forum Commoner
Posts: 52
Joined: Thu Nov 25, 2004 7:01 am
Location: Belgium

Timstamp PHP vs SQL

Post 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);
}
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

did you even bother to search... anywhere?
asterinex
Forum Commoner
Posts: 52
Joined: Thu Nov 25, 2004 7:01 am
Location: Belgium

Post by asterinex »

Yes, ofcourse I did!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

happy to see you found the first example in the mysql date and time functions manual then....
asterinex
Forum Commoner
Posts: 52
Joined: Thu Nov 25, 2004 7:01 am
Location: Belgium

Post 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!
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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
asterinex
Forum Commoner
Posts: 52
Joined: Thu Nov 25, 2004 7:01 am
Location: Belgium

Post 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 ?
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

I tend to use int(10).
asterinex
Forum Commoner
Posts: 52
Joined: Thu Nov 25, 2004 7:01 am
Location: Belgium

Post by asterinex »

Thanks, alot , using a Int(10) field was easy. I was to fixated on the timestampfield. Thanks alot!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
Post Reply