How do I get only one result using db_query()

Question

I am transitioning from 'old school' PHP MySQL methods of using mysql_fetch_array(), etc. and I am trying to be more Drupally by using the Database API in my modules.

I simply want to return and print out a value. For example:

$query = db_query("SELECT zip FROM {zipcodes} WHERE city = :city LIMIT 1", array(":city" => $city));

I know the value is there, I can access and print it by using traditional methods outside the database API.

print $query->zip is not working.

The API documentation is as clear as mud.

Can someone tell me the correct way to access these values?

Is there a good tutorial anyone could recommend as well?

Answer 1

If you wish to fetch only one result you could use fetchField with db_query to fetch the result, e.g.:

$zip_code = db_query("SELECT zip from {zipcodes} WHERE city = :city LIMIT 1", array(":city" => $city))->fetchField();

You could also fetch the values retrieved from the query's returned result source using options like fetchObject() similar to methods of conventional PHP (mysql_fetch_object) coding like using and get results.

Answer 2

Here's how to use the Database API in Drupal 7 without writing a MySQL query:

$query = db_select('zipcodes', 'z')
  ->fields('z', array('zip'))
  ->condition('z.city', $city)
  ->range(0, 1)
  ->execute();
$result = $query->fetchObject();
print $result->zip;

Answer 3

You have to loop your $query, you can't suppose you only have one result with the above given query.

foreach ($query as $row) {
  print $row->zip;
}

If you know you only have one result, you could call fetchObject on your query ->

$query = db_query("select zip from {zipcodes} where city = :city limit 1", array(":city" => $city))->fetchObject();

print $query->zip should then give you what you want.

Answer 4

I know this is old, but you can and should do:

$zip_code = db_select('zipcodes', 'z')
    ->fields('z', array(
        'zip'
    ))
    ->condition('city', $city)
    ->range(0, 1)
    ->execute()
    ->fetchField();

Answer 5

I would do

$row = (object)db_query('Your SQL here')->fetchAssoc();

if you want exactly one row from result set. Otherwise looping with foreach is the best option, as suggested before.

Answer 6

Well you have to ways to do this properly in Drupa 7:

1. db_select - in Drupal there is a function that return an object that you construct with him the SQL query - https://api.drupal.org/api/drupal/includes%21database%21database.inc/function/db_select/7. You have a method on the object called range. you can use it.

2. Entity field query which is a class that construct a SQL query upon entites: https://www.drupal.org/node/1343708. This is also have the range method.

Answer 7

Drupal 7

Use This Query For Single result:

$zip_code = db_query("SELECT zip from {zipcodes} WHERE city = :city", array(":city" => $city))->fetchField();

Instead of using this

$zip_code = db_query("SELECT zip from {zipcodes} WHERE city = :city LIMIT 1", array(":city" => $city))->fetchField();

because its generate warning.

Answer 8

Drupal 6

$query will be your result You need to fetch values from it, In your case If it fetches only 1 row and 1 column i.e zip then to get directly

$zip = db_result(db_query("YOUR SQL QUERY"));

$query->zip will not work as $query is result set not a loaded object or an array. So this should do

while($row = db_fetch_object($res)){
  $row->zip ; // etc
}

Note: db_fetch_array is another API to fetch values in array format