Jump to content


show mysql data under months?

  • Please log in to reply
2 replies to this topic

#1 black.horizons

  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 19 October 2006 - 11:58 PM


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:

  Article 10
  Article 9
  Article 8
  Article 7
  Article 6

  Article 5
  Article 4
  Article 3
  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

#2 printf

  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 20 October 2006 - 02:48 AM

Get the month, year using DATE_FORMAT, ORDER BY the DATE_FORMAT year 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!

$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";

// legend

where it says my_table

my_table = you would change that to your database table name

where it says date_column

date_column = change that to your date column name

where it says , other_fileds

, other_fileds = you would add the other fields you want the result array to return

where it says $row['title']

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


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


#3 black.horizons

  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 20 October 2006 - 09:57 AM

thanks i'll try that out later on and get back to you! Life saver!!

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users