Forums

Hello,

I would like to create a form similar to the “demo-dynamic-dropdown” already existing in the “Demos” section of the Chronoforms_V5.0.2 component, with the following major difference:

I would like the dropdown “league” and “club” being dynamically populated with values from the database and not by fixed values written in a script (such as the custom code in the On load_clubs event). I need firstly populate the dropdown “league” and based on its value selected by the user, populate the values of the dropdown “club”, but with values stored in database tables.

Could you please explain how to do that?

Thank you very much in advance
Best Regards/Jean Pierre

Hello everybody,

I understand that I didn’t have any reply to my "double dropdown" question because it was very general.

I will try to be as more specific as I can🙂

I have tried to find a solution to my request by reading a number of posts in this forum and I successfully managed to populate dynamically just my first dropdown element.

So my problem now is to populate the second dropdown with values depending on the value selected on the first dropdown.

I have found some posts and I have tried to understand them, but I am completely lost (I am not experienced with php and ajax).

I have let’s say 3 joomla database tables:
• #_category_table (with “id” as primary key)
• #_product_table (with “id” as primary key)
• #_product_xref_table (with “id” as primary key, and Category_ID and Product_ID as foreign keys. So, I can through this table find any product belonging to each category)

The first dropdown is populated dynamically with all categories from the #_category_table joomla database table, with the following php code:

<?php
$db =& JFactory::getDBO();
$query = "
SELECT `id`, `title`
FROM `#_category_table`";
$db->setQuery($query);
$form->data['CATEGORIES'] = $db->loadAssocList();
?>

Now, I am trying to populate the second dropdown element with all products per category, from #_product_table, using the following code, (that I have put in a custom code in a new event that I called “ajax”):
<?php
$db =& JFactory::getDBO();
$query = "
SELECT `name`
FROM `#_product_table`
WHERE id IN (
SELECT Product_ID
FROM `#_product_xref_table`
WHERE Category_ID = $form->data['CATEGORIES'])
";
$db->setQuery($query);
$form->data['PRODUCTS'] = $db->loadAssocList();
?>

Unfortunately, I am sure that I commit a serious mistake since my second dropdown does not work at all.

I would be very grateful if you could provide me any help to my problem

Very best regards
Jean Pierre

Hello,

I have succesfully managed to make a test in order to check if my second dropdown can be populated with my code. I have used the following code (where XYZ is just one fixed value, not linked with the selected value of the first dropdown):

<?php
$db =& JFactory::getDBO();
$query = "
SELECT `name`
FROM `#_product_table`
WHERE id IN (
SELECT Product_ID
FROM `#_product_xref_table`
WHERE Category_ID = XYZ)
";
$db->setQuery($query);
$form->data['PRODUCTS'] = $db->loadAssocList();

echo json_encode($form->data['PRODUCTS']);
?>

So with XYZ as fixed value the second dropdown is populated. The problem now is how to link XYZ with the values of $form->data['CATEGORIES'] selected in the first dropdown element.

I will appreciate your help
Best Regards
Jean Pierre

Hi Jean Pierre,

Please check the section at the end of this FAQ for code to use in your Ajax event. The FAQ assumes that you will use a DB Read action to load the data but the database query you have will work as well.

Bob

Hi Bob,

Thank you very much for your reply.

I have already seen this FAQ as well as the example in http://www.chronoengine.com/forums/posts/t89424/p314862.html#p314862, but I am completely lost (since I have no experience in ajax, javascript and php).

Based on this FAQ, I have tried to correct my sql select statement and my php script, inside “On ajax” event, in order to populate dynamically my second dropdown, as below:

<?php
$db =& JFactory::getDBO();
$sql = "
SELECT	 #_category_table.id
		, #_product_table.name
FROM		#_category_table
		, #_product_table
		, #_product_xref_table
WHERE	#_category_table.id = #_product_xref_table. Category_ID
AND		#_product_table.id = #_product_xref_table. Product_ID
";
$db->setQuery($sql);
$form->data['PRODUCTS'] = $db->loadAssocList();
$results = array();
// the next line set the first 'nothing selected' option.
$results[] = '=??';
foreach ( $form->data['TAGS'] as $v ) {
  $results[] = $v['id'].'='.$v['name'];
}
$results = implode("\n", $results);
echo $results;
$mainframe =& JFactory::getApplication();
$mainframe->close();
?>

However, I have no idea about the JavaScript code, that I have to use and in which place.

I have seen various post and various solutions in this forum regarding double dropdown. Other solutions are using json, other not, other using external php files etc…, and I am completely lost, (since I do not have deep knowledge about all this). I think also that the solutions are depending on the chronoforms and joomla versions (i.e. mootools etc…).

Is it possible to help me resolve my issue and make it a new working example (for chronoforms v5 and joomla 3.3.3), which will be an easy working reference for this kind of questions? I have noticed that “double dropdown” is a very popular topic, but references like the following are not very easy to understand by novice people:

- https://www.chronoengine.com/faqs/58-cfv4/cfv4-elements-and-html/2700-how-can-i-have-a-button-to-add-more-form-inputs.html
- http://www.chronoengine.com/faqs/58-cfv4/cfv4-elements-and-html/2647-how-do-i-build-a-select-drop-down-radio-button-or-checkbox-group.html
- http://www.chronoengine.com/forums/posts/f12/t96756/populate-fields-based-on-dropdown-selection.html?hilit=dropdown+select
- http://www.chronoengine.com/forums/posts/f2/t94009/
- http://www.chronoengine.com/forums/posts/f26/t92943/double-dropdown-with-ajax.html
- http://www.chronoengine.com/forums/posts/t89424/p314862.html#p314862

