Drupal 6: Export all Drupal data to JSON from a Drush script

In this article I’ll share a drush script I wrote to export data from a Drupal site to JSON format. Scripts like this will require customization, but hopefully it will be helpful as a kick start for some. I used it to export users, nodes, comments, taxonomy, and files from a blog site.

<?php

// define data format
DEFINE('EXPORT_DATE_FORMAT', 'Y-m-d H:i:s');

// fetch desired node data
$sql = "select nid from {node} order by nid asc";
$resource = db_query($sql);
$nodes = array();
while($row = db_fetch_object($resource)) {
  $node = node_load($row->nid);
  if (is_object($node)) {
    $nodes[] = $node;
  }
}

// create a container to store all data
$data = new StdClass();

// loop through node objects, collect desired data
$data->nodes = new StdClass();
foreach ($nodes as $nid => $node) {

  $n = new StdClass();

  // basic properties
  $n->nid = $node->nid;
  $n->type = $node->type;
  $n->uid = $node->uid;
  $n->user_name = $node->name;
  $n->status = $node->status;
  $n->created = date(EXPORT_DATE_FORMAT, $node->created);
  $n->changed = date(EXPORT_DATE_FORMAT, $node->changed);
  $n->title = $node->title;
  $n->body = $node->body;
  $n->path = $node->path;

  // cck field [simple example, single value]
  if (!empty($node->field_example_single[0]['value'])) {
    $n->field_example_single = $node->field_example_single[0]['value'];
  }

  // cck field [simple example, multi-value]
  if (!empty($node->field_example_multi)) {
    $n->field_example_multi = array();
    foreach ($node->field_example_multi as $field_data) {
      $n->field_example_multi[] = $field_data['value'];
    }
  }

  // taxonomy
  if (!empty($node->taxonomy)) {
    $n->taxonomy = array();
    foreach ($node->taxonomy as $tid => $object) {
      $n->taxonomy[] = $object->name;
    }
  }

  // files
  if (!empty($node->files)) {
    $n->files = array();
    foreach ($node->files as $fid => $object) {
      $f = new StdClass();
      $f->fid = $fid;
      $f->filename = $object->filename;
      $f->filepath = $object->filepath;
      $f->filemime = $object->filemime;
      $f->filesize = $object->filesize;
      $f->timestamp = date(EXPORT_DATE_FORMAT, $object->timestamp);
      $n->files[] = $f;
    }

  }

  // comments (recursive)
  if ($node->comment_count) {
    $n->comments = get_node_comments_recursive($n->nid);
  }

  // process node type
  if (!isset($data->nodes->{$n->type})) {
    $data->nodes->{$n->type} = array();
  }
  $data->nodes->{$n->type}[$n->nid] = $n;

}

// fetch user object list
$sql = "select uid from {users} order by uid asc";
$resource = db_query($sql);
$users = array();
while($row = db_fetch_object($resource)) {
  $user = user_load($row->uid);
  if (is_object($user)) {
    $users[] = $user;
  }
}

// loop through user objects, collect desired data
$data->users = array();
foreach($users as $user) {

  $u = new StdClass();

  $u->uid = $user->uid;
  $u->name = $user->name;
  $u->pass = $user->pass;
  $u->email = $user->mail;
  $u->created = date(EXPORT_DATE_FORMAT, $user->created);
  $u->status = $user->status;
  $u->picture = $user->picture;
  $u->roles = array_values($user->roles);

  $data->users[$u->uid] = $u;

}

$json = json_encode($data);

file_put_contents('/non/docroot/path/drupal_export.json', $json);

// FUNCTIONS

// recursively fetch comments data
function get_node_comments_recursive($nid, $pid = 0) {

  $sql = "
    select *
    from {comments}
    where nid = %d and pid = %d
    order by thread asc
  ";
  $resource = db_query($sql, $nid, $pid);

  $comments = array();
  while ($row = db_fetch_object($resource)) {

    $c = new StdClass();
    $c->cid = $row->cid;
    $c->pid = $row->pid;
    $c->nid = $row->nid;
    $c->uid = $row->uid;
    $c->subject = $row->subject;
    $c->comment = $row->comment;
    $c->hostname = $row->hostname;
    $c->timestamp = date(EXPORT_DATE_FORMAT, $row->timestamp);
    $c->status = $row->status;
    $c->thread = $row->thread;
    $c->user_name = $row->name;

    $comments[$row->cid] = $c;
  }
  if (empty($comments)) {
    return array();
  }

  foreach ($comments as $key => $value) {
    $children = get_node_comments_recursive($nid, $value->cid);
    if (!empty($children)) {
      $comments[$key]->children = $children;
    }
  }

  return $comments;
}
?>

I put this script outside my Drupal docroot in a scripts directory. I called it via drush like this:

$ cd drupal_docroot
$ drush scr ../scripts/drupal_export.php