General Question

klaas4's avatar

Make an archive-like page in PHP?

Asked by klaas4 (2194points) August 28th, 2009

Hi!

I want to make an archive for a news-system. My table’s as follows: http://bit.ly/3L2mUd.

My plan is to make an archive like this: http://bit.ly/XKh8T.

The problem is that the script automatically has to put the years above the posts, so that I don’t have to keep adding an other instance every year.

To make it easier here is the function that has to be executed for every year: showarticlesfor($year);

If you understand and can help, it would be greatly appreciated. I’m looking forward to see how this works.. It really have no idea how to do this.

Thanks! Davey.

Observing members: 0 Composing members: 0

4 Answers

Babbage's avatar

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()

klaas4's avatar

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. :)

Babbage's avatar

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.

klaas4's avatar

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

Answer this question

Login

or

Join

to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
or
Knowledge Networking @ Fluther