PHP & MySQL Archive

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

eshko

Newbie Poster

9 posts since Jun 2009

Reputation Points: 10

Solved Threads: 0

2 Years Ago

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.

Atli

Posting Pro

540 posts since May 2007

Reputation Points: 93