[SOLVED] format date column into year month articles...

[Bendude14]

I have a column in my database which simply contains dates, what i would like to do is format them on my page like so.

 

Year

    Month

          Article

          Article

Year

    Month

 

I know i can use MONTHNAME( DateColumn ) to get the months when articles have been posted but other than that i don't know where to start?

 

Thanks

Ben

[fenway]

I don't understand... DATE_FORMAT() is your friend.

[Bendude14]

Ok i read the post in your signature so ill start with this...

 

mySql Version 5.1.33

 

I have a column of dates in my database which are the dates when articles were posted. What i would like to do is display each post under a heading for that month and the months under the corresponding year.

 

I have tried this but it brings back the year for every post when what i want to do is group by year and then by month

 

SELECT YEAR(Date) as year, MONTHNAME(Date) as month, COUNT(ID) as articles FROM news GROUP BY YEAR(Date), MONTHNAME(Date) ORDER BY Date ASC

 

Hope this makes sense

Ben

 

[gassaz]

i think you will have to code to get the result that you want.

 

if you want each article you don't need a count()

 

SELECT YEAR(Date) as year, MONTHNAME(Date) as month, ID as articles FROM news

 

 

[PFMaBiSmAd]

GROUP BY consolidates the rows with same group values into a single entity and returns a single row for each entity.

 

To do what you want, your query needs to select the rows you want in the order you want (I'm not sure how your articles are referenced and just showed articles in the query) -

SELECT YEAR(Date) as year, MONTHNAME(Date) as month, articles FROM news ORDER BY Date

 

It is up to your presentation code to produce the output you want. As you loop through the result set, you need to remember the year value and month value and do any special processing to output the new section headings when they change values.

 

pseudo code -

// execute your query, test it for errors, and test if there are any rows in the results set

$year_value = ''; // initialize variable to remember the year value, set it to a value that will never exist as data
$month_value = ''; // same for the month value

// your existing while() loop to iterate through the rows in the result set -
while($row = ....){
    // test for a change in the year value
    if($year_value != $row['year']){
        // the value changed, output a new year heading
        echo $row['year'] . '<br />';

       $year_value = $row['year']; // remember the new value
    }

    // test for a change in the month value
    if($month_value != $row['month']){
        // the value changed, output a new month heading
        echo $row['month'] . '<br />';

       $month_value = $row['month']; // remember the new value
    }

    // do the normal processing that occurs on every row -
    echo $row['article'] . '<br />';
}

[Bendude14]

just what i needed, worked perfectly.

 

Thanks for the help.

Ben