代码之家 › 专栏 › 技术社区 › Ben

在MySQL中生成年/月列表

date mysql

Ben · 技术社区 · 14 年前

目前,对于我的博客,我使用以下代码获取年份/日期列表:

    $monthList = array(
        1 => 'January',
        2 => 'February',
        3 => 'March',
        4 => 'April',
        5 => 'May',
        6 => 'June',
        7 => 'July',
        8 => 'August',
        9 => 'September',
        10 => 'November',
        11 => 'October',
        12 => 'December'
    );
    $nav = array();
    for ($year = 2009; $year <= date('Y'); $year++) {
        foreach ($monthList as $id => $month) {
            $this->tru->query->runRaw(array(
                'name' => 'get-headline-count',
                'sql' => 'SELECT
                        COUNT(id) as count
                    FROM 
                        wp_'.$this->getId().'_posts
                    WHERE
                        post_status = \'publish\' AND
                        post_type = \'post\' AND
                        post_parent = \'0\' AND
                        YEAR(post_date) = '.$year.' AND
                        MONTH(post_date) = '.$id.'',
                'connection' => 'article'
            ));
            $temp = $this->tru->query->getArray('get-headline-count');
            if ($temp['count'] > 0) {
                $nav[$year][$id] = $temp['count'];
            }
        }
    }

    krsort($nav);

    return array(
        'month' => $monthList,
        'nav' => $nav
    );

起初这一切都很好,但现在我有了几年的数据和几个月的数据,这将不会再削减它。我以前从来没有做过这样的事情,我通常处理时间戳。

1 回复 | 直到 14 年前

renick 14 年前

您可以用这个查询替换循环

SELECT
   YEAR(post_date) as yr, MONTH(post_date) as mn, COUNT(id) as count
FROM 
    wp_posts
WHERE
    post_status = 'publish' AND
    post_type = 'post' AND
    post_parent = '0' 
GROUP BY YEAR(post_date), MONTH(post_date)