How to use PHP to archive MySQL content into months via Date() function?

[matar84]

The MySQL database is very simple. It consists of rows with the following fields:

ID, NewsDate, Subject, ShortStory, FullStory

The NewsDate field is in the MySQL format Date().

I am very new to PHP... could someone tell me how I could use these dates (YYYY-MM-DD) to automatically archive by years and months? I want my news archive to look something like:

2002
Jan
Feb
March
Etc

2003
Jan
Feb
March
Etc

And after you click a month, I want it to have a list of the news for that month by subject with a link to the file display.php?id=? (the file I made that displays the news story).

Any how, could someone show me the type of code to use to make it list them under years and months by using MySQL's Date() function? I really have no clue where to start. I am pretty new at this.

[nnichols]

This code is quite crude but it should work and give you a starting point -

<?

$host = 'localhost';	// enter your host name here
$db_user = 'test';	// enter your database username here
$db_pass = 'test';	// enter your database password here
$db_name = 'news';	// enter your database name here
$news_table = 'news';	// enter the name of the news table here

// establish connection and select database
$db = @mysql_pconnect($host, $db_user, $db_pass)
	or die('Failed to connect to db server.<br>MySQL error: '.mysql_error());
@mysql_select_db($db_name)
	or die('Failed to select db.<br>MySQL error: '.mysql_error());

// if month/year already selected
if (isset($HTTP_GET_VARS['month']) && isset($HTTP_GET_VARS['year'])) {
$month = $HTTP_GET_VARS['month'];
$year = $HTTP_GET_VARS['year'];

// query for news articles for selected month
$sql = "SELECT ID, NewsDate, Subject
	FROM $news_table 
	WHERE DATE_FORMAT(NewsDate,'%b') = '$month'
	AND DATE_FORMAT(NewsDate,'%Y') = '$year'
	ORDER BY NewsDate ASC";

// execute query
$res = @mysql_query($sql, $db)
	or die('Query Failed<br>MySQL error: '.mysql_error().'<br>Query: '.$sql);

// display links
echo '<table>';
echo '<tr><th>ID</th><th>NewsDate</th><th>Subject</th></tr>';

while ($row = mysql_fetch_array($res)) {

echo '<tr><td>'.$row['ID'].'</td><td>'.$row['NewsDate'].'</td><td><a href="display.php?id='.$row['ID'].'">'.$row['Subject'].'</a></td></tr>';

} // end while
echo '</table>';
} // end if

// if month/year not selected yet
else {

// query for Month Year
$sql = "SELECT DISTINCT(DATE_FORMAT(NewsDate,'%b %Y')) AS NewsMY, DATE_FORMAT(NewsDate,'month=%b&year=%Y') AS query_string
	FROM $news_table 
	ORDER BY NewsMY ASC";

// execute query
$res = @mysql_query($sql, $db)
	or die('Query Failed<br>MySQL error: '.mysql_error().'<br>Query: '.$sql);

// display links
while ($row = mysql_fetch_array($res)) {

echo '<a href="'.$PHP_SELF.'?'.$row['query_string'].'">'.$row['NewsMY'].'</a><br>';

} // end while

} // end else

?>

Hope this helps 😉

[matar84]

Wow, that code is excellent. I couldn't thank you enough!

[nnichols]

No probs. Please note that the code is not particularly efficient and some of the things I have included are just to give you some examples of different approaches.

Happy coding 🙂

[matar84]

I'm guessing this is a typo?

NewsDate,'%b %YYYY'

I changed it to one Y instead of four. :-)

[nnichols]

Yeah, it was a typo. Did it work other than that?

[matar84]

Yes it worked very well. Of course I will try to change many things in order to customize it. But it's great.

[nnichols]

change "ORDER BY NewsMY ASC" to "ORDER BY NewsDate ASC" in the second query

Hope this helps 😉