Displays a count of the comments waiting to be approved to the super-admin user, as well as a link to the comment approval queue.

  global $user;

  if ($user->uid == 1) {

  $sql = 'SELECT c.nid, c.cid, c.timestamp, c.status, c.name, c.homepage FROM {comments} c WHERE c.status = 1';
  $result = db_query($sql);

  $num_comments = db_num_rows($result);
  
  return "<div align='center'>
          <p>There are <strong>$num_comments</strong> comment(s) waiting to be 
          <a href='/admin/comment/list/approval'>approved</a></p></div>";
  }

quicker way might be this:

$comment_count = db_result(db_query("SELECT count(*) FROM comments WHERE status=1"));

In Drupal 5.x the link for the approval queue should be <a href='/admin/comment/list/approval'>approved</a>.

Limiting by role:
This will allow you to limit access to the block by role in 4.7 rather than just limiting it to user 1.

global $user;
$output = '';
$approved_roles = array('admin', 'role2', 'role3');
if (is_array($user->roles)) {
  if (count(array_intersect($user->roles, $approved_roles)) > 0) {

  $sql = 'SELECT c.nid, c.cid, c.timestamp, c.status, c.name, c.homepage FROM {comments} c WHERE c.status = 1';
  $result = db_query($sql);

  $num_comments = db_num_rows($result);

  $output .=  "<div align='center'>
          <p>There are <strong>$num_comments</strong> comment(s) waiting to be
          <a href='/admin/comment/list/approval'>approved</a></p></div>";


   'content goes here';
  }
}

return $output;

In Drupal 5.x you can simply select role access when creating the block in the block configuration.