使用php和mysql按年份和月份对归档进行分组 [英] grouping archive by year and month using php and mysql

问题描述

我要创建一个这样的存档列表:

I want to create an archive list like this:

• 2014
  • 三月
  • 二月
  • 简
    • 帖子1
    • 帖子2
    • 2014
      • March
      • Feb
      • Jan
        • Post 1
        • Post 2
        • 十一月
          • 帖子1
          • November
            • Post 1

            我正在使用PDO.我正在使用的表将postDate作为Datetime. postSlug用于获取干净的URL.我现在使用的编码是:

            I am using by PDO. the table I m using is having postDate as Datetime. postSlug is used to get a clean url. The coding I am using now is:

                <h1>Archives</h1>
                <hr />
            
                <ul>
            <?php
            $stmt = $db->query("SELECT postTitle, Month(postDate) as Month, Year(postDate) as Year FROM blog_posts_seo ORDER BY postDate DESC");
            while($row = $stmt->fetch()){
            $posts = $row['postTitle'];
            $year = $row['Year'];
                $monthName = date("F", mktime(0, 0, 0, $row['Month'], 10));
                $slug = 'a-'.$row['Month'].'-'.$row['Year'];
                    echo "<li>$year</li>";
                echo "<ul><li><a href='$slug'>$monthName</a></li>";
            
                    echo "<ul><li><a href='#'>$posts</a></li></ul></ul>";
            }
            ?>
            </ul>
            

            即时通讯得到的结果如下:

            The result im getting is as follows:

            2014
                May
                    Post
            2013
                June
                    Post
            2013
                June
                    Post
            2012
                June
                    Post
            

            简而言之，如何使用php对帖子和月份进行分组? 我是php和mysql的初学者.因此，如果您知道解决方案，如果可以帮助我完成完整的编码，那将是非常有用的.谢谢！

            In short how do I group the posts and months accordingly using php? I am am beginner in php and mysql. Therefore it would be of great help if you can help me in the complete coding if you know the solution. Thanks!

            推荐答案

            那怎么样?

            $data = array();
            while($row = $stmt->fetch()){
              $monthName = date("F", mktime(0, 0, 0, $row['Month'], 10));     
              $data[$row['Year']][$monthName][] = array(
               'post' => $row['postTitle'],
               'slug' => 'a-'.$row['Month'].'-'.$row['Year']
              );
            }
            echo '<ul>';
            foreach ($data as $year => $yearData){
               echo "<li>$year<br/>";
               echo '<ul>';
               foreach ($yearData as $month => $monthData){
                  echo "<li>$month<br/>";
                  echo '<ul>';
                  foreach ($monthData as $number => $postData){
                    echo "<li><a href='${postData['slug']}'>Post $number</a><br/>";
                    echo "<a href='#'>${postData['post']}</a></li>";
                  }
                  echo '</ul></li>';
               }
               echo '</ul></li>';
            }
            echo '</ul>';
            

            此解决方案以PHP方式实现，但是您也应该能够通过SQL查询获得结果，例如:

            This solution does it the PHP way, but you should be able to get the result with a SQL query too, with something like:

            SELECT
                Year(postDate) as Year, Month(postDate) as Month,
                GROUP_CONCAT(postTitle) as posts
            FROM
                blog_posts_seo
            GROUP BY Year, Month
            ORDER BY postDate DESC
            

            其中应该将所有与年份和月份相关的帖子返回到一行(未经测试)，并以逗号分隔.使用WITH SEPARATOR选项指定其他分隔符(请检查文档).

            which should return all the posts related by year and month in a single row (not tested), separated by commas. Use the WITH SEPARATOR option to specify a different separator (check the doc).

            文档:

            • GROUP BY和GROUP_CONCAT

            这篇关于使用php和mysql按年份和月份对归档进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！