Creating an Archive from Date

[tommurray]

Hi

I have a series of news articles that get published by a user into a database, one of the fields is the date (2007/09/20). Is it possible to filter out the results and create links to articles for the months of the current year? and then also have year links to articles in 2007, 2006 etc.

Can anyone point me in the right direction please?

Thank you

Regards

Tom

[pbmods]

Heya, Tom.

Probably the easiest way would be to use strtotime() and date():

$archives = array();
 
while( $_row = mysql_fetch_assoc($_res) )
{
    $archives[ date( 'Y', strtotime($_row['publish_date']) ) ] = $_row;
}
 

[tommurray]

Thank You for your help I was completetly stuck and never knew which way to approach the task, I cannot test the script at the minute but will do ASAP.

An presumingly I can then use this line to obtain the months for the year.

[PHP]

$archives[ date( 'F', strtotime($_row['publish_date']) ) ] = $_row;

[/PHP]

But would this not result in a long list of repeated months and years?

As if I use disctinct in the SQL query it will recieve 2007/09/20 and 2007/09/21 and will turn both into either 2007 or September as they are both distinct?

Kind Regards

Tom

[pbmods]

Heya, Tom.

You might want to do something like this:

while( $_row = mysql_fetch_assoc($_res) )
{
    $_year = date( 'Y', strtotime($_row['publish_date']) );
    $_mon = date( 'F', strtotime($_row['publish_date']) );
 
    $archives[$_year][$_mon][] = $_row;
}
 

The [] part will cause PHP to create a new sub-array that stores the new $_row rather than overwriting whatever is already there (oops).

[tommurray]

I now have the following code:

[PHP]

$result = mysql_query("SELECT date FROM news")
or die(mysql_error());

$archives = array();

while( $_row = mysql_fetch_assoc($result) )
{
$_year = date( 'Y', strtotime($_row['date']) );
$_mon = date( 'F', strtotime($_row['date']) );

$archives[$_year][$_mon][] = $_row;

}

mysql_close();

[/PHP]

thank you so much for your help, if echo $_year and $_mon then I see what I was expecting too, now how can I go about only displaying unique years & Mon?

Also when filtering the news atricles by the year once I have the links setup and passing a variable through to another page to retrieve the articles for that year say 2005, would it be recommended to do a query along the lines of:

[PHP]

$_targetYear = $_POST['targetYear'}

while($row = mysql_fetch_array( $result ))
{
$_year = date( 'Y', strtotime($_row['date']) );
foreach($row as $_year = $_targetYear)
{
// Print out here what information i need such as title etc.
}
}
[/PHP]

Sorry to keep asking questions, i'm still learning PHP and getting there slowly but surely.

Thank you one again for your help.

Kind Regards

Tom

[pbmods]

Heya, Tom.

thank you so much for your help, if echo $_year and $_mon then I see what I was expecting too, now how can I go about only displaying unique years & Mon?

Ah. *that's* what you were talking about. Fortunately, it's no too difficult.

I'm going to make one suggestion:

while( $_row = mysql_fetch_assoc($_res) )
{
        $_year = date( 'Y', strtotime($_row['publish_date']) );
/**/    $_mon = date( 'm', strtotime($_row['publish_date']) );/**/
 
        $archives[$_year][$_mon][] = $_row;
}
 
$months =
    array
    (
        1 => 'January',
        2 => 'February',
        .
        .
        .
        12 => 'December',
        13 => 'Oops'
    );
 

foreach( $archives as $_year => $_yearData )
{
    echo "
        <a href=\"/path/to/archives.php?year={$_year}\">{$_year}</a>
        <ul>";
 
    foreach( $_yearData as $_month => $_monthData )
    {
        echo "
            <li>
                <a href=\"/path/to/archives.php?year={$_year}&month={$_month}\">{$months[$_month]}</a>
                <ul>";
 
            foreach( $_monthData as $_article )
            {
                echo "
                    <li>
                        <a href=\"/path/to/archives.php?articleid={$_article['articleid']}\">{$_article['title']}</a>
                    </li>";
            }
 
            echo "
                </ul>
            </li>";
    }
 
    echo "
        </ul>";
}
 

Also when filtering the news atricles by the year once I have the links setup and passing a variable through to another page to retrieve the articles for that year say 2005, would it be recommended to do a query along the lines of:

[PHP]

$_targetYear = $_POST['targetYear'}

while($row = mysql_fetch_array( $result ))
{
$_year = date( 'Y', strtotime($_row['date']) );
foreach($row as $_year = $_targetYear)
{
// Print out here what information i need such as title etc.
}
}
[/PHP]

That won't quite work; aside from the syntax error, it's not really efficient to load your entire table and then process it in PHP, especially when MySQL will do all the hard work for you.

The trick is to use date() and strtotime() to format your query, and then only load articles whose `publish_date` falls within a given range.

For example, if you went to articles.php?year=2000&month=6:

