i'm going round in circles - basically i'm trying to create a list of blog posts by month

ie for 2010

Jan (45)
Feb (33)
Mar (35)

etc & then clicking on the month would produce a display of that months posts

i can do the select of posts by month & display them no problem -

i can do a select by month no problem but then i've got to have 12 sets of seperate queries on the same page

i managed to get this sql query working in phpmyadmin but i cant work out how to actually display the results on the page as above so that i can then add in a link to send a get statement to get that months results:

SELECT DATE_FORMAT(MONTH(post_date),'%m/%d/%y' ) AS unixdate , COUNT(post_date) AS counted FROM wp_posts WHERE post_status='publish' AND YEAR(post_date)=2010 GROUP BY MONTH(post_date)

which i would then add in something like a href=month.php?name=$month&year=$year

this is the query on the php page

<?php


include 'config_blog.php';
include 'opendb.php';

$result = mysql_query("SELECT DATE_FORMAT(MONTH(post_date),'%m/%d/%y' ) AS unixdate , COUNT(post_date) AS counted FROM wp_posts WHERE post_status='publish' AND YEAR(post_date)=2010 GROUP BY MONTH(post_date)", $connection) or die("error querying database");
$i = 0;
while($result_ar = mysql_fetch_assoc($result)){


?>

and then ideally something like:

<href=month.php?name=<?php echo $result_ar['MONTH']; ?>&year=<?php echo $result_ar['YEAR']; ?>

<?php
$i+=1;
}
?>

