MySQL Query – is there a better way?

Question

On my blog I am have a function that counts how many blog posts have been made month by month and these can be displayed in a sub menu.

It is looped by year, and then another nested loop by month. Each time it runs this query to count how many articles were posted in each month.

This is the query:

Select * from DM_blog blog where  blog.id IN 
    ( SELECT entry_id FROM DM_tags_target tagstarget WHERE tagstarget.parent_id IN 
    ( SELECT id FROM DM_tags tags WHERE tags.tag = '".$data['page']['id']."' AND tags.type = 'blog_target' )) AND blog.publish < '".date("Y-m-d H:i:s")."' and blog.status = '0'  and YEAR(blog.publish) = '".$year."' and MONTH(blog.publish) = '".$month."'"

Is there a better way to do this? Can I do all of the months at once instead of having to do 12 queries a year? The full code is here:

$this->benchmark->mark('blog_submenu_start');
    $dates = $this->blog_model->menu_dates($data);
    if($dates['years']){
        $i = 0;
        foreach($dates['years'] as $year){
            if($data['segments']['modal'] == $year['YEAR(blog.publish)']){
                $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['class'] = 'selected';
                $selected = true;
            }else if(date("Y") == $year['YEAR(blog.publish)'] && $selected == false){
                $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['class'] = 'selected';
                $selected = true;
            }

            // find the start month
            if($dates['first_entry'][0]['YEAR(blog.publish)'] == $year['YEAR(blog.publish)']){
                if($dates['first_entry'][0]['MONTH(blog.publish)'] < 2){
                    $limit_s['start'] = '1';
                    $limit_s['end'] = 13;
                }else{
                    $limit_s['start'] = $dates['first_entry'][0]['MONTH(blog.publish)'];
                    $limit_s['end'] = 12;
                }
            }else{
                $limit_s['start'] = 1;
                $limit_s['end'] = 13;
            }
            // run through the months
            $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_start');
            for($x=$limit_s['start'];$x<=$limit_s['end'];$x++){
                $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_start');
                $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_mysql_start');
                $count = $this->blog_model->month_count($year['YEAR(blog.publish)'],$x,$data);
            $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_mysql_end');
                if($last == false){
                    $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_selected_start');
                    if($data['segments']['modal'] == $year['YEAR(blog.publish)'] and $data['segments']['para_one'] == $x){
                        $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['months'][$x]['class'] = 'selected';
                    }
                    $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_selected_end');
                    $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_assign_start');
                    $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['months'][$x]['month'] = $x;
                    $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['months'][$x]['display_full'] = date('F',strtotime($year['YEAR(blog.publish)'].'-'.$x.'-01')); 
                    $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['months'][$x]['display_short'] = date('M',strtotime($year['YEAR(blog.publish)'].'-'.$x.'-01')); 
                    $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['months'][$x]['count'] = $count;
                    $data['date_submenu']['year'][$year['YEAR(blog.publish)']]['months'][$x]['sef'] = $year['YEAR(blog.publish)'].'/'.$x.'/';
                    $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_assign_end');
                    if(date("Y") == $year['YEAR(blog.publish)'] and date("m") == $x || date("n") == $x){
                        $last = true;
                    } // end date
                    $i++;
                    $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_'.$x.'_end');
                } // end last false
            } // end for
            $this->benchmark->mark('blog_submenu_dates_'.$year['YEAR(blog.publish)'].'_end');
        }
    }
    $this->benchmark->mark('blog_submenu_end');

$this->blog_model->month_count - this function is as follows:

function month_count($year,$month,$data=false){
    $query = $this->db->query("Select * from DM_blog blog where  blog.id IN 
    ( SELECT entry_id FROM DM_tags_target tagstarget WHERE tagstarget.parent_id IN 
    ( SELECT id FROM DM_tags tags WHERE tags.tag = '".$data['page']['id']."' AND tags.type = 'blog_target' )) AND blog.publish < '".date("Y-m-d H:i:s")."' and blog.status = '0'  and YEAR(blog.publish) = '".$year."' and MONTH(blog.publish) = '".$month."'");
    return $query->num_rows(); 
}

There are year() and month() functions on the mysql side. if you use them you could get a grouped result from the query directly — Christian Koch, Feb 03 '13 at 08:24
Have a look here for a better way to do what you're trying: http://stackoverflow.com/questions/3366895/group-by-month-and-year-in-mysql - Combine it with a count function and you should be set. — FreudianSlip, Feb 03 '13 at 08:26

score 1 · Answer 1 · answered Feb 03 '13 at 08:30

You can add group by clause to group the results by year and month.

Select * from DM_blog blog where  blog.id IN 
( SELECT entry_id FROM DM_tags_target tagstarget WHERE tagstarget.parent_id IN 
( SELECT id FROM DM_tags tags WHERE tags.tag = '".$data['page']['id']."' AND tags.type =     'blog_target' )) AND blog.publish < '".date("Y-m-d H:i:s")."' and blog.status = '0'  GROUP BY     YEAR(blog.publish), MONTH(blog.publish)

score 0 · Answer 2 · answered Feb 03 '13 at 08:29

You could use a GROUP BY expression:

SELECT id, YEAR(blog.publish), MONTH(blog.publish)
  FROM DM_blog blog 
 WHERE  blog.id IN 
( SELECT entry_id 
    FROM DM_tags_target tagstarget 
   WHERE tagstarget.parent_id IN 
         ( SELECT id 
             FROM DM_tags tags
            WHERE tags.tag = '".$data['page']['id']."' 
              AND tags.type = 'blog_target' 
          )
) 
AND blog.publish < '".date("Y-m-d H:i:s")."' 
AND blog.status = '0'  
GROUP BY YEAR(blog.publish), MONTH(blog.publish)

It will give you the results grouped by year and month

MySQL Query – is there a better way?

2 Answers2