best way to find out number or rows returned

By jng12 on 8 Nov 2008 at 23:33 UTC

I ran a db_fetch_object on an SQL query. but now I want to know the number of rows I got before doing any processing per row.

what is the best way to do that? what drupal function is there that does this (if any)?

thanks,

Comments

Easy!

budda

English

Cheshire

commented 8 November 2008 at 23:51

http://api.drupal.org/api/function/db_num_rows/5 and http://api.drupal.org/api/function/db_affected_rows/5 will be what you're after sir :-)

--
Ixis (UK): Drupal consultancy, Drupal hosting.

--
Ixis (UK): Drupal support, Drupal hosting.

perfect!

jng12 commented 8 November 2008 at 23:56

thanks!

db_affected_rows worked great!

db_num_rows is gone on version 6 so I really didn't have a choice among the two. hehe. THANKS!

Sorry, didn't notice the

budda

English

Cheshire

commented 8 November 2008 at 23:58

Sorry, didn't notice the Drupal 6.x tag in the top right corner of your post :)

--
Ixis (UK): Drupal consultancy, Drupal hosting.

--
Ixis (UK): Drupal support, Drupal hosting.

db_result()

j.somers commented 9 November 2008 at 19:25

The most common way to get a number in D6 is something like:

$count = db_result(db_query("SELECT COUNT(*) FROM {users}"));

Iirc, the reason num_rows() was removed was because it might caused portability and abstraction issues.

I figured I could do this,

jng12 commented 10 November 2008 at 18:08

I figured I could do this, but because I already have a query returning the rows, wouldn't that be just doubling the query unnecessarily?

Hi j.somers Can you please

dvkd commented 24 April 2009 at 07:12

Hi j.somers

Can you please tell me how to count number of rows with respect to specific column?

I wrote like this but it didnt work :

$count = db_result(db_query("SELECT COUNT(xyz) FROM {mytable} WHERE pageno=%d",$nid));

Is there any other way?

no num_rows() in D6?

fuzzy commented 13 August 2009 at 14:08

*** for D6 ***
so if you're just SELECTing, you can't get the number of rows that resouce contains? You HAVE to do another query (with count())?

I just do this: $result =

lefos commented 8 April 2010 at 02:51

I just do this:

$result = db_query($sql);
$count = count($result);

PHP will count how many sections are in the array(non_recursive, you can set recursive if you need it, but you don't for this)

http://php.net/manual/en/function.count.php

db_query() is supposed to

thomas.rambaud commented 8 April 2010 at 11:26

db_query() is supposed to return a resource, not an array.

Did you mean this ?

$result = db_query($sql);
$count = count(db_fetch_array($result));

But this returns the number of fields the query returns !

The best way is using db_num_rows/mysql_num_rows or db_result.

Drupal 7

Mohal commented 16 February 2012 at 11:15

Hi!

What if we are developing in Drupal 7?

db_result, db_num_rows & likes are not available in Drupal 7.

Thanx in advance!

Regards!

Drupal 7 Solution!

Mohal commented 25 February 2012 at 09:38

Hi!

First of all Drupal must define a function similar to "mysql_num_rows," secondly if someone is looking to do this in Drupal 7 then the easiest method is to execute same query twice.

E.g.

$num_rows = db_query ("SELECT COUNT(`id`) FROM `table-name`")->fetchField (); 

/* $num_rows is self defined variable; this query will return the number of rows returned because we are counting the "id" field here, which is supposed to be the primary key for this table. It is optional, you can count any field. The result of this query will be a single field because we are using "fetchField." */

//this is also a custom condition. Here the program will skip the "if" block if "$num_rows" is not equal to "1."
if ($num_rows == 1)
{
$result = db_query ("SELECT * FROM `table-name`");

/* This query will return your desired result set. Keep in mind that both queries should be same except the "COUNT", "fetchField", & the fields you are getting.*/
}
else
{
// You can perform anything else in this block.
}

If there is still some kind of confusion then you can ask from me. Drupal is very difficult and the problem is is that there are no easy examples given. From my experience if you want to learn Drupal then observe the default functions of Drupal; how they have implemented the stuff you want to implement.

Regards!