PHP and mysql timestamp

JamesM · 1 Nov 2007 at 13:40

I've got a MYSQL database of news which i want to arrange by date but im finding it difficult to code.

I have a VARCHAR in my database which has a timestamp in it eg. 071101130000 (YYMMDDHHMMSS) and I want to sort the news as follows..

1st Month
- 1st News Post
- 2nd News Post
- 3rd News Post

2nd Month
- 1st News Post
- 2nd News Post
- 3rd News Post

I cant get my head around selecting the months which have news posts in them (if I start posting news on the 25th October, i dont want September showing up as a month) and then selecting the seperate news posts for that month.

Any ideas?

marc2003 · 1 Nov 2007 at 14:05

JamesM said:
I have a VARCHAR in my database

see, that's where you're going wrong.

store it in proper mysql DATETIME format and then working with dates will be a little easier.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

JamesM · 1 Nov 2007 at 14:15

the only problem using datetime is that it updates itself when I dont want it to

Dj_Jestar · 1 Nov 2007 at 14:32

It'll only update itself if you tell it to, it will not change of it's own accord.