Double Dropdown with Ajax

Hello, I know there are other topics out there similar to this, but I don't think they are the same. I would like to have two dropdowns, where the second dropdown options are based on the choice of the first dropdown. My first dropdown is a custom Element pulling a list of Countries (for example). I have an SQL statement pulling from a database table in the Joomla database to list these countries in a dropdown. I would like my second dropdown to be similar, but the SQL statement will end like this: "...WHERE country = countryChosenFromFirstDropdown". I tried to use "WHERE country = '{$form->data['firstDropdownName']}'" but this is still returning an empty set. Any ideas? I found that syntax here: http://www.chronoengine.com/faqs/63-cfv4/cfv4-working-with-form-data/2594-how-can-i-use-the-form-data-with-php.html. My second dropdown is also a Custom Element. I figured it would be easiest to use Custom Elements since I have very indepth SQL statements which alters date fields in the SELECT portion of the statement. I found with other methods, I only had control over the WHERE clause, I need to be able to product the whole SQL statement. Please let me know if you need anymore information, I know this is kind of confusing and broad. I can copy the code also if needed. If there is another topic that answers this question, please point me to it. Thank you!

Thank you for your response. I got the first dropdown populating from the Custom Code event. When I went to set up the second dropdown I realized I might have missed some information in my previous request. My second dropdown needs some SQL customization:

SELECT CONCAT(DATE_FORMAT(`part1Date`, '%b %e, %Y '), `part1Day`, DATE_FORMAT(`begTime1`, ' %l:%i %p - '), DATE_FORMAT(`endTime1`, '%l:%i %p - ')) as `value` FROM `class_schedules`, `class_locations` WHERE `part1Date` > CURDATE() AND `class_locations`.`building` = '{$form->data['fromFirstDropdown']}' ORDER BY `part1Date` ASC;

The Dynamic Dropdown action doesn't allow for custom SQL statements does it? Am I just missing a step (hopefully!)?

Hi maspegren,

You can have full control over the query. Please see this FAQ and scroll down to the AJAX example near the end. You can either use a DB Multi-Record loader to get the raw records then do the computation in PHP; or you can hand-code a DB query using this SELECT statement.

Bob

I attempted this. I added a Dynamic Dropdown with the Source ID being the top dropdown ID, the Target ID being the second dropdown ID, Use Ajax set to Yes, and the AJAX event name is "ajax". I also put in "x" in the Extra Options Extension.

Then, instead of creating a DB Multi Record Loader I skipped that step and placed a Custom Code action in the "ajax" event.

This Custom Code is all within PHP tags, here's what I ended up with:

    $db =& JFactory::getDBO();
    $sql = "SELECT CONCAT(DATE_FORMAT(`part1Date`, '%b %e, %Y '), `part1Day`, DATE_FORMAT(`begTime1`, ' %l:%i %p - '), DATE_FORMAT(`endTime1`, '%l:%i %p - ')) as `value` FROM `class_schedules`, `class_locations` WHERE `part1Date` > CURDATE() AND `class_locations`.`loc` = `class_schedules`.`loc` AND `class_locations`.`building` = '{$form->data['choose_location_dropdown']}' ORDER BY `part1Date` ASC;";
    $db->setQuery( $sql );
    $allDates = $db->loadAssocList();
    $form->data['chooseDateID'] = $allDates

I set up the database connection, the SQL query, and loaded the results into $form->data['chooseDateID']. Then I used the code from the FAQ and hopefully replaced the variables appropriately:

$allDates = array();
// the next line set the first 'nothing selected' option.
$allDates[] = '=??';
foreach ( $form->data['chooseDateID'] as $v ) {
  $allDates[] = $v['value'].'='.$v['value'];
}
$allDates = implode("\n", $allDates);
echo $allDates;
$mainframe =& JFactory::getApplication();
$mainframe->close();

I'm not sure about the $form->data['chooseDateID'] part, I wasn't sure how to end the section I created for the SQL statement, then begin the section given in the FAQs. When I test this it just get an empty second dropdown. When I use Firebug, this second dropdown looks like <div id="error-message-choose_date_dropdown"></div>. I put a debugger in the form and didn't get any additional errors. Any ideas?

Is there anymore information that's needed? I would really like to figure this out.

Hi Maspegren,

The 'right' answer here is for you to add some debug code and use the Network tab in your browser Web Developer tools to see what is being returned.

I don't see anything wrong with the code, just a couple of small comments:

a) There's a ; missing in this line but I guess that may be from the copy and paste.

$form->data['chooseDateID'] = $allDates

b) Using $allDates twice is a bit confusing but I think it is OK.

