No result from an SQL query

Question

I have a view with a custom PHP field containing the following code.

$storeArray = array();  
$result = db_query("SELECT nid FROM node WHERE type ='emp'");  

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {  
    $storeArray[] =  $row[nid];    
}

if ($storeArray) {
  echo 'not empty';
}

The query doesn't return anything. Why?

Answer 1

You're mixing a couple of different things there :)

The resource returned from db_query() can be iterated over directly, there's no need for an intermediary function. The resource isn't the same as one that would be returned from mysql_query(), as the Drupal database layer wraps around PDO.

This is the standard way to iterate over db_query() results:

$result = db_query("SELECT nid FROM node WHERE type ='emp'");

$storeArray = array();
foreach ($result as $row) { // An object is returned by default
  $storeArray[] = $row->nid;
}

However, the db layer also has a handy shortcut method called fetchCol() that will fetch the results of the query's first column into an array. So you can simplify your code to a single line:

$storeArray = db_query("SELECT nid FROM node WHERE type ='emp'")->fetchCol();