How to Create a Page of Archives (Summary) for All WordPress Posts/Pages using PHP?

I have just created two summary pages for my two blogs: https://helloacm.com/archives-of-pagesposts/ and http://www.codingforspeed.com/archives-of-pagesposts/.

In both pages, all articles (including pages) are sorted by months. The number of comments for each post will be counted and printed after the post title within brackets.

This can be good to list all articles/pages for users and for search engines as well, so this page also serves as a site-map.

As you can see in the summary page, first, it prints the total number of posts/pages, and for each month (only if there is at least a post/page published on that month), it will generate indented links.

So, how are we going to achieve this? First of all, within WordPress, you would need some plugins to be able to include PHP code within posts/pages. Go to the control panel of wordpress after you login and navigate to Plugins and ‘Add New’. Search ‘Include PHP’ and you will find many options.

Then, copy and paste the following PHP code into a single PHP file, under the theme root directory.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

// helloacm.com
  global $wpdb;
  $query = "select count(1) from `wp_posts` where (post_type = 'page' or post_type = 'post') and `post_status` = 'publish'";
  $total = $wpdb->get_var($query);
  echo "<h2>Archives</h2>";
  $query = "select concat(year(`post_date`), '-', month(`post_date`)) as `month`, count(1) as `cnt` from `wp_posts` where (post_type='page' or post_type='post') and `post_status` = 'publish' group by `month` order by `post_date` desc";
  $result = $wpdb->get_results($query);
  echo "<ul>";
  if ($result) {
    foreach ($result as $month) {
      echo "<li>".$month->month." (".$month->cnt.")</li>";  
      $m = explode('-', $month->month);
      $y = $m[0];
      $m = $m[1];
      $q = "select `post_name`, `post_title`, day(`post_date`) as `day`, `id` from `wp_posts` where (post_type='page' or post_type='post') and `post_status` = 'publish' and month(`post_date`)='$m' and year(`post_date`)='$y' order by `post_date` desc";
      $r = $wpdb->get_results($q);
      if ($r) {
        echo "<ul>";
        foreach ($r as $post) {
          $cnt = $wpdb->get_var("select count(1) from `wp_comments` where `comment_approved`=1 and `comment_post_ID` = ".$post->id);
          if (!$cnt) $cnt = 0;          
          echo "<li>".$post->day.": <a title='".htmlentities($post->post_title, ENT_QUOTES)."' href='https://helloacm.com/".$post->post_name."/'>".$post->post_title."</a> ($cnt)</li>";  
        }
        echo "</ul>";
      }
    }  
  }
  echo "</ul>";

// helloacm.com
  global $wpdb;
  $query = "select count(1) from `wp_posts` where (post_type = 'page' or post_type = 'post') and `post_status` = 'publish'";
  $total = $wpdb->get_var($query);
  echo "<h2>Archives</h2>";
  $query = "select concat(year(`post_date`), '-', month(`post_date`)) as `month`, count(1) as `cnt` from `wp_posts` where (post_type='page' or post_type='post') and `post_status` = 'publish' group by `month` order by `post_date` desc";
  $result = $wpdb->get_results($query);
  echo "<ul>";
  if ($result) {
    foreach ($result as $month) {
      echo "<li>".$month->month." (".$month->cnt.")</li>";  
      $m = explode('-', $month->month);
      $y = $m[0];
      $m = $m[1];
      $q = "select `post_name`, `post_title`, day(`post_date`) as `day`, `id` from `wp_posts` where (post_type='page' or post_type='post') and `post_status` = 'publish' and month(`post_date`)='$m' and year(`post_date`)='$y' order by `post_date` desc";
      $r = $wpdb->get_results($q);
      if ($r) {
        echo "<ul>";
        foreach ($r as $post) {
          $cnt = $wpdb->get_var("select count(1) from `wp_comments` where `comment_approved`=1 and `comment_post_ID` = ".$post->id);
          if (!$cnt) $cnt = 0;          
          echo "<li>".$post->day.": <a title='".htmlentities($post->post_title, ENT_QUOTES)."' href='https://helloacm.com/".$post->post_name."/'>".$post->post_title."</a> ($cnt)</li>";  
        }
        echo "</ul>";
      }
    }  
  }
  echo "</ul>";

The algorithm here is to group by year-month in SQL and loop each month, and get all posts/pages for that month. Use global variable $wpdb to run the SQL statement. Use get_var for single variable SQL statements such as select count(1) and use get_results for multiple rows.

Change the wordpress table name wp_posts if the pre-fix is not wp_. However, you can use $wpdb->posts to get the name of the posts table, which is a better method. Filter the posts/pages by changing the corresponding SQL (where cause) statements accordingly.

Additionally, the facebook likes are counted and included in the brackets as well. To do this, you will need to create another table that has the key pair for article id and likes number. You will need to setup a crontab that runs perhaps once per hour to update the facebook likes (or other social networks) for each posts. This will be covered in another separate tutorial.

–EOF (The Ultimate Computing & Technology Blog) —