I would appreciate one more time your help
Best Regards
Jean Pierre

Hi,

I am doing some experimentation to find out a way to populate dynamically my double dropdown based on values selected on the first dropdown.

I have used the following javascript code, inside the On load event, before the HTML (Render Form) action, in order to get the item id value (of the value selected in the first dropdown):

jQuery(document).ready(function($){
$("#item").change(
function() {
item=$("#item option:selected").val(); //item id
}); //end ready function
});

Is it possible to use this specific item id value (item) taken from the above javascript inside an sql statement in a custom code on a new_event, in order to populate my second dropdown. If yes, could you please explain how to pass this value in the new_event event? I need to populate the second dropdown element using an sql statement and not a DB Read action.

Thanks in advance
Best Regards
Jean Pierre

Hi Jean Pierre,

Please see this FAQ which I've just written to try to work out how to do this with CFv5.

Best wishes

Bob

Hello Bob,

I would like to thank you for the FAQ.
I was completely confused with various posts talking about javascript, ajax, external php files etc....
With this FAQ, I have understood that i didn't need any Load JS action. My problem was how to get the value from my first dropdown and send it to my sql statement in the second dropdown. In my first attempt I was missing the brackets '{$form->data['category']}' and that was the error....

I succesfully managed to populate my second dropdown without the following code:

<?php
$options = array();
if ( !$form->data['Data'] || count($form->data['Data']) < 1 ) {
  // no result was found
  $options[] = 'Please select a category';
} else {
  foreach ( $form->data['Data'] as $d ) {
    $options[$d['id']] =  $d['title'];
  }
}
echo json_encode($options);
?>

Apparently the sql statement was enough to populate the 2nd dropdown.

The only problem that I have now, is that I can not set up a text like "Select a Product" in my 2nd dropdown, although I have set it up in the empty option of the dropdown element and also in the event tab of the 1st dropdown, like you have shown in this FAQ.

Do you have any idea?

Many thanks
Jean Pierre

Hi Jean Pierre,

I think this version should solve the Please select problem. It adds the first option weather or not a record is found.

<?php
$options = array();
$options[] = 'Please select a category';
if ( $form->data['Data'] && count($form->data['Data']) >= 1 ) {
  foreach ( $form->data['Data'] as $d ) {
    $options[$d['id']] =  $d['title'];
  }
}
echo json_encode($options);
?>

Bob

Hello Bob,

Thank you very much. It works.

However, in case you use a Custom Code with a select statement instead of DB a Read Action, the foreach statement is not necessary in order to populate the second dropdown. Just the sql select statement is returning the values needed, with $form->data['CATEGORIES'] = $db->loadColumn();

In that case, the foreach statement is a just redundancy which make the population time bigger. However, with just the sql statement I don't know how to solve the "Please select ..." issue!!!

Thanks again
BR/Jean Pierre

Hi Jean Pierre,

I agree that the extra code is only needed if you use the DB Read action; that's just simpler for users who aren't familiar with the Joomla! DBO code.

I think that array_unshift() will let you add the extra value

. . .
array_unshift($form->data['CATEGORIES'], array('id' => '', 'text' => 'Please select'));
?>

Not tested and may need debugging!

Bob

Hello Bob,

I have tried the array_unshift() as follows:

$db->setQuery($sql);
$form->data['CATEGORIES'] = $db->loadColumn();
array_unshift($form->data['CATEGORIES'], array('id' => '', 'text' => 'Please select'));
echo json_encode($form->data['CATEGORIES']);

where $sql stores my sql statement.

However, instead of "Please select", it's displayed: [object Object].

By the way is it possible to display a "Select" text in both dropdowns, upon "On Load" action, with the second one being idle until the user makes its selection in the first dropdown?

Thank you very very much
Best Regards,
Jean Pierre

Hi Jean Pierre,

I got the Object object value before I added the PHP to restructure the code, I've probably got the structure of the extra array element wrong :-(

You can use the Debugger to see the structure of the resulting array and correct the code.

And, yes sure - we call that a Double drop-down and there is some built in support for that in CFv5 Please see this FAQ

Bob.

Hi Bob,

Although the "Debbuger" action is working inside the "On Load" and "On Submit" events, unfortunately it's not working inside the "On ajax" event. If I add a Debugger action inside the "On ajax" event (after the Custom Code action) the second dropdown is not populated any more and I don't get any debug info.

On the other hand I have tried to make exactly what you are suggesting on your aforementioned FAQ, in order to display the "Select" text in both dropdowns, upon "On Load" action. I have set up the events in the element settings of the first dropdown according to "Using Dynamic Options/The dropdown1 element" paragraph of your FAQ. I have also put a different "Select" text on both dropdowns in the "Empty Option" on both dropdowns, but it does not seem to work.

Do you have any idea?

Many thanks
Best Regards/Jean Pierre

Hi Jean Pierre,

You can 'make' the debugger work inside the Ajax event - use your browser Web Developer tools. I use Chrome and there is a Network tab that will show me what is returned from an Ajax request. Normally this should just show a list of options (see the image below) but if a Debugger is added the debug output will be visible - possibly messy but it can be interpreted.

Bob