a dynamic date

littleelmo

I'm trying to figure out how i can do this, here's a very general explaination:

I've got a mysql table that stores articles and the dates they were written.

Each week new articles will be added. What I want to do is be able to query the database and get the most recent articles for the week and display them.

What I want to know is what would the mysql query look like to get these most recent articles ? (but with dates that aren't in the future, because articles will be uploaded before they are ready to go live. I want them to only go live once the article date is at least the same date as the current date).

Is there a way I can do this?
Thanks in advance.

Anon

take a look at http://xhawk.net/yaapi/

this is a script i wrote to do something similar to what you want. to add functionality to pre-post articles, just add something to the sql like
select ... where ... live_date < NOW()...

-paul
http://xhawk.net

brandonschnell

some example code:

CREATE TABLE articles_table (id INT AUTO_INCREMENT PRIMARY KEY, article_date DATETIME, author CHAR(255), article LONGTEXT);

SELECT article_date, author, article FROM articles_table WHERE article_date < NOW() ORDER BY article_date DESC LIMIT 10;

littleelmo

okay. I see. However I have one more question. Is it possible to perform arithmetic on the date? So for example if I want to see if the article date is within 7 days of the current date. How would I perform that? Would it be a part of the mysql query or would I first generate an article from the mysql database and then test the articles date using some php function?

thanks

brandonschnell

i'd use mysql to do this.

maybe something like this:

SELECT article_date, author, article FROM articles_table WHERE article_date > DATE_SUB(NOW(), INTERVAL 7 DAY) AND article_date < NOW() ORDER BY article_date DESC;

http://www.mysql.com/doc/D/a/Date_and_time_functions.html

Anon

look into the sql DATE_SUB and DATE_ADD functions

select * from table where live_date > date_sub(NOW(), interval 1 week) and live_date < NOW();

that should be close to what you want

littleelmo

Thank you both Paul and Brandon for your help. You're help will answer my question.