db_query not returning expected results - updated

Question

I'm using raw SQL in a custom module (please don't tell me to use the query builder; this is a single static query that will never need to take params.) If I run the query directly in phpMyAdmin, it returns two records. When I run the module, it doesn't display anything.

function speaker_carousel_get_data() {
  error_log("calling get data");
  $query = db_query("SELECT
                    node.title AS node_title,
                    node.nid AS nid,
                    field_speaker_node.title AS field_speaker_node_title,
                    field_speaker_node.nid AS field_speaker_node_nid,
                    'node' AS field_data_field_job_title_node_entity_type,
                    'node' AS field_data_field_company_node_entity_type
                    FROM 
                    {node} node
LEFT JOIN {field_data_field_speaker} field_data_field_speaker ON node.nid = field_data_field_speaker.field_speaker_target_id
LEFT JOIN {node} field_speaker_node ON field_data_field_speaker.entity_id = field_speaker_node.nid
WHERE ((node.status = ':status') AND (node.type = ':type') AND (node.title = ':title'))
ORDER BY node_title ASC", array(':status' => '1', ':type' => 'speaker', ':title' => 'April Carlisle'));
  print "<pre"> .print_r($query). "</pre>";
  return $query;
}

I had expected the print_r() to return an array of results, but instead it gives me this:

DatabaseStatementBase Object
(
    [dbh] => DatabaseConnection_mysql Object
        (
            [shutdownRegistered:protected] => 
            [target:protected] => default
            [key:protected] => default
            [logger:protected] => 
            [transactionLayers:protected] => Array
                (
                )

            [driverClasses:protected] => Array
                (
                    [SelectQuery] => SelectQuery
                    [MergeQuery] => MergeQuery
                    [DatabaseTransaction] => DatabaseTransaction
                    [InsertQuery] => InsertQuery_mysql
                    [DeleteQuery] => DeleteQuery
                    [DatabaseSchema] => DatabaseSchema_mysql
                )

            [statementClass:protected] => DatabaseStatementBase
            [transactionSupport:protected] => 1
            [transactionalDDLSupport:protected] => 
            [temporaryNameIndex:protected] => 0
            [connectionOptions:protected] => Array
                (
                    [database] => expo
                    [username] => myuser
                    [password] => mypassword
                    [host] => localhost
                    [port] => 
                    [driver] => mysql
                    [prefix] => Array
                        (
                            [default] => 
                        )

                )

            [schema:protected] => DatabaseSchema_mysql Object
                (
                    [connection:protected] => DatabaseConnection_mysql Object
 *RECURSION*
                    [placeholder:protected] => 0
                    [defaultSchema:protected] => public
                    [uniqueIdentifier:protected] => 4ffc5cb56dca11.89303530
                )

            [prefixes:protected] => Array
                (
                    [default] => 
                )

            [prefixSearch:protected] => Array
                (
                    [0] => {
                    [1] => }
                )

            [prefixReplace:protected] => Array
                (
                    [0] => 
                    [1] => 
                )

        )

    [queryString] => SELECT
                    node.title AS node_title,
                    node.nid AS nid,
                    field_speaker_node.title AS field_speaker_node_title,
                    field_speaker_node.nid AS field_speaker_node_nid,
                    'node' AS field_data_field_job_title_node_entity_type,
                    'node' AS field_data_field_company_node_entity_type
                    FROM 
                    node node
LEFT JOIN field_data_field_speaker field_data_field_speaker ON node.nid = field_data_field_speaker.field_speaker_target_id
LEFT JOIN node field_speaker_node ON field_data_field_speaker.entity_id = field_speaker_node.nid
WHERE ((node.status = ':status') AND (node.type = ':type') AND (node.title = ':title'))
ORDER BY node_title ASC
)
<pre>1</pre>

What's going on? How do I get the results of the query?

EDITED AFTER SOME EXPERIMENTING

I've narrowed it down to the WHERE clause - if I set the query to the following, it works.

$sql = db_query("SELECT node.title AS node_title,
                  node.nid AS nid,
                  field_speaker_node.title AS field_speaker_node_title,
                    field_speaker_node.nid AS field_speaker_node_nid,
                    'node' AS field_data_field_job_title_node_entity_type,
                    'node' AS field_data_field_company_node_entity_type
                    FROM 
                    node node
LEFT JOIN field_data_field_speaker field_data_field_speaker ON node.nid = field_data_field_speaker.field_speaker_target_id
LEFT JOIN node field_speaker_node ON field_data_field_speaker.entity_id = field_speaker_node.nid
                  ORDER BY node_title ASC")->fetchAll();

I don't know if the issue is with the where clause itself, or with the placeholders. Did I not format it correctly?

Answer 1

I did figure out the issue; the placeholders should not be in quotes (in the query, not the args array.) An example I was working from showed them formatted in single quotes, so that's what I did. I just assumed somehow Drupal was replacing things, but if they're in single quotes they're being passed my mysql as literal strings and obviously there are no results that match.

The Database abstraction layer says:

Note that placeholders should be a "complete" value. For example, when running a LIKE query the SQL wildcard character, %, should be part of the value, not the query itself. Thus, the following is incorrect:

    SELECT nid, title FROM {node} WHERE title LIKE :title%;

It should instead read:

    SELECT nid, title FROM {node} WHERE title LIKE :title;

and the value for :title should include a % as appropriate. Again, note the lack of quotation marks around :title. Because the value is not inserted into the query as one big string but as an explicitly separate value, the database server knows where the query ends and a value begins.

Answer 2

$result = db_query('SELECT id, ...');

foreach ($result as $row) {
  // do something with $row->id;
}