Newbie: Time function

Anon

Hi all,

This code is supposed to remove any items from a basket that are over 10 seconds old, then add a new item to a basket:

$currenttime = time();
$deletetime = time() + 10;
$query = "DELETE FROM $table_basket WHERE TIME > $deletetime";
$result=mysql_query($query);
if (! $result) printf ("Error: %s<br>", mysql_error ());
$query = "INSERT INTO $table_basket SET PROD_ID = $key, QUANTITY = $value, CUST_ID = \"$customer\", TIME = $currenttime";
$result=mysql_query($query);
if (! $result) printf ("Error: %s<br>", mysql_error ());

The problem is that the TIME field in the table seems to be set as 838:59:59 whenever the item is added to a basket. If I output the $currenttime and $deletetime using echo, it prints 988133891, 988133901 for example, but these figure do change.

Can anyone tell me what I am doing wrong?

Much appreciated,

David.

Anon

Mysql stores timestamps differently than unix. You'll need to convert your unix time stamp.

Look at http://www.php.net/manual/en/function.time.php

echion

Hi,

You are probably using a type for your TIME field which only saves the time. If you want to compare against a unix timestamp, you can either store this as a large integer or timestamp if your database has support for that kind of type.

Good luck

Anon

Excuse my ignorance but I still don't understand.

Am I to convert my TIME field to unix format before it is input in the mysql table, or do I convert it when it is retrieved from the table?

Furthermore, when I set the TIME field to type TIMESTAMP, and it now only returns 0's which I believe happens when an illegal entry is made. I think this is because it does not accept 9-figure values?

Thanks again!

David.

Anon

Unix stores it's timestamp as the number of seconds since the "Unix Epoch" which is Jan 1, 1970 at 00:00:00 GMT.

MySQL stores it's timestamps as a string of values that it translates into a date and time:
TIMESTAMP(14) YYYYMMDDHHMMSS

TIMESTAMP(12) YYMMDDHHMMSS

TIMESTAMP(10) YYMMDDHHMM

TIMESTAMP(8) YYYYMMDD

TIMESTAMP(6) YYMMDD

TIMESTAMP(4) YYMM

TIMESTAMP(2) YY

So to make a comparison between the two you need to convert one into the other. You can do this many ways:

MySQL has a function called UNIX_TIMESTAMP() which you can use in your sql statement: $query = "DELETE FROM $table_basket WHERE UNIX_TIMESTAMP(TIME) > $deletetime"

Or you could convert your unix timestamp into a MySQL timestamp using the date() function in PHP: $deletetime = date('Y-m-d HⓂs', timestamp())

There are other ways to do this, but these are probably the two best suited to your purpose. I'd suggest that you uset the second one since it will allow you to convert $currenttime also. Second I'd double check your logic I think you want to substract 10 seconds from $deletetime not add 10 seconds, and you probably want to check where TIME < $deletetime (less than not > greater than).