How to do a Soundex search in the 'where conditions' box of a read database function

sk

skittle 28 Feb, 2018

I have had no problem testing a variety of different 'Where conditions' in a read database function I defined. However, I cannot see any way to do a Soundex search. It seems that this is because both the source and target data need to have the SOUNDEX() function applied. This is a problem due to the fact that anything to the right of the colon is apparently interpreted as search criteria in the 'Where conditions' function definition.

For example:

OS.name/LIKE:%{data:keywords}% works great!

Debugger shows that %{data:keywords}% is replaced by '%criteria%' as expected.

SOUNDEX(OS.name):SOUNDEX({data:keywords}) does not work.

Debugger shows that SOUNDEX({data:keywords}) is replaced by 'SOUNDEX(criteria)'. I am pretty sure the string 'SOUNDEX(criteria)' doesn't sound like anything in my database!

How can I work around this limitation and execute a SOUNDEX search in a CC read database function?

Thanks,

John

sk

skittle 02 Mar, 2018

Answer

1 Likes

I managed to solve this. In case anyone else needs to know how, here it is:

I used an HTML view and wrote some PHP to grab the search keyword(s) when the search criteria is submitted. Then I used the PHP soundex() function to get the SOUNDEX value of the search criteria.

The code for the HTML view looks like this and is called show_sndx:

<?php
if (!empty($this->data['keywords'])) {
echo soundex($this->data['keywords']);
} 
?>

Then, in the Where Conditions window of the Read DB function I wrote my search criteria:

SOUNDEX(OS.name):{view:show_sndx}

Works perfectly!

Max_admin 02 Mar, 2018

Hi John,

Good, but you better use a PHP function to return the value instead of an HTML view, you would need to remove the PHP tags from the code in that case.

Best regards

Max
ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?

sk

skittle 02 Mar, 2018

Thanks Max!

I just set it up with a PHP function instead. At the bottom of the PHP function dialog box it says '...returned value will be set as var'. However, when I setup the Where conditions in the Read DB function like this:

SOUNDEX(OS.name):{var:show_sndx}

...no data is passed through so the search does not work as expected.

Using the fn: qualifier works though so now I have:

SOUNDEX(OS.name):{fn:show_sndx}

Thanks again!

John

Max_admin 03 Mar, 2018

Hi John,

Yes, the first would work if instead of "echo" you use "return"

Best regards

Max
ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?

Forums

How to do a Soundex search in the 'where conditions' box of a read database function