db_query not returning anything (or empty) - drupal 7

Question

I am trying to perform a db_query but am unable to do so.

The query I am trying to perform is very simple, but to make things even simpler, the following query does not even work for me:

$result = db_query('SELECT name FROM users WHERE uid = 1');

and if I do:

echo (string)$result->name;

nothing is displayed.

Does anyone know what could be causing this?

Edit - Updated with new code:

define('DRUPAL_ROOT', getcwd());
$_SERVER['REMOTE_ADDR'] = "localhost";
require_once DRUPAL_ROOT . '/includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

$result = db_query('SELECT OFFICE_ID FROM {OFFICE}');
$counter = 0;
foreach($result as $record) {
  $counter = $counter + 1;
  print $record->office_id;
}
print $counter;

Nothing will be printed, but at the end of the loop, the number of loops performed is the same as the number of rows returned from a direct query on the DB itself.

Answer 1

The return from db_query() is a resource that you can iterate over, it's never a single value.

The way to get a single field from a query is with the fetchField() method:

$result = db_query('SELECT name FROM {users} WHERE uid = 1')->fetchField();

If you've got more than one result then you can iterate over it like so:

foreach ($result as $row) {
  // Do something with $row...
}

or, you can make use of one of the helper methods to get data directly from the query into a local variable. These include:

• fetchObject()

  Fetches the next row and returns it as an object.

• fetchAllAssoc()

  Returns the result set as an associative array keyed by the given field.

• fetchAllKeyed()

  Returns the entire result set as a single associative array.

• fetchAssoc()

  Fetches the next row and returns it as an associative array.

• fetchCol()

  Returns an entire single column of a result set as an indexed array.

• fetchField()

  Returns a single field from the next record of a result set.

Answer 2

The answer to my problem is very weird and I have no idea why. But to solve it, all I had to do was change the query from using "single-quotes" to using "double-quotes" -

I used:

$result = db_query("SELECT OFFICE_ID FROM {OFFICE}");

Instead of:

$result = db_query('SELECT OFFICE_ID FROM {OFFICE}');

I hope someone could help me in finding the cause.

Answer 3

You need to execute the query. Try:

$result = db_query('SELECT office_id FROM {office}')->fetchAll();
foreach($result as $record) {
  print $record->office_id;
}

Also, are your table and column names really all uppercase?

Answer 4

The comparison table at PHP Everywhere has a row, "Common string constants," which states that PHP string constants with codes in them are not evaluated if they are in single quotes, but only if in double quotes. I wonder if db_query needs to do some evaluation of the SQL string you passed to it that cannot take place if double quotes are used. If that's the case, it could explain your situation.

If that is the issue, then the proper answer to your question would be a link to the page of the PHP manual that explains it.