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
<?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:

  • 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.

Permalink

Why aren't you using Drupal's database abstraction layer (http://api.drupal.org/api/group/database/6)?

Add new comment

The content of this field is kept private and will not be shown publicly.

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.