How to construct a db_query() with complex criteria?

Question

I'm getting into the database API and it's really interesting how it works. The function db_query() makes querying the database very easy. However, I don't quite understand placeholders and the relationship between placeholders and $args parameter.

Example #1, the first comment on the doc page, can be written as: $result = db_query('SELECT n.field1, n.field2, ... FROM {table} n WHERE n.field0 = :field0', array(':field0' => $field0value)); foreach ($result as $record) { // Data are in $record->field1, $record->field2, ... The db_query function has two parameters; the first is an SQL query and the second is an array definition. The two are related by ":field0", which is called a placeholder. The fields and table are also modified by something called "n"; I don't know what that is.

I found that the above code works. However, I also found that it works and yields the same results without the n's, without the placeholder, and without the array: (The function syntax says the default if the array is not provided is simply "array()".) 'SELECT field1, field2, ... FROM table WHERE field0 = ' . $field0value

I tried this because I need to use complex criteria in which it is not clear what to do with the placeholder and array: SELECT * FROM table WHERE concat(if(field1 = "", field2, field1), "value1") = "value2"

I ran this in phpMyAdmin and it works fine. It works by first looking at field1 and if there's something there, it uses that but if it's empty then it takes the value from field2. It then combines that with "value1" and selects records for which the result equals "value2".

So I put that in db_query like so: db_query('SELECT * FROM {table} WHERE concat(if(field1 = "", field2, field1), "value1") = "value2"');

which resulted in the message: PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause': SELECT * FROM {table} WHERE concat(if(field1 = "", field2, field1), "value1") = "value2"; Array ( ) in include_once() (line 87 of ....php).

Drupal does not seem to be complaining about the use of the concat and if functions, so it seems to be capable of handling them. The symbol between the words "column" and "in" above is a pair of single quotes. So it looks like Drupal is looking for a column designation somewhere and not finding one. My guess is it's looking for something in the empty array. Do I need to use one or more placeholders and some form of array construct in order to get this to work?

Thanks for your help.

Answer 1

The answer provided in the comment above did get the code to work, but according to Jaypan in the Drupal forums, using that solution is a security risk. The error was caused by not using placeholders and the associated array argument, and the purpose of those devices is to prevent SQL injection attacks. The solution of reversing the quotation marks was able to stop the error messages, but at the cost of leaving open a security hole in the code. Jaypan gave me the code to implement the complex criteria with placeholders:

<?php
db_query('SELECT * FROM {table} WHERE concat(if(field1 = :empty, field2, field1), :value1) = :value2',
array(':empty' => '', ':value1' => 'value1', ':value2' => 'value2');
?>

I've implemented that and it works. The pattern seems clear enough to be able to adapt it to other complex criteria in the future, although it would be nice if we had some documentation of placeholders and their syntax.

===============

Edit: It turns out there is some documentation of placeholders, although it's not comprehensive and the discussion with Jaypan (linked above) helped to clarify things.

Answer 2

Looks like you problem is within the field1 = "" ... Try to use single quotes and it should work.

Not sure why Clive did not post his comment as "the" answer (maybe he fears downvotes?), which is why I did so now ... So that NewSites has something to consider for "accepting" ...

PS: your new problem should be moved to a new question I think.