How to do a MATCH() AGAINST() Fulltext search in the 'where conditions' box of a read database function?

Question

Based on my recent post concerning how to do a Soundex search I thought I had this all figured out. Apparently my technique of using a function to supply the criteria values is not the answer in the case of trying to run a MATCH query. Everything I have tried creates a MySQL syntax error when the search is activated. I was able to successfully test my query using PHPMyAdmin and it worked as expected.

First, I tried the obvious, but it didn't work -

MATCH(OS.name)/AGAINST:('John')

Of course this didn't work either -

MATCH(OS.name)/AGAINST:{data:keywords}

This attempt used a function that returned the properly formatted value for the AGAINST() portion of the query. It returned - ('criteria') - but still didn't work -

MATCH(OS.name)/AGAINST:{fn:show_search_crit}

Any ideas on how to get this to work are greatly appreciated!

Thanks,

John

skittle · Accepted Answer

It looks to me like it is not possible to use certain MYSQL where conditions in a read database function. For example, the MATCH() AGAINST() where statement simply will not work in a read database function based on how the the conditions are parsed by CC.

However, I found a way around this limitation!

The first step is to create a PHP function that performs the desired MYSQL query and returns the results as a variable. Here is my PHP function which is called 'match_against_name_search':

$db=JFactory::getDBO();
$kw = "'". $this->data['keywords'] . "'";

$query = "
SELECT OS.name, OS.type, OS.title, OS.summary, OS.program FROM `jos_chronoforms_data_opensan` AS OS
WHERE MATCH(OS.name) AGAINST($kw)
LIMIT 10
";
$db->setQuery($query);
$db->query();
$val = $db->loadAssocList();
return $val;

There is an important detail to notice here! You must use loadAssocList() to create your array of results! Using loadRowList() will not generate a properly formatted array for CC.

Next, I created a Table view and named it 'php_built_search'. In the data provider field you need to reference the appropriate PHP function. In my case I entered:

{var:match_against_name_search}

Then, in the Columns List field I entered:

name:Name
type:Type
title:Title
summary:Summary
programs:Programs

Finally, in the Events section I entered a reference to my Table view by entering:

{view:php_built_search}

And that's it! It works great and I now have total control over the SQL queries that I need to use in CC!

I hope this helps someone!

John