PHP & MySQL Archive

[eshko]

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

[Atli]

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.

[eshko]

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

Cheers,
eshko

[chocalate]

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?