By shreky on

hi, I am trying to get the count value of results from my database query. I do not want to use SELECT COUNT(id) since I am not just getting the count value but also results from the db. I tried using mysql_num_rows but that did nothing.

My test query as an example

$result = db_query('SELECT id, name, title FROM table_name ... ..... ....

In the above query, how would I get a count of the total rows in the database? Any help would be greatly appreciated.

⋅ Categories: Drupal 6.x

Comments

scrypter’s picture

scrypter commented 
$db = array();
$result = db_query('SELECT id, name, title FROM table_name ... ..... ....
 while ($rec = db_fetch_object($result)) {
      $db[$rec->id] = $rec->name;
}
$num = count($db);

Not total rows in the database, but number of rows in the query.

www.purpleoar.co.nz/scryptik - Javascript editor with syntax error checking
www.purpleoar.co.nz - Web development, Drupal consultancy

www.purpleoar.co.nz/scryptik - Javascript editor with syntax error checking
www.purpleoar.co.nz - Web development, Drupal consultancy

criznach’s picture

criznach commented

Determine how many result rows were found by the preceding query.

http://api.drupal.org/api/function/db_num_rows/5

http://www.trailheadinteractive.com

mooffie’s picture

mooffie commented

db_num_rows() was dropped in D6, for portability sake. So now we should do 'SELECT COUNT...'; or we could count via PHP, as 'scrypter' demonstrated; or we could rethink our logic.

darrenlambert’s picture

darrenlambert commented

I think an oversight has been made in D6. I need to know the number of records in the returned result set before I iterate through them. Using a 'select count(*)' is nonsense, yet another query to fire.
If we could reset the result set, and iterate it again we could count the records before iterating them.

kevinquillen’s picture

kevinquillen commented

^ agree. Why was this dropped?

===========
read my thoughts

lorinpda’s picture

lorinpda commented

Hi,
A serious answer to your question would be to ask you do an execution plan analysis (along with analyzing the network, disk partitions, etc.). In other words, DBA's (database administrators) perform real and critical business functions. The folks who write database engines provide performance tuning tools for a reason!! You analyze your situation before asserting a solution. DBA's spend time analyzing and designing and programming database performance for very good reasons.

If your database had a million records in the "node" table. I would guess that your web server doesn't hold enough memory to store 1 million node records. I would further guess that performing a select count(nid) from {node}, is a relatively small price to pay versus scrolling though a million records. That's probably why the folks who designed SQL, created the "count" operation!

Generally, you want to perform 2 queries:
1) select count(KEY FIELD)
2) go an get the actual data (i.e. select field1, field2, .....)

However, always consider timing the executions if you are concerned about performance. Never just guess.

Please note! Usually selecting a count on one field is more likely to be faster the doing a count on all fields. The underlying database engine is likely to use an index to perform the count. The implications in the comments above, that the database performs a full table scan, just to get the count results, is highly unlikely. However, most database engines will provide you with tools to time the execution and then tune.

There might be some cases where skipping the select count step is warranted. For example, you might have a small table (5- 10 record) that defines system definitions (e.g. allowed colors). In that case, a small definition table might be quicker to just transport and then count (i.e. just select the data and then scroll the results). Again, that's why you do an analysis before you assert a solution :)

Good luck. Hope that helps.