Useful Drupal Administrative SQL Queries

Quite often, it is necessary to perform batch operations on a drupal-based site - operations on multiple users, or multiple nodes, for example, and the administrative interfaces don't support the desired operation.

I've put together a list of my favorite queries on this page. I'll update these as time permits.

Get a list of users who have not created any content:

SELECT u.uid FROM {users} u WHERE u.uid NOT IN (SELECT DISTINCT n.uid FROM {node} n) AND u.uid > 1 ORDER BY uid;

Get a list of nodes containing PHP content in body or teaser:

SELECT * FROM node_revisions n WHERE body LIKE '<?php%' OR teaser LIKE '<?php%';

Get a list of nodes with PHP input format (assumes that PHP input format is == 2, which is the default for Drupal 4.7):

SELECT * FROM node_revisions n WHERE format=2; 

Get a list of nodes with PHP content in body or teaser, AND having the PHP input filter (assumes that PHP input format is == 2, as it is by default in most Drupal installs:

SELECT * FROM node_revisions n WHERE (body LIKE '<?php%' OR teaser LIKE '<?php%') AND format=2; 

Want to see visitor paths thru your site? This query will show you a list of sessions grouped by user session, in chronological order:

SELECT * FROM accesslog a ORDER BY sid, aid;

Want to see your cache loading and node count?

SELECT
  (SELECT COUNT(DISTINCT(cid)) FROM cache) AS cached_items,
  (SELECT COUNT(DISTINCT(nid)) FROM node) AS nodes;

How to unpublish all content created by a given user id (using drupal's db_query() API)

$uid=$uid_to_unpublish;

$result = db_query('UPDATE {node} SET status=0 WHERE uid=%d', $uid);

Get a list of filters enabled for a particular role:

$rid = 1;  // from the role table - rid==1 for anonymous, ==2 for authenticated user, etc.
$result = db_query('SELECT * FROM {filter_formats} WHERE roles LIKE "%%%d,%%",$rid); 

Want to get a list of external referrers to your site, sorted by occurance?

SELECT COUNT(url) AS dupecount, url FROM {accesslog} WHERE NOT (url LIKE '%example.com%') GROUP BY url HAVING (dupecount>1) ORDER BY dupecount DESC;
(Replace example.com with your domain name)