Page 1 of 1
query by date
Posted: Wed May 21, 2003 2:54 am
by jamrop
I wonder if it is possible to have a query that uses the date
e.g
table has a tv series list. lets say therre is a tv_id(primary key), tv_name, tv_episode, date
in php u query to show the whole list - select * from tvseries.
and it is displyed in a table.
what i want to know, if u update the epsiode so u had eps 1-10, but now u have 1-15, is it possible to highlight tv series that have been updated within 2 weeks?
Many thanks
Posted: Wed May 21, 2003 3:14 am
by Gleeb
Sure, it's possible.
Code: Select all
SELECT * FROM table WHERE date = now()-60*60*24*14
60 secondind in 60 minutes in 24 hours in 14 days. 1209600 seconds in total. that should work
Just as a quick note, try the databases forum for SQL stuff, it'll help you get a reply faster.
Posted: Wed May 21, 2003 4:23 am
by Friday
Code: Select all
select * from TABLE_NAME where date=date_add(now(),interval -14 day)
Posted: Wed May 21, 2003 5:01 am
by jamrop
thanks for reply, but if u have already got e.g select * from tvseries, which displays all the listing, then u need another query (date query) to put in, so it can highlight the updates
how would u get around that?
Posted: Wed May 21, 2003 5:05 am
by Gleeb
A quick correction to my SQL. It has the wrong operand. A cookie to the one who finds it
You don't need a second Query to find that out, PHP is quite capable of handling dates

Posted: Wed May 21, 2003 2:35 pm
by jamrop
hey
i have tried both those codes in my php, but it does not seem to find anything. IS the sql code just looking for anything that was updated 2 weeks ago, Or within 2 weeks?
Many thanks
Posted: Wed May 21, 2003 2:43 pm
by nincha
Posted: Wed May 21, 2003 3:58 pm
by jamrop
?????????????????????
Please its doing my head in
solution..
Posted: Thu May 22, 2003 6:58 am
by coolpravin
use this one
Code: Select all
SELECT * FROM tvseries WHERE TO_DAYS( NOW( ) ) - TO_DAYS(date ) <= 7
Here date is supposed to be name of the colum .
Warning :-
1)Do not use date as column name instead you can use ondate.
2)Always store date in Y-m-d format to database.
Re: solution..
Posted: Thu May 22, 2003 8:35 am
by ckuipers
coolpravin wrote:use this one
Code: Select all
SELECT * FROM tvseries WHERE TO_DAYS( NOW( ) ) - TO_DAYS(date ) <= 7
Here date is supposed to be name of the colum .
Warning :-
1)Do not use date as column name instead you can use ondate.
2)Always store date in Y-m-d format to database.
I always use the unix timestamp as a date in the database. Takes some formatting but it's very easy to work with when you have to calculate with dates.