1

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?

  • 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. – EmmyS Jul 10 '12 at 18:41
  • See Database abstraction layer, where it is stated that placeholders should not placed between quotation marks. What you have written as comment is the answer for your own question; you should write an answer, and accept it. – kiamlaluno Jul 10 '12 at 19:00
  • Thanks for the link. I inherited Drupal along with my current job, and I've never had time to really get through best practices - I'm just trying to keep my head above water learning what I need as I need it. – EmmyS Jul 10 '12 at 19:19
2

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.

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

foreach ($result as $row) {
  // do something with $row->id;
}
  • I'd already tried that. When it didn't return anything, I switched it out for the print_r() to see what exactly was being returned. It's NOT returning anything I can loop through - see the original post. – EmmyS Jul 10 '12 at 17:36
  • To clarify, the query is in one function, the processing code in another function which calls the speaker_carousel_get_data() function. I just tried moving the processing code into the same function, and although the function is being called (an error log right before the query does print), when I try to loop through results after the db_query line, error logs added there don't happen. I'm not saying they print out with no value; they don't print at all, which means that the query isn't returning anything that can be looped through that way. – EmmyS Jul 10 '12 at 17:42
  • The return value from db_query() implements DatabaseStatementInterface, which in turn implements Traversable...meaning it can be iterated over using foreach. You can loop over anything that implements Traversable so if your debug statements aren't displaying chances are your query is returning no results – Clive Jul 10 '12 at 18:00
  • Thanks, @Clive. But that begs the question - why isn't it returning results when run via the module, but the exact same query does return results when run in phpMyAdmin? What is Drupal doing to the query that makes it not return anything? – EmmyS Jul 10 '12 at 18:10
  • Hmmm I missed that bit, not really sure to be honest. You can dump the results of the query straight into an array using fetchAll() (i.e. db_query('sql here')->fetchAll();), try running that and using print_r to see if you get any data – Clive Jul 10 '12 at 18:13

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.