Sort by date

dashwood75

Can anyone advise me the best way to input articles with a date/timestamp into a database, so that I can then query the database and display articles by date...

I am tearing my hair out over this as I think it is a simple thing to do..just not for me 😕

help is appreciated😃

Roger_Ramjet

RTFM . Simple, basic, fundamental db design issue. Choose the right column type for the data you want to store and use. Some db will allow you to set the default value for a column to a date function like curdate() or now(), others require that you generate this in code and insert into the db. Try reading up on the date functions as well.

dashwood75

thanks for your reply, I am a newbie in the world of php/mysql, so getting pointers helps..didnt even think to look at mysql manuals...

will give it a go and let you know if I get it working!:eek:

dashwood75

I finally got it working using this SELECT statement:

$query = "SELECT * FROM news WHERE date > SUBDATE( CURDATE( ) , INTERVAL 30 DAY )";

I basically have the field set as date type and use php to input the date in mysql date format 0000-00-00, then I use the select statement above to display only the articles from the last 30 days.

so my next step is to tweak this to display everything prior to the last 30 days as an archive page...any pointers?!

I am getting there slowly...

Roger_Ramjet

Point one. Change the coulmn name from 'date' to anything else. 'DATE' is a reserved word in mysql, and most other db engines. It should never be used for a column name. Why? Because one day you will write a query and the db will interpret date as the native date function not your column name.

Date is also meaningless. What Date?? Date input, date edited, birthdate, today's date? Make the name as meaningfull as possible. In this case it would be date_in or something similar so everyone who looks at it can see that it is the date the article was input.