Populate dynamically cascading select list

  • alexandersson
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
  • Posts: 38
  • Thank you received: 0

alexandersson replied the topic: Populate dynamically cascading select list

Yes... the tables in my DB
#220353
  • Posts: 673
  • Karma: 32
  • Thank you received: 66

JulianGracin replied the topic: Populate dynamically cascading select list

Hi,

Can you tell me exactly what's not working? Are you getting some kind of an error?


Regards,
Julian

=========================================
Need to renew Pro Support?
Buy Here!
=========================================
Satisfied?
Consider a membership!
=========================================
Like us on Facebook
=========================================
Support Crosstec and get ALL EXTENSIONS and professional support for just $5
Here!
#220355
  • alexandersson
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
  • Posts: 38
  • Thank you received: 0

alexandersson replied the topic: Populate dynamically cascading select list

Actual situation:
- Section Violazione 1 works perfectly
- Sections Violazione 2/3/4... don't work.
You can see the screenshots attached...



#220357
Attachments:
  • Posts: 673
  • Karma: 32
  • Thank you received: 66

JulianGracin replied the topic: Populate dynamically cascading select list

Hi,

You will need to modify the code in Before Form Pieces. Replace the old code with this one:
$table = '#__violazioni'; // Name of your table in the database

function ff_formatJSON($keys, $values) {
  $out = "";
  if( (count($keys) == count($values)) && count($keys) > 1) {
    
    for ($i = 1; $i < count($values); $i++ ) {
      if ( count($values[$i-1]) !== count($values[$i]) ) {
        $out = "ERROR - the input array sizes don't match!";
        err('omfd an aeeor');
        return $out;
      } 
    }
    
    $out .= "[ ";
    for ($i = 0; $i < count($values[0]); $i++) {
      if ($i !== 0) { $out .= ", "; }
      $out .= "{";
      for ($j = 0; $j < count($keys); $j++) {
        if ($j !== 0) { $out .= ", "; }
        $out .= "\"".$keys[$j]."\": \"".$values[$j][$i]."\"";
      }
      $out .= "}";
    } 
    $out .= " ]";

  } else {

    if (count($values) > 1) { $out .= "[ "; }
    for ($i = 0; $i < count($values); $i++) {
      if ($i !== 0) { $out .= ", "; }
      $out .= "{\"".$keys."\": \"".$values[$i]."\"}";
    } 
    if (count($values) > 1) { $out .= " ]"; }

  }
  return $out;
}

function createListLine($input) {
 $output = "1;;\n";
 for ($i = 0; $i < count($input); $i++) {
      $output .= "0;".$input[$i].";".$input[$i]."\n";
 }
 return $output;
}

 function ff_setSelectList($name, $value) {
  global $ff_processor;
  for ($r = 0; $r < $ff_processor->rowcount; $r++) {
     $row =& $ff_processor->rows[$r];
     if ($row->name==$name)
     $row->data2 = $value;
     unset($row);
  }
}

function ff_loadSelectListFromDatabase($tableName, $columnName, $elementName, $disFlag = false) {

   $distinct = '';
   if ($disFlag) {
      $distinct = 'Distinct';
   }

   $db = JFactory::getDBO();
   $db->setQuery("Select $distinct $columnName From $tableName Order by  $columnName ASC ");
   $result = $db->loadColumn();

   ff_setSelectList($elementName, createListLine($result));
}


# Initial list load
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma1', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma2', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma3', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma4', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma5', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma6', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma7', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma8', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma9', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma10', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma11', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma12', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma13', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma14', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma15', true);

# dynamic list load
$elName = JRequest::getVar('elementName');
$condition = JRequest::getVar('condition');
$targetColumn = JRequest::getVar('targetColumn');
$sourceColumn = JRequest::getVar('sourceColumn');

if ($elName !== null) {
   while (@ob_get_level() > 0) { @ob_end_clean(); }

   if ($condition == '') { exit; }

   $db = JFactory::getDBO();
   $db->setQuery("Select Distinct $targetColumn From $table Where $sourceColumn = '$condition'");
   $result = $db->loadColumn();

   echo ff_formatJSON('value', $result);

   exit; 
}

You can notice the long list of the following:
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma1', true);
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma2', true);
...


The reason you lists weren't populating, is because the first lists were being filled by this line of php code. So by calling this function for all 15 lists, all of them will have values.
You will need to call this function for any element you wish to have it's value loaded from the database when the form loads.


Regards,
Julian

=========================================
Need to renew Pro Support?
Buy Here!
=========================================
Satisfied?
Consider a membership!
=========================================
Like us on Facebook
=========================================
Support Crosstec and get ALL EXTENSIONS and professional support for just $5
Here!
#220381
  • alexandersson
  • Topic Author
  • Offline
  • Junior Boarder
  • Junior Boarder
  • Posts: 38
  • Thank you received: 0

alexandersson replied the topic: Populate dynamically cascading select list

Ok I've replaced the "Before Form Piece" and now all 15 sections work perfectly.
But what if I need to obtain same behavior with other select lists in the same form (referenced to another table)?

In my previous post I've written "Another thing... form contains also 2 other sections, each with 2 select list ("provincia" and "comune"). Both are referenced to the same table ("avt3c_comuni")... I would like same behavior"

I've tried to duplicate the "Before Form Piece", changing references to table (avt3c_comuni instead of avt3c_violazioni) and select lists . With two "Before Form Piece" (one after other), form doesn't load.

So I think I've to create one script for both groups of select lists. How can I integrate other table and other select lists in the same script?

You have avt3c_comuni.csv.zip attached to my previous post.

