1,105,281 Community Members

PHP & MySQL Archive

Member Avatar
eshko
Newbie Poster
9 posts since Jun 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hello every one!

I'm developing one simple site, and I need to make an "archive" script.

My MySQL Table looks like:

| id | title | content | is_deleted | pub_date |

*pub_date is in PHP time() format. Example: 1258150430

So, what I want to make is an example tree view of entries from database sorted by year and month. Example:

2009

-January

--Post title 1
--Post title 2
--Post title 3
--Post title 4

-February

--Post title 1
--Post title 2
--Post title 3
--Post title 4

-April

--Post title 1
--Post title 2
--Post title 3
--Post title 4

2010

-January

--Post title 1
--Post title 2
--Post title 3
--Post title 4

-February

--Post title 1
--Post title 2
--Post title 3
--Post title 4

-April

--Post title 1
--Post title 2
--Post title 3
--Post title 4

Can anyone help me with this problem? I really don't know how to do this...

Cheers,
eshko

Member Avatar
Atli
Practically a Master Poster
691 posts since May 2007
Reputation Points: 182 [?]
Q&As Helped to Solve: 107 [?]
Skill Endorsements: 16 [?]
 
0
 

Hey.

*pub_date is in PHP time() format. Example: 1258150430

It is generally better to use MySQL's DATE and TIME types to store date or time. Easier to work with it that way.

Anyhow. You can either fetch all the data at once and have PHP format it, or you can fetch the data in batches, based on the dates (DATE types come in handy here), and display it in PHP.

For example, using the former method:

<?php
// Fetch all the data
$dbLink = new mysqli('localhost', 'usr', 'pwd', 'dbName');
$sql = "SELECT id, title, pub_date FROM myTable
        WHERE is_deleted = FALSE
        ORDER BY pub_date DESC";
$result = $dbLink->query($sql);
if($result && $result->num_rows > 0)
{
    // An array to store the data in a more managable order.
    $data = array();
    
    // Add each entry to the $data array, sorted by Year and Month
    while($row = $result->fetch_assoc())
    {
        $year = date('Y', $row['pub_date']);
        $month = date('m', row['pub_date'])
        
        $data[$year][$month][] = $row;
    }
    $result->free();
    
    // Go through each Year and Month and print a list of entries, sorted by month.
    foreach($data as $_year => $_months)
    {
        echo "<h1>{$_year}</h1>";
        foreach($_months as $_month => $_entries)
        {
            echo "<h2>{$_month}</h2>";
            echo "<ul>";
            foreach($_entries as $_entry)
            {
                echo "<li><a href=\"show_entry.php?id={$_entry['id']}\">{$_entry['title']}</a></li>";
            }
            echo "</ul>";
        }
    }
}

$dbLink->close();
?>

If you have a lot of entries, this could consume a lot of memory. It would probably be best to do it in smaller batches, but that would be easier if you used the DATE or DATETIME type for your dates.

Member Avatar
eshko
Newbie Poster
9 posts since Jun 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Thank you very much for helping me! This script works very well!

Cheers,
eshko

Question Answered as of 4 Years Ago by Atli
Member Avatar
chocalate
Newbie Poster
1 post since Jan 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi ,

AS refer to the blog monthly achieve entries as above code it work well . Thank you.:)

However, I would like to ask you on how to do the drop-down menu for the monthly blog archive instead of listing the month down?

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: