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

Recommended Answers

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.

Jump to Post

All 3 Replies

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.

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

Cheers,
eshko

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?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.