Search code examples
mysqlijoomla

SQL fulltext search - Unknown column 'test' in 'where clause'

I´m trying to do a fulltext search in a Joomla system with this query:

SELECT `id`,`user_id`,`article_id`,`body`,`created_time`
FROM `#__user_notes`
WHERE (`user_id` = '660' AND `article_id` IN (23, 24) AND `id` < '385') 
OR (MATCH (body) AGAINST (`test`) AND `id` < '385' AND `user_id` = '660')
ORDER BY id DESC LIMIT 10

This error is thrown:

JDatabaseExceptionExecuting: Unknown column 'test' in 'where clause'

Works fine without the fulltext search line:

OR (MATCH (body) AGAINST (`test`) AND `id` < '385' AND `user_id` = '660')

I´ve set the ENGINE to MYISAM as this answer suggest fulltext query in joomla

Solution

  • You should remove backtick from your query.

    OR (MATCH (body) AGAINST (`test`) AND `id` < '385' AND `user_id` = '660')

    above should be replaced with

    OR (MATCH (body) AGAINST ('test') AND `id` < '385' AND `user_id` = '660')

    Mysql uses backtick for column name but test here is a text that you want to search.

    Because you are using Joomla so in Joomla, instead of using $db->quoteName you should use $db->quote. quoteName is used to escape column name and quote is used for values