CODEWITHSUNDEEP
phpmysql
Alex.DX
Alex.DX

Reputation: 141

Advanced news archive Years/Month using PHP MySql

I insert date of news with timestamp in MySQL database. Now I need to advanced archive for my news Like This :

2013

    July (4)
         News 1 
         News 2 
         News 3 
         News 4 
    December (2)
         News 1 
         News 2 
    NovemberPrint (4)
         News 1 
         News 2 
         News 3 
         News 4 
2014
    January (8)
         News 1 
         News 2 
         News 3 
         News 4 
         News 5 
         News 6 
         News 7 
         News 8

Now I need any PHP function and MySQL SELECT structure for print this using PDO Or MySQL Query.

Online Example HERE

EDIT: I found This Ask From Here: 

$sql = "SELECT YEAR(FROM_UNIXTIME(timestamp)) AS YEAR, 
                MONTHNAME(FROM_UNIXTIME(timestamp)) AS MONTH, 
                COUNT(*) AS TOTAL 
         FROM NEWS GROUP BY YEAR, MONTH ORDER BY YEAR DESC, MONTH ";
$newsdata = DataAccess::ArrayFetch($sql);

$currentYear = null;

foreach($newsdata AS $news){            
  if ($currentYear != $news['YEAR']){
    echo '<h2>'.$news['YEAR'].'<h2>';
    $currentYear = $news['YEAR'];
  }
 echo '<dd>'.$news['MONTH'].'<dd><dt>'.$news['TOTAL'].'</dt>';
}

But, This Print Only Count and GROUP By Years ad Month. I need to listed article after each month.

Upvotes: 3

Views: 2362

Answers (3)

artemn
artemn

Reputation: 2069

Try it:

SELECT YEAR(creation_date), MONTH(creation_date), COUNT(id) from news GROUP BY YEAR(creation_date), MONTH(creation_date)

Upvotes: 0

Dave
Dave

Reputation: 3658

You can't use GROUP BY if you also want the names of the individual articles. Simply fetch the entire list and then group into an array by year and month.

$sql = "SELECT timestamp, your_id_column, your_title_column FROM `NEWS` ORDER BY timestamp DESC";

$newsdata = DataAccess::ArrayFetch($sql);

$nav = array();

foreach ( $newsdata as $news ) {
    $year = date('Y', $news['timestamp']);
    $month = date('F', $news['timestamp']);
    $nav[$year][$month][$news['your_id_column']] = $news['your_title_column'];
}

When you iterate the resulting array you can count the news items for each year/month to generate the total.

<ul>
    <?php
    foreach ( $nav as $k => $v ) {
        ?>
        <li><?php echo $k ?>
            <ul>
            <?php
            foreach ( $v as $k2 => $v2 ) {
                ?>
                <li><?php echo $k2.' ('.sizeof($v2).')' ?>
                    <ul>
                        <?php
                        foreach ( $v2 as $k3 => $v3 ) {
                            ?>
                            <li><?php echo $v3 ?></li>
                            <?php   
                        }
                        ?>
                    </ul>
                </li>
                <?php
            }
            ?>
            </ul>
        </li>
        <?php
    }
    ?>
</ul>

Upvotes: 3

Jeremy T
Jeremy T

Reputation: 768

You're going to want to use MySQL for this:

ORDER BY DATE(actionTime)

Just order the results by date instead of grouping them, and replace your count(*) with just a *.

Upvotes: 0

Related Questions