| Order by timestamp WITH simple math calculation
|
neophyte
msg:4475616 | 12:04 pm on Jul 14, 2012 (gmt 0) |
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
msg:4475629 | 2:53 pm on Jul 14, 2012 (gmt 0) |
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
msg:4475730 | 1:00 am on Jul 15, 2012 (gmt 0) |
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
msg:4475742 | 1:26 am on Jul 15, 2012 (gmt 0) |
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
msg:4475747 | 1:51 am on Jul 15, 2012 (gmt 0) |
Dijkgraaf -
Fantastic... have used option #2 and it works like a charm.
Thanks very much for the guidance!
|
|
|
