4

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.

  • did you try \"%\"? – AjitS Nov 30 '12 at 6:38
  • Does this happen to be against a postgresql db? – Letharion Nov 30 '12 at 8:22
  • @Letharion It happens also with MySQL 5. – kiamlaluno Nov 30 '12 at 9:41
  • % signs usually need to be escaped. Try using "%%" instead – Clive Nov 30 '12 at 10:15
  • @Letharion I'm using MySQL 5.5, Didn't tried with other DB drivers. – Mathankumar Nov 30 '12 at 11:41
3

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");
  • There's a rouge backslash in your second example, I'd imagine that's why it throws an exception ;) – Clive Nov 30 '12 at 12:12
  • I know. :) My point is that escaping the % character, and leaving the \ character still raises an exception. Remind me that \ is also the escape character used from the Markdown markup. ;) (Don't try to write it between `, or it will give you an headache.) – kiamlaluno Nov 30 '12 at 12:17
  • 1
    Gotcha :) Looks like you've just found the same comment editor bug as me...backslashes inside backticks don't get shown! – Clive Nov 30 '12 at 12:21
  • 1
    Yeah, I was getting crazy for writing such an easy thing. I guess \ should work. (In the case you are wondering how I did, it's ``\``.) – kiamlaluno Nov 30 '12 at 12:24

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.