28

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?

38

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.

  • 1
    some constructive criticism on the downvote would be rather useful – optimusprime619 Feb 22 '12 at 12:23
  • None of the functions or methods you describe are available in Drupal 7. Your example will produce a fatal error. Also you seem to be mixing Drupal 6 and Drupal 7 code, hence the down vote – Clive Feb 22 '12 at 12:56
  • 2
    @Clive oops....blood rush... good to know the reason though..thanks! – optimusprime619 Feb 22 '12 at 14:20
  • 1
    No worries, if you fix up the answer I'll gladly remove the downvote – Clive Feb 22 '12 at 15:16
  • 1
    @Clive done it now... :) – optimusprime619 Feb 22 '12 at 16:45
15

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;
9

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.

  • 4
    Note: Instead of hardcoding a limit, you should use db_query_range(). – Berdir Feb 22 '12 at 9:11
3

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.

2

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();
0

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.

0

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.

-2

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

  • 1
    db_result db_fetch_array and db_fetch_object are for Drupal 6 and earlier. – jenlampton May 19 '14 at 5:26

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.