Buy Now
Sign in

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

skittle , March 03 at 21:16
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 -
Of course this didn't work either -
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 -
Any ideas on how to get this to work are greatly appreciated!
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':
​$kw = "'". $this->data['keywords'] . "'";​
​$query = "​
​SELECT, OS.type, OS.title, OS.summary, OS.program FROM `jos_chronoforms_data_opensan` AS OS​
​LIMIT 10​
​$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:
Then, in the Columns List field I entered:
Finally, in the Events section I entered a reference to my Table view by entering:
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!
Hi John,
Great, thank you for sharing your solution!
Just one note, you have to escape the $kw value otherwise your code will be vulnerable to hacking.
Best regards
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
Thanks Max!
Kind regards,