Qiita - 言語別投稿数推移

qiita_lang_post_transition.php

<?php
    require 'vendor/autoload.php';
    
    ORM::configure('mysql:host=localhost;dbname=qiita');
    ORM::configure('username', 'xxxx');
    ORM::configure('password', 'xxxx');
    
    $sql = "SELECT CASE WHEN ( `title` like '%php%'    OR `body` like '%php%' )   THEN 'PHP' 
                        WHEN ( `title` like '%ruby%'   OR `body` like '%ruby%' )   THEN 'Ruby' 
                        WHEN ( `title` like '%python%' OR `body` like '%python%' ) THEN 'Python'
                        WHEN ( `title` like '%swift%' OR `body` like '%swift%' ) THEN 'Swift'
                        WHEN ( `title` like '%javascript%' OR `body` like '%javascript%' ) THEN 'Javascript'
                        ELSE 'その他' 
                   END AS lang
                 , LEFT(`item_created_at`, 7) AS ym
                 , COUNT(*) as cnt
            FROM `items` 
            GROUP BY lang, ym
            ORDER BY lang, ym ASC";
    
    $items = ORM::for_table('items')->raw_query($sql)->find_many();
    
    $ym = array();
    
    
    
    foreach($items as $rec){
        if ($rec->ym!="2015-10"){
            $data[$rec->lang][$rec->ym] = $rec->cnt;

            if (array_search($rec->ym, $ym)===false){
                $ym[] = $rec->ym;
            }
        }
    }
    
    require_once './view.php';