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.


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);
| improve this answer | |
  • mysql_num_rows ? Yes I saw something like in internet too. This will do. This is great ! – AgA Dec 16 '11 at 17:07
  • mysql_num_rows is deprecated in php 5.5.0 and up, so we'll need to use the available alternatives in the future. – Scott Lahteine Apr 25 '14 at 20:18
  • and db_result is deprecated in Drupal 7 – wranvaud Jan 23 '17 at 21:30
  • @Drilix - If look at the question it is tagged with drupal 6 not 7. – Cyclonecode Jan 24 '17 at 5:07
$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.

| improve this answer | |

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
| improve this answer | |
  • 1
    Looping is all I want to avoid. I also want to avoid another query.. – AgA Dec 16 '11 at 17:01
  • If so you should have specify this in your question in order to avoid being disappointed by answers and downvoting them ;) – tostinni Dec 16 '11 at 17:51

For Drupal 7 you can use

$result = db_query($query);
| improve this answer | |

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.