// Validate $_GET['year'] and $_GET['month'] and create date strings.
// The end result will look something like this:
$_startDate = '2000-06-01 00:00:00';
$_endDate = '2000-06-30 11:59:59';  // Hint:  Use strtotime on 2000-07-01 11:59:59 and then subtract the number of seconds in one day.  Then use date().
 
$_sql = "
SELECT
        *
    FROM
        `articles`
    WHERE
        `publish_date`
            BETWEEN
                    '{$_startDate}'
                AND
                    '{$_endDate}'
    ORDER BY
        `publish_date` DESC";
 

You'd do something very similar if no month were specified (e.g., fetch all articles between 2000-01-01 00:00:00 and 2000-12-31 11:59:59).

Sorry to keep asking questions, i'm still learning PHP and getting there slowly but surely.

That's OK; I'm supposed to be working right now :P

Keep on it, and post back if you get stuck.

[tommurray]

pbmods


Thank you for your help I have created the archive links, I have added a couple of if statements to show only the blog titles for the current month.

But your script has also provided me with a great way of creating a site map for all the archives, which I think will only improve usability and accessibility.

Thank you. I'm in the process of implementing your second suggestion to retrieve the articles from the database.

Thank you for your help.

If you don't mind could I post back if I have any problems? I owe big time thank you.

Tom

[pbmods]

Heya, Tom.

Go right ahead; that's what we're here for :)

Good luck with your project, and if you ever need anything, post back anytime.

[tommurray]

Hey pdmod its me again unfortunatly for you i'm trying to pick your brain :)

I have a problem were I am up to the part of running through the string date and time as you suggested using the strtotime.

I have created an if statement to catch if there is a variable for the month passed through if not then the start month gets set to January and the end month December.

If a month is passed through then I am setting the amounts of days within the month.

at the end of the statements and before the strtotime begins. Everything works fine when I echo $_startDate & $_endDate.

What am I doing wrong with the strtotime? as when I attempt to echo the results nothing is displayed.

Thanks you in advance

Kind Regards

Tom

[PHP]

$year = $_GET['year'];
$mon = $_GET['month'];

if ($mon == 'January' || $mon == 'March' || $mon == 'May' || $mon == 'July' || $mon == 'August' || $mon == 'October' || $mon == 'December')
}
$day = '31';
}
else if ($mon == 'April' || $mon == 'June' || $mon == 'September' || $mon == 'November' )
{
$day = '30';
}
else if ($mon == 'February')
{
$day = '28';
}

if ($mon == '')
{
$_startDate = "".$year."-January-01 00:00:00";
$_endDate = "".$year."-December-31 11:59:59";
}
else
{
$_startDate = "".$year."-".$mon."-".$day." 00:00:00";
$_endDate = "".$year."-".$mon."-".$day." 11:59:59";
}

$_start_period = date('Y-m-d H:i:s', strtotime($_startDate));
$_end_period = date('Y-m-d H:i:s', strtotime($_endDate));

echo $_start_period;
echo $_end_period;
[/PHP]

[pbmods]

Heya, Tom.

Rather than try to determine what the last day of the month is (don't forget about leap years and February!), let PHP handle that and focus on what you do know:

• strtotime() returns a value in seconds.
• The last day of the month plus one day is the first day of the next month.
• There are 86,400 seconds in a day.

To test the theory, try this:

echo date( 'F j, Y g:i a', time() - 86400 );
 

The output should be exactly 1 day ago.

What if the current date was, say, July 1?

echo date( 'F j, Y g:i a', strtotime('2007-07-01 11:59:59') - 86400 );
 

The result is June 30, 2007 11:59 PM. Hm....

[tommurray]

pbmod

Thank you i understand how to use strtotime now but i'm completely stuck on how to get the year and month into the equation? How can I get it to go one month ahead to then minus 86400 seconds off?

I have looked around an I saw you can put plus one month but that will only add on 30 days so that no good for this situation.

Thank you so much for your help.

Tom

[pbmods]

Heya, Tom.

If you [should] calculate the first of the month like this:

$_startDate = $year."-".$mon."-1 00:00:00";
 

Then you can calculate the last day of the month like this:

$_nextMonth = $mon + 1;
$_endDate = date( 'Y-m-d H:i:s', strtotime("{$year}-{$_nextMonth}-1 11:59:59") - 86400 );
 

[tommurray]

pbmods

Thank you for your help I have it all working correctly thanks to your expertise, one problem I did have is converting the string 'September' or any other month to is numerical value '09' is this somthing which can be done?

I got round the problem with if and else statements but was wondering if strtotime should have worked in this situation.

Kind Regards

Tom

[pbmods]

Heya, Tom.

You can use an associative array for that:

$_months =
    array
    (
        'january' => '01',
        .
        .
        .
    );
$_month = $_months[strtolower($mon)];
 

Making everything lowercase is not strictly necessary, but it does make your code a bit more robust.

[tommurray]

pbmods

Thank you for all your help i owe you big time.

Tom

[pbmods]

Heya, Tom.

We aim to please.