Welcome to WebmasterWorld Guest from 54.160.131.144

Forum Moderators: open

Message Too Old, No Replies

Order by timestamp WITH simple math calculation

     

neophyte

12:04 pm on Jul 14, 2012 (gmt 0)

10+ Year Member



Hello All -

I'm having trouble with a particular select and hoping someone here can help.

I'm selecting a table of information and want to order it by a timestamp field (fld_approved).

That's easy.

The trick that I'm trying to preform however is that I want the results sorted by the timestamp value of fld_approved + 3 days AND are LESS THAN the current time.

As mentioned, fld_approved contains an int timestamp (like 1342178312) and the 259200 shown below is 60*60*24*3 seconds (the seconds for 3 days).

What I've got now - which doesn't work at all - is:

"SELECT *
FROM $tbl_foo
ORDER BY fld_approved
WHERE (fld_approved + 259200) < UNIX_TIMESTAMP
LIMIT 0, 10";

I keep getting an error on the WHERE clause and can't figure out where I'm going wrong... or if I'm going about this query in the wrong way altogether.

Any guidance greatly appreciated.

LifeinAsia

2:53 pm on Jul 14, 2012 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



You need to put the WHERE before the ORDER BY:
"SELECT *
FROM $tbl_foo
WHERE (fld_approved + 259200) < UNIX_TIMESTAMP
ORDER BY fld_approved
LIMIT 0, 10";

neophyte

1:00 am on Jul 15, 2012 (gmt 0)

10+ Year Member



LifeinAsia -

Thanks very much... that should have been obvious to me.

Now, however, the error I'm getting is Unknown column 'UNIX_TIMESTAMP' in 'where clause'.

I would think that the WHERE clause is keying off fld_approved within the parens, but apparently not.

I've tried WHERE((fld_approved + 259200) < UNIX_TIMESTAMP) and other variations which would force the identification of fld_approved, but without any luck.

Any ideas?

Dijkgraaf

1:26 am on Jul 15, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



UNIX_TIMESTAMP is a PHP command, so you need to either

1) execute that on the PHP side
"SELECT *
FROM $tbl_foo
WHERE (fld_approved + 259200) < " + UNIX_TIMESTAMP + "
ORDER BY fld_approved
LIMIT 0, 10";
2) replace UNIX_TIMESTAMP with NOW()

"SELECT *
FROM $tbl_foo
WHERE (fld_approved + 259200) < NOW()
ORDER BY fld_approved
LIMIT 0, 10";

My preference is for option 2.

neophyte

1:51 am on Jul 15, 2012 (gmt 0)

10+ Year Member



Dijkgraaf -

Fantastic... have used option #2 and it works like a charm.

Thanks very much for the guidance!