How to make dynamic dbjoin "WHERE clause" controlled by other elements

[margarizaldi]

Hi everybody, I'm new in fabrik and know very limited (or insufficient, perhaps) knowledge in coding. Now I need your help to solve my problem in creating a small tournament registration form.
I have created a form/list called `Players` which a registered user (say, manager) can submit their players along with some details including gender, birthday, grade, etc.

Now I need those submitted players can be registered by each manager in a table called `match_registration`. The form/list contains:

• dropdown element: match_registration___category
• dropdown element: match_registration___class
• dbjoin element: match_registration___player_name

My problem is in the last element (dbjoin), actually I could configure them using standard SQL Where statement e.g.:

where {thistable}.user_id = '{$my->id}'
and {thistable}.gender = 'male'
and {thistable}.grade >= '3'
and {thistable}.grade <= '7'
and {thistable}.birthday >= '2000-11-30'
and {thistable}.birthday <= '2004-12-01'

But I need more "dynamic" filtering (perhaps using JS in dropdown elements) so that when the `category` value changed (e.g.: from 'Junior' to 'Senior'), the Where statement changes accordingly, e.g:

...
and {thistable}.grade >= '8'
and {thistable}.grade <= '10'
and {thistable}.birthday >= '1977-11-30'
and {thistable}.birthday <= '2000-12-01'
...

I'm thinking that this case can be done using JavaScript in 'category' element using onChange etc. but I don't know how to write the code at all
I've searched through this forum but I haven't found the answer, or maybe it's too simple so that no one discussed for it.
I accept any better idea so that it is possible or even easier to implement.

Regards.

 

[cheesegrits]

You might be able to do it with CASE statements in your WHERE, using placeholders from the form you want the values from

where {thistable}.user_id = '{$my->id}'
and {thistable}.gender = 'male'
and (
   CASE
      WHEN '{yourtable___category}' = 'Senior' THEN
         and {thistable}.grade >= '3'
         and {thistable}.grade <= '7'
         and {thistable}.birthday >= '2000-11-30'
         and {thistable}.birthday <= '2004-12-01'
      ELSE
         and {thistable}.grade >= '8'
         and {thistable}.grade <= '10'
         and {thistable}.birthday >= '1977-11-30'
         and {thistable}.birthday <= '2000-12-01'
      END
)

... and enable AJAX update.

I'm not sure how your "age" calculation (birthday) needs to work, but it's usually easier to use something like TIMESTAMPDIFF(YEAR,{thistable}.birthday,CURDATE()) to get an age, than to use static dates.

-- hugh

 

[margarizaldi]

Thanks for your suggestion, but I get an error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASE WHEN '' = 'Junior' THEN and `players`.grade >= '3' ' at line 5 SQL=SELECT DISTINCT(`players`.`id`) AS value, `player_name` AS text FROM `players` AS `players` WHERE `players`.user_id = '519' and { CASE WHEN '' = 'Junior' THEN and `players`.grade >= '3' and `players`.grade <= '7' and `players`.birthday >= '2000-11-30' and `players`.birthday <= '2004-12-01' WHEN '' = 'Adult' THEN and `players`.grade >= '3' and `players`.grade <= '7' and `players`.birthday >= '1977-11-30' and `players`.birthday <= '2000-12-01' ELSE and `players`.grade >= '8' and `players`.grade <= '10' and `players`.birthday >= '1967-11-30' and `players`.birthday <= '2000-12-01' END } ORDER BY text ASC

It seems that the CASE doesn't recognize the observed element. What do you think? Did I make a mistake??

 

[cheesegrits]

You probably need to fiddle with the syntax a little. Probably doesn't need the first 'and' in the THEN and ELSE blocks. Also, don't use curly braces around the case construct, use regular parens.

Sent from my HTC 10 using Tapatalk

 

[margarizaldi]

Well I followed your two suggestions and it works perfectly. You're the king Hugh! Thanks much.

 

[cheesegrits]

Thank you. Feel free to express your appreciation in the form of a support subscription.

-- hugh

 

[margarizaldi]

Well I'll surely consider for that...