A Drupal categories block PHP script

A lot of people have written me to ask how I generate the Drupal categories block on this website. There are probably a lot of different ways to generate a Drupal categories block these days, but the way I do it is with the PHP script shown below. (Sorry, I'm too lazy to look to see if there is a new Drupal categories block module/solution; I'm pretty content with what I have.)

I run this PHP script periodically during the day to generate the Drupal categories data (from a Linux crontab entry), in the output XHTML format I want, and then include this in my Drupal "Categories" block.

I run this script only once a day because at this point the information isn't that relevant. It doesn't matter too much to readers whether I have 350 blog posts about Java or 375, the fact is there are a lot of them. And from a performance perspective, I'd much rather run the script below just once and then include that content into my Drupal categories block; that's a much less expensive process than running this query every time a page is requested.

My Drupal categories block PHP script

Without any further ado, here is the source code for my Drupal categories block PHP script:

#!/usr/local/bin/php -q

#
# Name:        make-drupal-categories.php
# Description: A Drupal "make categories" PHP script.
#
# Copyright 2010 Alvin Alexander, http://devdaily.com
# This script is released under the Creative Commons Attribution-ShareAlike 3.0 License.
# See http://creativecommons.org/licenses/by-sa/3.0/ for more license information.
#
<?php

  class Category
  {

    // combined info from term_data and term_node
    var $name;
    var $display_name;
    var $description;
    var $num_posts;

    function Category($name, $num_posts, $description)
    {
      $this->name = $name;
      $this->num_posts = $num_posts;
      $this->description = $description;

      # do a little work to fix category names that have spaces, or where i want to change
      # their display name
      $this->display_name = str_replace('-', ' ', $name);
      if ($this->display_name === "ooa ood") $this->display_name = "ooa/ood";
      if ($this->display_name === "jfc swing") $this->display_name = "swing";
      if ($this->display_name === "linux unix") $this->display_name = "linux/unix";
      if ($this->display_name === "mac os x") $this->display_name = "mac os x";
      if ($this->display_name === "page 1") $this->display_name = "page 1";
      if ($this->display_name === "product review") $this->display_name = "product reviews";
      if ($this->display_name === "software dev") $this->display_name = "software dev";

      if ($this->name === "mac-os-x") $this->name = "osx";
      if ($this->name === "linux-unix") $this->name = "linux";
    }

    function print_rec()
    {
      if ($this->name === "page-1") return;
      if ($this->name === "product-review") return;
      if ($this->name === "software-dev") return;

      printf("  <li class=\"leaf\"><a href=\"/%s\">%s <span class=\"category-count\">(%d)</span></a></li>\n", 
            $this->name, 
            $this->display_name, 
            $this->num_posts);
    }
  }

  function print_header()
  {
    printf("<ul class=\"menu\">\n");
  }

  function print_footer()
  {
    printf("</ul>\n");
  }


  #--------#
  #  MAIN  #
  #--------#

  // (1) connect to the database
  $link = mysql_connect('localhost', 'MYSQL_USERNAME', 'MYSQL_PASSWORD');
  if (!$link) {
    die('Could not connect: ' . mysql_error());
  }
  
  // the name of your drupal database
  mysql_select_db ('drupal'); 

  // (2) create a list of blog objects from the url_alias table
  $query = "select td.name, count(tn.nid) as num_posts " .
          "from term_node as tn, term_data as td " . 
          "where tn.tid in (select tid from term_data where vid=1 order by tid) " . 
          "and tn.tid = td.tid " . 
          "group by tn.tid " . 
          "order by td.name ";
  $result = mysql_query($query);

  $categories = array();
  while ($row = mysql_fetch_array($result, MYSQL_BOTH)) 
  {
    # all i have now are 'name' and 'num_posts'
    $cat = new Category($row[0], $row[1], '');
    array_push($categories,$cat);
  }

  // (X) free up the database resources
  mysql_free_result($result);
  mysql_close($link);

  // print the output
  print_header();
  $count = count($categories);
  for ($i = 0; $i < $count; $i++) 
  {
    $curr_cat = $categories[$i];
    $curr_cat->print_rec();
  }
  print_footer();

?>

Drupal categories block script - discussion

I'm not going to discuss too many of the details here, unless people ask a lot of questions. But in short, I run this script something like this once a day:

make-drupal-categories.php > categories.html

And then I include that HTML output file content into my Drupal categories block, something like this:

<? include "categories.html" ?>

If you haven't used PHP in a Drupal block before, I discuss this in my Custom Drupal PHP block tutorial.

As mentioned, I think including this HTML file on every page request is a much cheaper hit on the system than running that script on every page hit, though there are Drupal caching mechanisms that I don't know at this point.

Drupal categories block script - summary

I hope this Drupal categories block source code and discussion has been helpful. If you have any questions or comments, just use the Comments form below.