Make an archive-like page in PHP?

Hi Davey,

Here is some quick and dirty code for your ‘showarticlesfor($year)’ function which should get the job done (NOTE: I am assuming you are using MySQL as your DBMS):

function showarticlesfor($year){
$connection = mysql_connect(“MY_HOST”, “MY_USER_NAME”,“MY_PASSWORD”) or die(mysql_error());
mysql_select_db(“MY_DATABASE”, $connection);

$result = mysql_query(“SELECT title, DATE_FORMAT(TABLE_NAME.date, ’%d-%m’) FROM TABLE_NAME WHERE DATE_FORMAT(TABLE_NAME.date, ’%Y’) = ’$year’”, $connection) or die(mysql_error());

if(mysql_num_rows($result) > 0){
echo ”<br />$year<br /><br />”;
while($row = mysql_fetch_assoc($result)){
echo ”{$row[“date”]} {$row[“title”]}<br />\n”;
}
}
}

Be sure to replace ‘MY_HOST’, ‘MY_USER_NAME’, ‘MY_PASSWORD’, ‘MY_DATABASE’, and TABLE_NAME with the appropriate information. Also note that using the word ‘date’ as a column title in your table is not a good idea, as a lot of DBMS have this as a reserved word for their function/datatype, so you have to reference it in a SELECT as ‘TABLE_NAME.date’.

Explanation:
Basically, you are selecting all the data from your table that has a year of $year. You are also formatting the date output to be “day-month”. If there were any records returned to you, we first output the year, followed by a few breaks, and finally by the records (the while loop).

References:
MySQL DATE_FORMAT
PHP mysql_fetch_assoc()

Hi @Babbage,

Thank you very much, but I already have the code for that. It is appreciated to see how someone else does it though.

The problem is those years: I’m looking for a script which can search the database for all the years that there are posts from, and then loop it so that I can use the function to display the articles for that year..

Thanks for thinking with me though. :)

Well, there are a couple of ways to do that. You could try:

SELECT DISTINCT(DATE_FORMAT(TABLE_NAME.date, ”%Y”)) FROM TABLE_NAME;

Or you could try:

SELECT DATE_FORMAT(TABLE_NAME.date, ”%Y”) FROM TABLE_NAME GROUP BY DATE_FORMAT(TABLE_NAME.date, ”%Y”);

Both should return only the [DISTINCT] years which appear in the table. You can then loop through the results (similar to the while() loop I posted before), and process them with the showarticlesfor() function.

@Babbage Yeah, that’s what I meant! Thank you so very much, I’m gonna try right now!