I'm sorry for disturb...
#220445
  • Posts: 673
  • Karma: 32
  • Thank you received: 66

JulianGracin replied the topic: Populate dynamically cascading select list

Hi,

To load the provincia1 and provincia1 lists, you will need to call ff_loadSelectListFromDatabase() function for these two lists.

Let me explain this function first:

function ff_loadSelectListFromDatabase($tableName, $columnName, $elementName, $disFlag = false)

- $tableName: is the name of the table you are pulling data from.
Right now you are calling it like this:

$table = '#__violazioni'; // Name of your table in the database
...
ff_loadSelectListFromDatabase($table, 'Norma', 's_violazionenorma1', true);


But you can replace the $table with '#__you_table_name'. The prefix, #__ is used as a wildcard instead of the 5 letter prefix in your database, something like kdflj_. If you have created the table in your database without that prefix, omit it.

So you can call the function like this:

ff_loadSelectListFromDatabase('#__your_table_name', 'Norma', 's_violazionenorma1', true);

- $columnName: is the name of the Column in the specified table you are setting values to.

- $elementName: is the name of your list element you are populating with the data.

- $disFlag: if set to true, it selects only distinct values. If there are multiple rows in your database with the same values, it selects only 1 row per value. Keep this to true. I Should have probably reversed this :)

To populate your two lists, add the following 2 lines of code:

ff_loadSelectListFromDatabase('#__comuni', 'Regione', 'provincia1', true);
ff_loadSelectListFromDatabase('#__comuni', 'Regione', 'provincia2', true);


Replace the '#__comuni' with the correct table name if I used the wrong one here.

You also need to add Actionscript to both of the provincia elements. Since you are now pulling data from the two different tables, the Actionscript needs to be updated. There will be one extra line added.
The added line is:

tableName: '#__comuni',
- #__comuni: replace this with the correct table name you are getting the data from. You can simply add this line in the existing code, rather that replacing everything. Add this line between the two lines like this:

sourceColumn: 'Provincia',
tableName: '#__comuni', //replace the tableName with the proper one for each element
elementName: element.name.slice(6, element.name.length - 2)

function ff_sel_provincia_action(element, action) {
   switch (action) {
      case 'change':
         jQuery.post('<?php return JUri::root(); ?>/index.php', {
            option: 'com_breezingforms',
            ff_form: ff_processor.form,
            format: 'html',
            condition: element.value,
            targetColumn: 'Comune',
            sourceColumn: 'Provincia',
            tableName: '#__comuni', 
            elementName: element.name.slice(6, element.name.length - 2)
         }, function(data) {
            var list = ff_getElementByName('sel_comune');
            list.empty();
            if (data != '') { 
               var input = JSON.parse(data);
               if (!Array.isArray(input)) {
                  list.options[list.length] = new Option(input.value, input.value, true, false);
               } else {
                  list.options[0] = new Option('', '', true, false);
                  for (var i = 0; i < input.length; i++) {
                     list.options[i + 1] = new Option(input[i].value, input[i].value, true, false);
                  }
               }
            } 
         });
         break;
      default:
         ;
   } // switch
} // ff_sel_regione_action


Now in the Before Form piece, you will need to replace the code under the # dynamic list load comment:
# dynamic list load
$elName = JRequest::getVar('elementName');
$condition = JRequest::getVar('condition');
$targetColumn = JRequest::getVar('targetColumn');
$sourceColumn = JRequest::getVar('sourceColumn');
$tableName = JRequest::getVar('tableName');

if ($elName !== null) {
   while (@ob_get_level() > 0) { @ob_end_clean(); }

   if ($condition == '') { exit; }

   $db = JFactory::getDBO();
   $db->setQuery("Select Distinct $targetColumn From $tableName Where $sourceColumn = '$condition'");
   $result = $db->loadColumn();

   echo ff_formatJSON('value', $result);

   exit; 
}

Try it out and let me know if it produces the results you expected.


Regards,
Julian

=========================================
Need to renew Pro Support?
Buy Here!
=========================================
Satisfied?
Consider a membership!
=========================================
Like us on Facebook
=========================================
Support Crosstec and get ALL EXTENSIONS and professional support for just $5
Here!
#220484
Moderators: ForumSupporttomeperica
Time to create page: 0.291 seconds

New Icon Packs Category!

Crosstec is now offering icon packs.

If you are a paying subscriber, icon packs are automatically added to your account.

Check out our icon packs page!

Live Support Chat Opened!

Join our Discord chat here to receive live support and talk directly to the team!

Summer Sale!

50% discount on all of our extension subscription plans, templates and icon packs!

Get Your Subscription Here

News and Updates

Get informed about new downloads, updates and more in our News and Updates newsletter.

All Extensions Subscription

Get 1 year access to all of our current and future products and 1 year of professional support -- 99 for just 49! (Summer Sale)

No support per domain or website installation limits! Includes all of our current and future Joomla!® extensions, Joomla!® templates for the duration of your membership. This means, by purchasing an All Extensions Subscription you'll have it all covered!

Get it from here

3rd Party Discount - 25% Off

We help you to keep your costs under control. If you are a new member and purchased a form building tool from a different form vendor, then you'll get a 25% discount on our subscription plans.

How to receive the discount:

Send us a quick email to sales@crosstec.org with a proof of purchase (for example a paypal receipt), await payment instructions and enjoy your membership!

Live Support Chat Opened!

Join our Discord chat here to receive live support and talk directly to the team!

Community Reward

Help us to create new extensions and plugins! With only $5 you help us a lot and get unlimited download access to all of our products, professional support and even more. Get your reward now!

Read More Here