Why is this query failing?

Question

I'm using CONCAT in a join. It works well in PHPMyAdmin, but the equivalent Drupal query doesn't work.

$query->join('taxonomy_term_data', 'td', 'sbc.tname LIKE CONCAT( td.name,  "%\" ) AND td.vid = sbc.vid');

This query causes the following error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column '%\' in 'on clause'

However, the following query works fine.

$query->join('taxonomy_term_data', 'td', 'sbc.tname LIKE CONCAT( td.name,  \'%\' ) AND td.vid = sbc.vid');

The only difference between the two queries is the string quotes: In the first one I used double quotes to wrap %; in the second one I used single quotes.

PHPMyAdmin gives me the result whether the % is wrapped with single quotes, or double quotes. (Actually, it should.)

I think there is something I'm missing. I'm just curious to know what it is.

Answer 1

I tried executing a query similar to the one you are showing using an desktop application to connect to the MySQL server running on my computer, and I got the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"%\") AND td.vid = n.vid' at line 1

Drupal reported the following error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column '%\' in 'on clause': SELECT n.* FROM {node} n INNER JOIN {taxonomy_term_data} td ON n.title LIKE CONCAT( td.name, "%\" ) AND td.vid = n.vid;

The PDO driver seems throwing the wrong exception.

The error is caused from the fact you are escaping the second quote, and there isn't any other quote character, except the one ending the join condition.

If I recall correctly, you should not use the double quotes inside SQL strings. Rewrite the query as following, and it works.

$query->join('taxonomy_term_data', 'td', "sbc.tname LIKE CONCAT(td.name,  '%') AND td.vid = sbc.vid");

I also tried escaping the % character with another %, as suggested by @Clive, but the following still raised the same exception.

$query->join('taxonomy_term_data', 'td', 'sbc.tname LIKE CONCAT(td.name,  "%%\") AND td.vid = sbc.vid');

The following worked too, but the result doesn't change, compared to the first query I wrote.

$query->join('taxonomy_term_data', 'td', "sbc.tname LIKE CONCAT(td.name,  '%%') AND td.vid = sbc.vid");