A PHP/MySQL script to query the Drupal comments table

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

  // 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]);
    # use this next line for debugging, as needed

  // free up the database resources


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:

  • How to create a simple PHP class.
  • How to connect a PHP script to a MySQL database.
  • How to specifically query the Drupal comments database table.
  • One way to print the data you get from your PHP database query.

If you have any questions, or suggestions on improving this code, just use our Comments section below.