4

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.

9

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.

  • Clive, I just added that, but it is not returning anything. I know for a fact I am on the correct database. – chlong Jun 21 '12 at 17:27
  • Are you positive? I just ran: $result = db_query('SELECT name FROM {users} WHERE uid = 1')->fetchField(); dpm($result); and 'admin' was printed to the messages area as expected – Clive Jun 21 '12 at 17:30
  • Clive, I just tried yours, but dpm() threw an error as a Call to undefined function dpm(). I updated the question to include the fact that I do include Drupal root before the code. – chlong Jun 21 '12 at 17:36
  • Oh dpm() is part of the devel module, you might not have it installed. Try just print $result; – Clive Jun 21 '12 at 17:37
  • Clive, that worked. I will start to build up from there and try to track down the problem. Thanks. This is really a weird problem...and your answers are as informative as always. Thank you. – chlong Jun 21 '12 at 17:41
3

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.

0

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?

0

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.

  • It's nothing to do with PHP, no - it's related to MySQL's ANSI_QUOTES setting. If that's turned off, double quotes are used for string literals. If it's turned on, they're used to enclose identifiers (table names etc). That's the problem you're seeing here, PHP/PDO won't get involved in that decision – Clive May 15 '15 at 14:56
  • @Clive - Thanks Clive. You should post that as an answer and hopefully the asker will select it so people can find it among all the other stuff here. In any case, thanks for explaining. – NewSites May 15 '15 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.