query API calling db_query result a non-object, but SQL works

Question

I query my database using the following query API code, but Drupal tells me that the result is a non-object. I can't find any reason to it

in what situation does db_query return anything but?

The error:

Fatal error: Call to a member function execute() on a non-object

the code:

for ($counter = 1; $counter <= 12; $counter++) {
  $query[$counter] = db_query("SELECT `foo` FROM `{bar}` WHERE DATE(FROM_UNIXTIME(`thing_$counter`)) = CURRENT_DATE");
  $results = $query['$counter']->execute()
    ->fetchAll();
}

The SQL works and produced results when I ran it in PHPmyAdmin.

The answer in this exchange looked tantalizing:

$date = date_create();
$date_to_test_against =  $date->format('U');
 $query[$counter] = db_query("SELECT `foo` FROM `{bar}` WHERE `thing_$counter` = $date_to_test_against");

however that produced the same result, calling execute on a non-object.

I know I don't have enough experience with this API yet. Can someone please point me to what I'm missing?

Answer 1

First, in the third line of your first snippet, you're using $query['$counter']. Remove the quotes.

Second, a db_query return value is a query already executed, so you want $results = $query[$counter]->fetchAll();

Answer 2

In the case of db_query, what you get back from it is a resource (it is just a wrapper around php's mysql_query.

But the answer depends on if you're in Drupal 6 or 7

Drupal 6:

In order to turn that into an object, you need to run db_fetch_object on the result, and if you have multiple results you'd need to loop through it.

For example:

<?php
    $sql = "SELECT buckles FROM foo WHERE llama = 'cheese'";
    $result = db_query($sql);

    while ($rowObj = db_fetch_object($result)) {
        echo $rowObj->buckles;
    }

Drupal 7

Use the nifty db_select api! The examples on that page pretty good, but basically it builds a query and then returns something that's very similar to a PDO object (see SelectQuery::execute for the following example)

<?php
  $matches = array();
  $query = db_select('table')
     ->fields('table', array('field_name'));

  foreach ($query->execute() as $row) {
     $matches[$row->relation_type] = $row->label;
  }