Counting the number of rows returned from db_query() with the "SELECT" statement

Question

How do I find the total number of rows returned in db_query() for the SELECT statement, or the equivalent of mysql_num_rows()?

I'm using MySQL.

Answer 1

If you look at the db_result() documentation for Drupal 6:

function db_result($result) {
   if ($result && mysql_num_rows($result) > 0) {
    // The mysql_fetch_row function has an optional second parameter $row
    // but that can't be used for compatibility with Oracle, DB2, etc.
    $array = mysql_fetch_row($result);
    return $array[0];
   }
   return FALSE;
}

I also saw that you could do this in Drupal 6:

$num_rows = db_result(
  db_query("SELECT COUNT(*) FROM {node} WHERE type = '%s'", $type->type)
);

It looks like you could simply do:

// Execute your query.
$result = db_query($your_query);
// Use mysql_num_rows() on the result set.
$num_rows = mysql_num_rows($result);

Answer 2

$res = db_query("SELECT title FROM {node} WHERE  status = '%d'",  1);

db_query() returns an object, and you can check the total number of rows using $res->num_rows.

Answer 3

It depends if you needs the number of rows before looping on the result set or not.

If you need it before, you generally make a SELECT COUNT(*) query with the same arguments as your first query and use db_result() to retrieve it.

If you need it after, just put a variable you'll increment during your loop:

$result = db_query("SELECT nid, title FROM {node} WHERE status = 0");
$total_rows = 0;

while ($row = db_fetch_array($result)) {
  //Process your results here

  //Increment your counter
  $total_rows++;
} 

Answer 4

For Drupal 7 you can use

$result = db_query($query);
$result->rowCount();