Forums

Hi!

My problem is an dynamic dropdown which should show the data of two joined multi record loaders in association of another one. 😲

I have one database table with categories named 'db_mailkategorien' and one table with another category named 'db_kategorien' whitch should show in the second dropdown if the first one was changed. The association of this tables where in an third one witch shows the ids of both "mailkat_id" and "kategorie_id".

This was the classic select in mysql:

SELECT a.kategorie_id, kategorie_text
FROM db_kategorien as a
LEFT JOIN db_maildata as b ON a.kategorie_id = b.kategorie_id
LEFT JOIN db_mailkategorien as c ON b.mailkat_id = c.mailkat_id
WHERE c.mailkat_id = VALUE_OF_FIRST_DROPDOWN

But how can i build this with chronoforms?

1. In my form there are two drop downs:

1.1 first dropdown
name/id = mailkategorie
dynamic data= yes
data path = model_Mailkategorien
key = mailkat_id
value = mailkat_name

1.2 second dropdown
name/id = kategorie
dynamic data= yes
data path = model_Kategorien
key = kategorie_id
value = kategorie_text

2. On load event there are:

2.1 first db multi record loader action
Table = db_mailkategorien
Request param = (empty)
Model ID = model_Mailkategorien
Fields = mailkat_id, mailkat_name
Advanced:
Load data = yes
Enable Assoc = no
Join rule = (empty)
Assoc model = (empty)
Group model data = yes

2.2 a dynamic dropdown action
Source Dropdown ID = mail_mailkategorie
Target Dropdown ID = mail_kategorie
Use AJAX = yes
AJAX Event name = ajax
Extra options extension = x

3. On ajax event there are:

3.1 second db multi record loader action
Table = db_kategorien
Request param = (empty)
Model ID = model_Kategorien
Fields = kategorie_id, kategorie_text
Advanced:
Load data = no
Enable Assoc = no
Join rule = model_Kategorien.kategorie_id=model_Maildata.kategorie_id
Assoc model = model_Maildata
Group model data = yes

3.2 third db multi record loader action
Table = db_maildata
Request param = mail_mailkategorie
Model ID = model_Maildata
Fields = mailkat_id
Advanced:
Load data = yes
Enable Assoc = yes
Join rule = (empty)
Assoc model = model_Kategorien
Group model data = no

The first dropdown shows ok! On select an value, the second shows always an loading error or nothing.

I have read this FAQs (1. FAQ, 2. FAQ) but i can't find the right way.

Can someone help? Thanks!

Hi brostalski,

I would probably use a Custom Code action in the form Ajax event rather that trying to join the two DB Saves.

<?php
$db = \JFactory::getDBO();
$query = "
  SELECT a.`kategorie_id`, `kategorie_text`
    FROM `db_kategorien` AS a
    LEFT JOIN `db_maildata` AS b ON a.`kategorie_id` = b.`kategorie_id`
    LEFT JOIN `db_mailkategorien` AS c ON b.`mailkat_id` = c.`mailkat_id`
      WHERE c.`mailkat_id` = '{$form->data['???']}'
";
$db->setQuery($query);
$data = $db->loadAssocList();
. . .
return $data;
?>

You may need to extend this a bit to get the data into the correct format to be used in the second drop-down.

Bob

Thanks Bob, that's the right way!