How do I get 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? Thanks!

Answer 1

hi if you wish to fetch only one result you could use fetchField with db_query to fetch the result (ex)

$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

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 3

$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