A PHP/MySQL script to query the Drupal comments table

By Alvin Alexander. Last updated: June 4, 2016

PHP/MySQL FAQ: Can you share an example of a PHP script that is used to query a MySQL database and then loop over the SELECT query results?

This weekend I finally fixed a minor/known bug in my PHP/MySQL script that I use to generate the Drupal sitemap for this website. (A PHP script that queries a Drupal MySQL database.) I like to update the sitemap whenever a blog post has changed, and I had all of that working, except I wasn't accounting for changes due to user comments that are approved.

Yesterday I finally got the "user comments" portion of my Drupal sitemap script working, and I thought I'd share the code here that I use to get the comments from my Drupal database.

PHP/MySQL code to read the Drupal comments database table

The PHP/MySQL code I'm sharing here doesn't actually generate the sitemap; it just demonstrates how to create a class named Comment; query the database for all the Drupal comments that have been approved; and then adds those comments to an array named comments. Optionally, you can un-comment the line near the bottom of the script to print the comment information using the PHP printf function.

Here is the source code for my PHP/MySQL/Drupal comments script:

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

  // created by alvin alexander, devdaily.com

  class Comment
  {
    var $cid;      // comment id
    var $nid;      // nid this comment belongs to
    var $time;     // time comment was created or last edited
    var $status;   // 0 = published

    function Comment($cid, $nid, $time, $status)
    {
      $this->cid = $cid;
      $this->nid = $nid;
      $this->time = $time;
      $this->status = $status;
    }

    function print_details()
    {
      printf("%s, %s, %s, %s\n", $this->cid, $this->nid, $this->time, $this->status);
    }
  }

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

  // (1) connect to the database; change these parameters as needed
  $db_host   = 'localhost';
  $db_user   = 'username';
  $db_pass   = 'password';
  $db_dbname = 'drupal';

  $link = mysql_connect($db_host, $db_user, $db_pass);
  if (!$link)
  {
    die('Could not connect: ' . mysql_error());
  }
  mysql_select_db ($db_dbname);

  // (2) create a list of Comment objects from the comments db table.
  //     i get this by nid in ascending order for particular needs i have.
  $q = "select cid, nid, timestamp, status from comments where status = 0 order by nid asc";
  $result = mysql_query($q);

  // create an empty comments array
  $comments = array();
  
  // populate the comments array from the database data
  while ($row = mysql_fetch_array($result, MYSQL_BOTH))
  {
    $c = new Comment($row[0], $row[1], $row[2], $row[3]);
    array_push($comments,$c);
    # use this next line for debugging, as needed
    #$c->print_details();
  }

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

?>

Drupal PHP/MySQL comments script -- discussion

I'm still a long way away from being a competent PHP programmer, but I believe this PHP/MYSQL script is technically okay. I hope that by sharing the source code here, this will be a decent example of: