Jump to content

Archived

This topic is now archived and is closed to further replies.

black.horizons

show mysql data under months?

Recommended Posts

Hi,

I'm writing a news cms package, and i'm wondering if theres a way I can get the articles to appear under the month they were written.

I don't want to hard code the months though, as this system will hold "archives" of the news items for maybe 2 or 3 years.

Basically like this:

2006
October
  Article 10
  Article 9
September
  Article 8
August
  Article 7
  Article 6

2005
October
  Article 5
  Article 4
September
  Article 3
August
  Article 2
  Article 1

Every month will have at least one article, possibly getting up to as many as 15 articles a month, but I want to display the articles in a list like that [i can link to their own "page" - really a call to another mysql script to pull and format the news accordingly].

This is the only bit im struggling with, and so far i've got the date stored as a date field in the MySQL database, but changing it to a timestamp field will only involve changing around 5 lines of code at most.

TIA, Alex

Share this post


Link to post
Share on other sites
Get the [b]month[/b], [b]year[/b] using DATE_FORMAT, ORDER BY the DATE_FORMAT [b]year[/b] DESC and then MONTH(date_column) DESC! Then in your result loop, create (2) test variables to hold (current_month, current_year), then when looping only change those (2) variables when they don't match the current result variables!

A simple example... (I don't know what you table has, but you should be able to follow this!

[code]$result = mysql_query ( "SELECT DATE_FORMAT(date_column, '%M') AS month, DATE_FORMAT(date_column, '%Y') AS year, other_fileds FROM my_table WHERE date_column >= '2005-01-01' AND date_column <= '2006-10-15' ORDER BY date_column DESC" );

if ( mysql_num_rows ( $result ) > 0 )
{
$c_month = '';
$c_year  = '';

echo "<pre>\r\n";

while ( $row = mysql_ftech_assoc ( $result ) )
{

if ( $row['year'] != $c_year )
{
echo "<h3>" . $row['year'] . "</h3>\r\n";

$c_year = $row['year'];
}


if ( $row['month'] != $c_month )
{
echo "\t<h4>" . $row['month'] . "</h4>\r\n";

$c_month = $row['month'];
}

echo "\t\t" . $row['title'] . "\r\n";
}
}[/code]


// legend

where it says [b]my_table[/b]

[b]my_table[/b] = you would change that to your database table name

where it says [b]date_column[/b]

[b]date_column[/b] = change that to your date column name

where it says [b], other_fileds[/b]

[b], other_fileds[/b] = you would add the other fields you want the result array to return

where it says [b]$row['title'][/b]

[b]$row['title'][/b] = you would change that to the name of your column that holds the article time -> $row['article_title_column_name']



//EDIT

I changed the ORDER BY, just ORDER BY date_column, is really all you need!


me!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.