c) I'm assuming that both chunks of code are in the same Custom Code action in the Ajax event.

Bob

I added debug code to the onLoad event and got code for the first dropdown no problem. Then I added a debugger to the On ajax event and got nothing. Both of these debuggers are in the Events area. When I used Firebug and viewed the Script I found the choose_location_dropdown had this line: url: 'index.php?option=com_chronoforms&chronoform=DriveTimeslot2&event=ajax'. I don't ever see the URL change to this, could that be an issue or is this just some backend stuff that the browser will do without the user knowing? I will continue looking at the script, but I'll have to admit, I'm not 100% sure what I am looking for.

I did miss the semicolon, I put it in an made sure I wasn't missing anymore and nothing changed.

What do you mean I used $allDates twice? Each instance of $allDates is supposed to refer to the same variable in my code. Should I create a second variable to replace some of the $allDates somewhere?

I'm going to outline exactly what I did again to verify everything is correct:

1. Use Form Wizard to create new form
2. Add two Drop Down elements with Dynamic Data set to Yes
3. Go to the Events tab and add a Custom Code element, and a Show html element.
4. Place this in the Custom Code element to pull the first dropdown options

    <?php
    $db =& JFactory::getDBO();
    $sql = "SELECT DISTINCT `class_locations`.`building` as `classLocation` FROM `class_schedules`, `class_locations` WHERE `part1Date` > CURDATE() AND `class_locations`.`loc` = `class_schedules`.`loc` ORDER BY `classLocation` ASC;";
    $db->setQuery( $sql );
    $allBuildings = $db->loadAssocList();
    $form->data['chooseLocationID'] = $allBuildings;

    ?>

5. Then I went back to my first dropdown and added "chooseLocationID" to the Data Path option under Dynamic Data, and made the Value Key and Text Key "classLocation".
6. I added a Dynamic Dropdown to the events tab. I configured the event by placing the first dropdown id in the Source box, the second dropdown ID in the Target box, Use AJAX set to "Yes", AJAX event name set to "ajax", and an "x" in the extra options.
7. I added an event to the Events tab called "ajax"
8. Since I can't use a DB Multi Record Loader I just added a Custom Code action to the "ajax" event.
9. I set up a DB connection, set up the SQL statement, saved the outcome to an array called "chooseDateID" then added the code at the bottom of the Double Dropdown with AJAX tutorial 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, changing out variables with my own. Here is the code I came up with:

    <?php
    $db =& JFactory::getDBO();
    $sql = "SELECT CONCAT(DATE_FORMAT(`part1Date`, '%b %e, %Y '), `part1Day`, DATE_FORMAT(`begTime1`, ' %l:%i %p - '), DATE_FORMAT(`endTime1`, '%l:%i %p - ')) as `value` FROM `class_schedules`, `class_locations` WHERE `part1Date` > CURDATE() AND `class_locations`.`loc` = `class_schedules`.`loc` AND `class_locations`.`building` = '{$form->data['choose_location_dropdown']}' ORDER BY `part1Date` ASC;";
    $db->setQuery( $sql );
    $allDates = $db->loadAssocList();
    $form->data['chooseDateID'] = $allDates;

$allDates = array();
// the next line set the first 'nothing selected' option.
$allDates[] = '=??';
foreach ( $form->data['chooseDateID'] as $v ) {
  $allDates[] = $v['value'].'='.$v['value'];
}
$allDates = implode("\n", $allDates);
echo $allDates;
$mainframe =& JFactory::getApplication();
$mainframe->close();

    ?>

10. Then I went back to the second dropdown and added "chooseDateID" as the Data Path and put in "value" in the Value Key and the Text Key. "value" is defined in the SQL statement, so is "chooseDateID".

The top dropdown loads, but nothing shows up in the bottom dropdown. Here's what I get when I use Firebug to view the Script:

window.addEvent('load', function() {
$('choose_location_dropdown').addEvent('change', function(){
var load_req = new Request({
url: 'index.php?option=com_chronoforms&chronoform=Double_Dropdown_Test&event=ajax',
method: 'get',
onRequest: function(){
$('choose_date_dropdown').empty();
new Element('option', {'value': '', 'text': 'Loading...'}).inject($('choose_date_dropdown'));
},
onSuccess: function(responseText){
$('choose_date_dropdown').empty();
var response_data = responseText.trim().split("\n");
response_data.each(function(line){
var line_data = line.split("=");
new Element('option', {'value': line_data[0], 'text': line_data[1]}).inject($('choose_date_dropdown'));
});
$('choose_date_dropdown').fireEvent('change');
},
onFailure: function(){
$('choose_date_dropdown').empty();
new Element('option', {'value': '', 'text': 'Loading failed.'}).inject($('choose_date_dropdown'));
}
});
load_req.send($('choose_location_dropdown').get('name')+'='+$('choose_location_dropdown').get('value'));
});
}); 

