By Aar0n on

Hi drupalers...

Does anyone know how I can check whether my db_query returns no results or NULL..

I have looked at the db_query api but it only returns "a database query result resource, or FALSE if the query was not executed correctly".

Any help would be much appreciated!

⋅ Categories: Drupal 5.x

Comments

wonder95’s picture

wonder95 commented

All you have to do is check to see if it is valid. Here's how I do it:

$sql  "SELECT * FROM node WHERE type = %s";
$result = db_query($sql,'page');
if ($result) {
  run this code (result returned)
} else {
  run some other code (no result returned)
}
Aar0n’s picture

Aar0n commented

i tried this but it seemed as though it would always return true (result returned) even if the query returned empty.

I guess this is right though because the result returned is nothing ><

gollyg’s picture

gollyg commented

if the query is returning true, then you can check the number of results using 

db_num_rows($result);

An empty result set will still return true, as there was no problem executing the query. So the test could be 

if(!$result) {
  // problem with query
}
elseif (db_num_rows($result)) {
  // results returned
}
else {
  // no results, but good query
}
Aar0n’s picture

Aar0n commented

that works cheers.

I was hoping for a more elegant solution that would involve something along these lines

if (empty($result) { }

but this will do me fine, just a lot more lines of code = )

cheers gollyg

melezhik’s picture

melezhik commented

Thanks!

Your solution with elseif (db_num_rows($result)) { works really fine!
It helped me in the best way!

alexfisher’s picture

alexfisher commented

Perfect, db_num_rows was exactly what I was looking for. Thanks.

mccarthyr’s picture

mccarthyr commented

Hi

I needed to do the same thing, but I am using Drupal 6 and the use of db_num_rows() does not work on version 6 but you can use db_result() to to a check if there is a row (which it will return then) or else if will return FALSE. The only thing with using this which some people do not like it that is requires 2 hits to the database, one for your actual query and another to check if a row was returned.

You can get around this by just checking the variable that you are assigning values to that come from db_fetch_object() and this should work fine for both your version 5 and 6.

E.g.
$query = "SELECT SOME STUFF FROM YOUR DATABASE";
$query_result = db_query( $query );

// If there is nothing db_fetch_object() returns FALSE anyway
while ( $myDB_stuff = db_fetch_object( $query_result ) )
{
$content .= $myDB_stuff -> myDB_field;
}
if ( !$content )
{
return 'my nothing to display message...';
}
else
{
return $content;
}

If you don't mind the 2 hits to the database then the above could be written as follows:

$query = "SELECT SOME STUFF FROM YOUR DATABASE";
$query_result = db_query( $query );

if ( !db_result($query_result) ) // CHECKING FOR EMPTY RESULT SET...
{
return "my nothing to display message...";
}

while ( $myDB_stuff = db_fetch_object( $query_result ) )
{
$content .= $myDB_stuff -> myDB_field;
}
return $content;

Hope that helps.

Richard

gollyg’s picture

gollyg commented

I must say i am surprised that this function has been removed!

If you are using mysql you could use the native php function to count the rows:

mysql_num_rows($result)

to retrieve the number of rows.

This would plug in to the earlier code in place of db_num_rows() and avoid the second query, but tie your code to a specific db implementation.

Zero4ffect’s picture

Zero4ffect commented

To get the row count from a db_query result use something like:

$result = db_query($query);
if (db_affected_rows($result) > 0){
  // do something here
}