2

This seems to be fairly easy question, but I could not find simple answer to it.

Using db_select when database column happens to be a reserved mysql word (when in this case) results in an error.

Table:

+-----------------------------------+------------+--------+
|               sort_id             |    when    |  user  |
+-----------------------------------+------------+--------+
| 1                                 | 1448270950 |    123 |
| 3                                 | 1448270955 |     12 |
| 50                                | 1448270959 |     45 |
+-----------------------------------+------------+--------+

Standard drupal7 select using db_select:

$query = db_select('my_table', 'd')
    ->fields('d', array('sort_id', 'when', 'user'))
    ->condition('user', $uid)
    ->execute();

BUT column named when is the problem here.

I know I can write the query using plain SQL:

db_query("SELECT sort_id, user, `when` FROM {naughty_table} WHERE user = :user", array(":user" => $user_id));

But there should be a way to solve this using regular db_select?

IMPORTANT:

I have tried to use backticks like this:

$query = db_select('my_table', 'd')
    ->fields('d', array('sort_id', '`when`', 'user'))
    ->condition('user', '123')
    ->condition('`when`', '1448270950')
    ->execute();

But backticking like this does not seem to work whatsoever.

4

You can use where() instead:

$query = db_select('my_table', 'd')
  ->fields('d', array('sort_id', '`when`', 'user'))
  ->condition('user', '123')
  ->where('`when` = :timestamp', array(':timestamp' => 1448160590))
  ->execute();

Produces:

SELECT d.sort_id AS sort_id, d.when AS when, d.user AS user
FROM 
{my_table} d
WHERE  (user = :db_condition_placeholder_0) AND (`when` = :timestamp) 

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.