db_query() is not returning results from a valid query

Question

In short: db_query of a SELECT statement is returning no results. However, running the SELECT statement in phpMyAdmin vs the database returns thousands of results. I'm using Drupal 6.

What I'm trying to do: The site I'm working on gets thousands of spammers hitting it each day and creating accounts. The accounts are worthless to them, because if they do not pay, they don't get to do anything but log in. I want to safely remove these accounts. Therefore, I'm selecting the accounts based on certain criteria and then want to iterate through each and use the Drupal delete user function to remove the user from Drupal. Right now, I'm just trying to print the info that returns so I can see if I get the correct results before moving on to the delete, which is a simple 1 line addition.

Here's the code:

include_once './includes/bootstrap.inc';
include_once './includes/database.mysql-common.inc';

drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

$query = "SELECT u.uid as id, u.name as name, u.mail as email, ur.rid as role, FROM_UNIXTIME(u.created) AS created, FROM_UNIXTIME(unix_timestamp()-1209600) AS deadline FROM {users} as u LEFT JOIN {users_roles} as ur ON u.uid = ur.uid WHERE (ur.rid < 3 OR ur.rid IS NULL) AND u.uid > 350 AND u.created < (unix_timestamp()-2419200) ORDER BY u.uid";

$results = db_query($query);

print $query;
print "<br/>";
print db_num_rows($results);

foreach ($results as $row) {
        print "<br />";
        print "ID: " & $row['id'];
        print "Name: " & $row['name'];
        print "E-Mail: " & $row['email'];
        print "Role: " & $row['role'];
        print "Created: " & $row['created'];
        print "Deadline: " & $row['deadline'];
}

---

For Posterity's (and other people finding this question) sake, here's the final code:

include_once './includes/bootstrap.inc';
include_once './includes/database.mysql-common.inc';

drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

$query = "SELECT u.uid as id, u.name as name, u.mail as email, ur.rid as role, FROM_UNIXTIME(u.created) AS created, FROM_UNIXTIME(unix_timestamp()-1209600) AS deadline FROM {users} as u LEFT JOIN {users_roles} as ur ON u.uid = ur.uid WHERE (ur.rid < 3 OR ur.rid IS NULL) AND u.uid > 350 AND u.created < (unix_timestamp()-2419200) ORDER BY u.uid";

$results = db_query($query);

print "Users To Delete:  " . db_affected_rows($results);

while ($row = db_fetch_array($results)) {
    print "<br />";
    print "Deleting: ";
    print " ID: " . $row['id'];
    print " Name: " . $row['name'];
    print " E-Mail: " . $row['email'];
    print " Role: " . $row['role'];
    print " Created: " . $row['created'];
    print " Deadline: " . $row['deadline'];

//delete the spam user cleanly
user_delete(array(), $row['id']);

    print " Completed.";
}

I setup a CRON job to run this weekly to do the clean-up. There's no reason it can't be run more or less often, though.

Answer 1

The SQL looks fine, but you're using the wrong versions of the DB API at the moment.

For Drupal 6 you want to use syntax similar to the following:

$results = db_query($query);
while ($row = db_fetch_array($results)) {
  print "ID: " . $row['id'];
}

There's some good documentation for the D6 API on this post, it's worth a read.

Forgot to mention...db_num_rows() is a Drupal 5 function which is why the script is failing. There's a discussion on the alternative methods for D6 here (most involving COUNT queries)