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
:lol: 8O :lol: 8O

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.