1

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.

  • 1
    You seem to have Drupal 5 and Drupal 7 code in there...which version are you using? Also & is a logical AND in PHP, for string concatenation you should use ., e.g. print "ID: " . $row['id']; – Clive Mar 1 '13 at 21:51
  • Using Drupal 6. And thanks for pointing out the &. Been a long time since I've written PHP and this is the first time I've written for Drupal at all. – A.Welch Mar 1 '13 at 21:51
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)

  • Thank you. I'll update to use db_fetch_array. However, as far as I can tell, I still get no results from the db_query(). The db_num_rows print is completely blank. – A.Welch Mar 1 '13 at 22:06
  • Yeah forgot about that one sorry, I updated the answer – Clive Mar 1 '13 at 22:10
  • @A.Welch If you are using Drupal 6 + My SQL you can try db_affected_rows instead of db_num_rows() – GoodSp33d Mar 2 '13 at 7:30
  • I am using Drupal 6 + MySQL. The combination of the two changes you two suggested fixed it. db_num_rows() wasn't returning an error to the php logs, so it didn't even cross my mind that that's what was causing the problem! It works like a charm now. – A.Welch Mar 2 '13 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.