if this makes sense 🙂

    Trust your database...I don't think you're doing yourself any favors by trying to combine queries.

    I would do something like this:

    // Get month/year for last 12 months...
    for($x = 0; $x < 13; $x++)
      {
      // Get month/year for SQL query...
      $fdom = mktime(0, 0, 0, date("m") - $x, 1, date("Y");      // fdom = first day of month (Unix)
      $year = date("Y", $fdom);
      $month = date("m", $fdom);
    
      // Get and display post count for month...
      $date_cond = "MONTH(post_date) = $month AND YEAR(post_date) = $year";
      $q1 = "SELECT COUNT(post_id) AS post_count FROM wp_posts ".
        "WHERE $date_cond";
      $res = mysql_query($q1);
      $post_count = $row['post_count'];
      echo "<h1>$month - $year  ($post_count)</h1>\n";
    
      // Get and display individual posts for month...
      $q2 = "SELECT <...details...> FROM wp_posts WHERE $date_cond";
      $res = myql_query($q2);
      while($row = mysql_fetch_assoc($res))
        {
        // Output individual posts here
        }
      }

    ...or, if you are trying to just display a summary and a link to detail, you would replace the above <h1> line with:

    echo "<h1><a href='post_detail.php?m=$month&y=$year'>$month - $year ($post_count)</a></h1>\n";

    ...then on your post_detail.php page, you use the same query as above but take your variables from the URL:

    $month = $_GET['m'];
    $year = $_GET['y'];
    
    // Note: should do some validation of month/year here
    
    $date_cond = "MONTH(post_date) = $month AND YEAR(post_date) = $year";
    
    // Get and display individual posts for month...
    $q2 = "SELECT <...details...> FROM wp_posts WHERE $date_cond";
    $res = myql_query($q2);
    while($row = mysql_fetch_assoc($res))
      {
      // Output individual posts here
      }
      ixalmedia wrote:

      Trust your database...

      You say that, and then you go and perform queries in a loop, doing all of the date handling in PHP?? Pick one side or the other! 🙂

      Here's my solution:

      $year = date('Y'); // set to current year; could use variable/query string instead
      
      $q1 = "SELECT COUNT(post_id) post_count, MONTH(post_date) `month`
      		   FROM wp_posts
      		   WHERE YEAR(post_date)=$year
      		   GROUP BY MONTH(post_date)"; 
      $res = mysql_query($q1);
      
      // Store post counts into array using month number as key
      $post_counts = array();
      while($row = mysql_fetch_assoc($res))
      	$post_counts[$row['month']] = $row['post_count'];
      
      // Run through months and output totals
      for($month_num = 1; $month_num <= 12; $month_num++) 
      { 
        $month = date('m', mktime(0,0,0,$month_num));
        $post_count = (isset($post_counts[$month_num]) ? $post_counts[$month_num] : 0);
      
        // Add link here, e.g. to "posts.php?year=$year&month=$month"
        echo "<h1>$month - $year  ($post_count)</h1>\n"; 
      }

      It's untested (don't have access to a MySQL server at the moment), but it looks right to me. :p

        Not what I meant. I meant that for the most part, the database can handle simple queries and not to overthink it.

        Of course, I say this while at the same time asking a query optimization question in another thread...

          thanks bradgrafelman

          exactly - just need to format/display it properly but it looks good to go

            How about:

            $q1 = "SELECT COUNT(post_id) post_count, MONTH(post_date) month
            FROM wp_posts
            WHERE YEAR(post_date)=$year
            GROUP BY MONTH(post_date)
            LIMIT 12";

            Then you don't have to create an array and loop through. Might have to add an "ORDER BY MONTH(post_date) DESC" clause as well...but maybe not.

              ixalmida wrote:

              Then you don't have to create an array and loop through.

              While the LIMIT might be useful for optimization purposes, that doesn't change any part of the PHP code... I don't see what you mean that you "don't have to create an array and loop through."

              ixalmida wrote:

              Might have to add an "ORDER BY MONTH(post_date) DESC" clause as well...but maybe not.

              There's no need to make the DB to any extra work and order the result set... we're just dumping the values into an array that PHP will grab the appropriate values from when it loops through the months.

                Here's a MySQL stored procedure that you might find handy. It saves a bunch of time on DATE_FORMAT statements and does a little validation too.

                FUNCTION `engShortDate`(mydate DATE) RETURNS varchar(10) CHARSET latin1
                    DETERMINISTIC
                BEGIN
                  DECLARE minDate DATE;
                  DECLARE newDate VARCHAR(10);
                
                  SET minDate = DATE('1999-01-01');
                
                  /* If value is below the specified minimum date, return "NULL" */
                  IF(mydate > minDate) THEN
                    SET newDate = DATE_FORMAT(mydate, '%c/%e/%Y');
                  END IF;
                
                  RETURN newDate;
                END

                So instead of:

                  DATE_FORMAT(MONTH(post_date),'%m/%d/%y' )

                You can just have:

                  engShortDate(post_date)

                I have one for "long" dates too (includes the time) but it requires a time zone offset variable so it probably isn't for everyone.

                  Here's yet another way to accomplish the monthly listing. This method outputs the headers as rows are retrieved (filling in any gaps) rather than storing them in a temporary array and then looping again from month #1 to 12 using a for() loop:

                  $year = date('Y'); // set to current year; could use variable/query string instead
                  
                  $q1 = "SELECT COUNT(post_id) post_count, MONTH(post_date) `month`
                             FROM wp_posts
                             WHERE YEAR(post_date)=$year
                             GROUP BY MONTH(post_date)
                             ORDER BY MONTH(post_date) DESC
                             LIMIT 12"; 
                  $res = mysql_query($q1);
                  
                  // Store post counts into array using month number as key
                  $month_num = 1; // initialize to first month
                  while($row = mysql_fetch_assoc($res))
                  {
                  	// handle gaps in result set, e.g. no posts for given month(s)
                  	while($row['month'] > $month_num)
                  	{
                  		$month = date('m', mktime(0, 0, 0, $month_num++));
                  		echo "<h1>$month - $year (0)</h1>\n";
                  	}
                  
                    $month = date('m', mktime(0, 0, 0, $month_num++));
                  
                    // Add link here, e.g. to "posts.php?year=$year&month=$month"
                    echo "<h1>$month - $year ($row[post_count])</h1>\n";
                  }

                    that doesn't change any part of the PHP code

                    Sure it does...

                    // Get 12 months of data only...
                    $q1 = "SELECT COUNT(post_id) post_count, MONTH(post_date) `month`, YEAR(post_date) `year`
                               FROM wp_posts
                               WHERE YEAR(post_date)=$year
                               GROUP BY MONTH(post_date)
                               ORDER BY post_date DESC
                               LIMIT 12"; 
                    $res = mysql_query($q1);
                    while($res = mysql_fetch_assoc($res))
                      {
                      extract($row);
                      $echo "<h1>$month - $year ($post_count)</h1>\n";
                      }

                    You would need the ORDER BY in order to pull out the last 12 months though. I'm not contending that this is more efficient but it does make the code simpler.

                    --->> Gah - I see you came to the same conclusion while I was typing this...

                      Except with your method, if no posts appear for any given month, then that month(s) won't appear in the listing. Although the OP didn't state that this was a requirement, I assumed that he/she always wanted 12 months listed for any given year, even if it meant stating that a given month had 0 posts.

                        You couldn't use "ORDER BY MONTH(post_date)" due to splitting across years. You'd get 12(2009), 11(2009)...3(2010), 2(2010), etc...

                        You'd have to write it: "ORDER BY post_date DESC"

                          that month(s) won't appear in the listing

                          Valid point. Didn't think of that. 🙂

                            As per the OP's first post:

                            adeibiza wrote:

                            basically i'm trying to create a list of blog posts by month

                            ie for 2010

                            Jan (45)
                            Feb (33)
                            Mar (35)

                            Thus is seemed to me like the intent was to create a simple yearly index, rather than a running history (e.g. from the current month backwards, spanning across different years as you're suggestion).

                              Confirmed on my db..."ORDER BY MONTH(created) DESC" gives you this order:

                              12-2009 (12)
                              11-2009 (15)
                              ...
                              4-2010 (19)
                              3-2010 (17)
                              ...etc.

                              "ORDER BY created DESC" gives you the correct order:

                              4-2010 (19)
                              3-2010 (17)
                              etc...

                                I should read more carefully, I guess. Doh.

                                  Write a Reply...