mysql / php time issue

[bastienk]

Hi All

I am using a mysql datetime field which I fill with :

$last_accessed = date("Y-m-d H:m:s");

thru a sql statement. When the users are trying to view results using an order by on this field, the results are sometimes skewed where the most recent (and therefore first) record is not at the top of the list. I have checkd the data and there are no times that are exactly the same...

What is the problem? Here is the sql

$sql="select a.ad_id as aid, a.ad_title as title, a.ad_text as text,
      u.user_id as uid, u.user_name as user, u.user_email as email
      from ads a, users u
      where ad_deleted = 0 and ad_duration >= now()
      and a.ad_user_id = u.user_id order by [COLOR=red]ad_last_access_timestamp desc[/color]
      limit $NextBlock, $Display_len";

This is not a consistent error and I am unsure as to what the issue is

TIA

 

[awingnut]

This looks like a mysql issue rather then PHP. I suggest you ask this question a mysql forum.

 

[DaZZleD]

you should always use the mysql function UNIX_TIMESTAMP when working with dates... it's a life saver.
modify to this

...where ad_deleted = 0 and UNIX_TIMESTAMP(ad_duration) >= UNIX_TIMESTAMP(now())
      and a.ad_user_id = u.user_id order by UNIX_TIMESTAMP(ad_last_access_timestamp) desc

 

[bastienk]

will give it a try