CODEWITHSUNDEEP
phpmysqlrowsarchive
Matteo Corona
Matteo Corona

Reputation: 203

Archieve my blog'news by year and month, showing title and data of creation

I am trying archive a blog of news. I would like to make it similar to the one shown THERE

At the point I am, I can show the year, the month and the number of posts for each month, but I can't show the title and the slug of various posts.

My database structure is a table with these rows: id (int), title(varchar), slug(varchar), created(timestamp).

here u are the code:

$newsdata= $db->query("
    SELECT 
         YEAR(created) AS YEAR, 
         MONTHNAME(created) AS MONTH,
         title,
         slug,
         id,
         COUNT(*) AS TOTAL 
         FROM pages 
         GROUP BY YEAR, MONTH
         ORDER BY YEAR DESC, MONTH

")->fetchALL(PDO::FETCH_ASSOC);

$currentYear = null;

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

U can see the result HERE

I know also that there is an interesting post there about my needs but it doesn't help me to reach my goal.

Upvotes: 0

Views: 150

Answers (1)

Matteo Corona
Matteo Corona

Reputation: 203

I found the solution right form me HERE, if u want to read the entire discussion.

Here we go the code:

$db= new PDO('mysql:host=62.149.150.197;dbname=Sql692231_4', 'Sql692231', 'f7157ead'); //connection setup
$stmt = $db->query("
    SELECT 
    Month(created) as Month, 
    Year(created) as Year,
    title, 
    slug, 
    created 
    FROM pages 
    ORDER BY created DESC
");

 // you will store current month here to control when the month changes
 $currentMonth = 0;
 // you will store current year here to control when the year changes
 $currentYear = 0;

 while($row = $stmt->fetch()){
     // if the year changes you have to display another year
    if($row['Year'] != $currentYear) {
        // reinitialize current month
        $currentMonth = 0;
        // display the current year
        echo "<li class=\"cl-year\">{$row['Year']}</li>";
        // change the current year
        $currentYear = $row['Year'];
    }
    // if the month changes you have to display another month
    if($row['Month'] != $currentMonth) {
        // display the current month
        $monthName = date("F", mktime(0, 0, 0, $row['Month'], 10));
        echo "<li class=\"cl-month\">$monthName</li>";
        // change the current month
        $currentMonth = $row['Month'];
    }
    // display posts within the current month
    //$slug = 'a-'.$row['Month'].'-'.$row['Year'];
    //echo "<li class=\"cl-posts\"><a href='$slug'>$monthName</a></li>";
    echo '<li class="cl-posts active"><a       href="http://www.matteocorona.com/cms_images/page.php?page='.$row['slug'].'">'.$row['title'].'</a></li>';
    echo '<li class="cl-posts active">'.$row['created'].'</li>';
    echo "<br/>";
}

and the result,HERE

Upvotes: 1

Related Questions