Forums

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