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)