Posted by tomas.teicher on 

Hi,
is it possible to get number of results with db_select function?

⋅ Categories: Drupal 7.x

Comments

Anonymous’s picture

Anonymous (not verified) commented 

The easiest way would be:

$results = db_select('table')->fields(NULL, array('field'))->execute()->fetchAll();
$count = count($results);
Joel Costa’s picture

Joel Costa commented 
    $result = db_select('table_name', 'table_alias')
            ->fields('table_alias')
            ->execute();
    $num_of_results = $result->rowCount();
tomas.teicher’s picture

tomas.teicher commented 

thank you both,
I think better is rowCount(),
but count() works too
thanks
Tomas

Joel Costa’s picture

Joel Costa commented 

The difference here is:

If you use count() you need to execute fetchAll() first, this means you need to get all data from your database first. So, if your result have 10.000 rows, than you will need to fetch it from your database and load into memory (assign it to a variable).

With rowCount() you don't need to fetch data from database, so it is more efficient.

drnikki’s picture

drnikki commented 

similarly, you can add an expression. See http://drupal.org/node/1266664

itqn2004’s picture

itqn2004 commented 

or

$countnode = db_query("SELECT COUNT(nid) AS count FROM {taxonomy_index} WHERE tid = :tid", array(':tid' => $tid))->fetchField();

http://jobs68.com
Build Drupal website please contact me itqn2004@gmail.com
TanTran

kunal.kursija’s picture

kunal.kursija commented 

countQuery() with dynamic queries is also one of the way to acheive this.

$results = db_select('table')->fields(NULL, array('field'))->countQuery()->execute()->fetchField();

print_r($results);
chrisroditis’s picture

chrisroditis commented 

This is 10x more resource efficient than using ->rowCount() after execute()

A healthy disregard for the impossible.

jeff h’s picture

jeff h commented 

This is the proper way. Be warned though—stupidly it returns the integer count as a string.

_________
http://marmaladesoul.com

Anthony Robertson’s picture

Anthony Robertson commented 

While it wasn't a problem until my table got huge, this method is incredibly faster.

Here are some results obtained from devel query log, based on my table and data:

db_select('table', 'alias')->fields(NULL, array('a_field'))->countQuery()->execute()->fetchField();
7ms

db_select('table', 'alias')->fields('alias')->execute()->rowCount();
4365ms

Jaypan’s picture

Jaypan commented 

Wow, that's a pretty big difference. Nice benchmarking. It definitely makes more sense though - with the faster query you're simply querying the number of rows. With the slower query, you're selecting all the data in the rows, then counting it in PHP after it's been retrieved.

kalaiselvann’s picture

kalaiselvann commented 

Hi

Get count with db_select working fine. In the below code:

$query = db_select('node', 'n')
      ->condition('n.type', 'article', '=')
      ->fields('n', array('nid','title'))
      ->execute();
      $num = $query->rowCount();
      
      
      
      
      来自  https://www.drupal.org/node/1226410