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.

Note that these are tested under MySQL 4.x and 5.x.

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 Drupal 4.7:

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. This should be
$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)