It doesn't look like anything is coming in... Any ideas? I can give you the URL too if that helps. I know I'm close, and it's probably just something really stupid and obvious I'm missing :?

I put in the url below: /index.php?option=com_chronoforms&chronoform=Double_Dropdown_Test&choose_location_dropdown=Lincoln Southeast High School&event=ajax and got all the times/dates that are supposed to show in the dropdown. I still don't see anything from the debugger though. I added a "Debugger" Action to the On ajax event. I also removed the modelID for the second dropdown. Is there a reason the dropdown items will show when I type in the URL mentioned above, but not on my form?

I have opened Firebug with this URL:/index.php?option=com_chronoforms&chronoform=Double_Dropdown_Test&choose_location_dropdown=East%20High%20School&event=ajax and found the Script tab to be empty and the HTML just lists the SQL results.

I believe the only information given when viewing the form on the site using Firebug is the code I posted below:

 //<![CDATA[
window.addEvent('load', function() {
$('choose_location_dropdown').addEvent('change', function(){
var load_req = new Request({
url: 'index.php?option=com_chronoforms&chronoform=Double_Dropdown_Test&event=ajax',
method: 'get',
onRequest: function(){
$('choose_date_dropdown').empty();
new Element('option', {'value': '', 'text': 'Loading...'}).inject($('choose_date_dropdown'));
},
onSuccess: function(responseText){
$('choose_date_dropdown').empty();
var response_data = responseText.trim().split("\n");
response_data.each(function(line){
var line_data = line.split("=");
new Element('option', {'value': line_data[0], 'text': line_data[1]}).inject($('choose_date_dropdown'));
});
$('choose_date_dropdown').fireEvent('change');
},
onFailure: function(){
$('choose_date_dropdown').empty();
new Element('option', {'value': '', 'text': 'Loading failed.'}).inject($('choose_date_dropdown'));
}
});
load_req.send($('choose_location_dropdown').get('name')+'='+$('choose_location_dropdown').get('value'));
});
});
//]]> 

Am I looking in the wrong place, or is my code missing something? Would you like the URL of the form location?

That is what I did in the second paragraph in my last response. I opened the form in the browser, opened Firebug, selected the first dropdown option, and looked in the Script tab. When I open the Console tab I get a few Warnings about password fields being present then this:

TypeError: $(...) is null	
$('choose_location_dropdown').addEvent('change', function(){

That is all that is under Console->All.

Nevermind, I seemed to have gotten it! The Dynamic Dropdown was asking for the Field ID NOT the Field Name. Both of the Field IDs were empty within the Dropdown elements, so it wasn't connecting the two dropdowns. I filled out the Field ID field to match the Field Name in both of the dropdown elements, now all is well. Thank you for your patience and guidance!!

Hi maspegren,

Well found, good to hear that you got it working OK.

Bob

I want to build a html double drop down where the user can select a 'country' and then a 'city' based on the 'country'. Also, once a 'city' has been selected I want there to be an onClick event using the href 'city_link' which takes the user to another page.

Really would appreciate some help as I've been stuck on this a while...

Hi john99s ,

Where exactly are you stuck? The first part with the country+city drop-downs is well covered here and in the FAQs.

Bob

I'm hoping this is still being monitored since my whole coding method is in the previous posts🙂 I notice when the page is initially loaded my first dropdown is populated, but my second is not. It will populate once I change my first dropdown (causing the ajax function to run). Why this occurs makes sense, but I would like it if both of the dropdowns can be populated initially, OR if something like "Select Option" can be used for my first field in my first dropdown. My first dropdown is populated by loading a loadAssocList, I'm not sure if I can add a string to the beginning of that array? I tried array_merge and using the union operator and both didn't work. They only took the first letter of the value and didn't select it initially either. I'm thinking there is probably a better, Chronoforms, way of doing this. If you need an example please let me know.

Hi maspegren,

Unfortunately the code in ChronoForms doesn't handle the republishing :-(

There's a working example in this forum post that can be adapted.

Bob

Bob, that might be above my head. Is there an easier way to add a "Select Option" text item above my other first dropdown items so the user has to select an option in the first dropdown regardless, which will trigger the second dropdown? Currently the initial option in the first dropdown is one of the records my SQL statement returns.

Hi maspegren,

I'm not sure, you can make it required but it will probably have a value from the republish anyway. You could also add a message in Custom Element element. But the JavaScript is still the neatest solution.

Bob