mysql timestamp feild comparison

[nadeemshafi9]

hello guys i have a timestamp feild generated by mysql in my db.

what happens is when somone click an item in the catalogue it gets recorded.

ok i need to compare the timestamps in the db to see what the user has looked at most recently so i can get items similar to that and advertise them.

how do find the most recent item if the timestamp on each item looks like,

2007-01-15 14:57:06

should i explode it in php

should i be looking at some SQL function whithin or built in db func ?

what i whant is to find the latest item i mean the latest date and time more importantly.

thanx for any help

[shoz]

You should be able to get the similar items with one query depending on the version of MYSQL you're using but to get the item most recently looked at you can try the following

[code]
SELECT item FROM tablename WHERE user_id = $user_id ORDER BY tstmpcolumn DESC LIMIT 1
[/code]

If you have other questions post the version of mysql you're using
[code]
SELECT VERSION()
[/code]

and the create statement for the tables involved.
[code]
SHOW CREATE TABLE tablename
[/code]

[owenjh]

You could explode the time stamp and then use the mktime function to turn it into a unix timestamp. All you would have to do to pick the newest item would be pick the highest time stamp.

[code]
$time = "2007-01-15 14:57:06";

$firstExplode = explode(" ", $time);
$date = explode("-", $firstExplode[0]);
$time = explode(":", $firstExplode[1]);

echo mktime($time[0], $time[1], $time[2], $date[1], $date[2], $date[0]);
[/code]

[nadeemshafi9]

wow i guess those university lesson of SQL payed off heres what i done its a sub query v simple i did hav a go at exploding and stuff and using now() - the timestamp and generating a number to compare but the fololwing worked fine

$sql = "SELECT * FROM item_fuzz WHERE time = (SELECT MAX(time) FROM item_fuzz);";

thanx very much for your hep guys

[nadeemshafi9]

i gues by DESC ing it using the timestamp would return the first on in the result as the highest or latest timestamp 2 so yes shoz you have a point, yours is even simpler.