Forums

drop down list using DB , question for Greyhead ?

each 17 May, 2011
Hi ,

I've just bought "ChronoForms-31-for-Joomla-site-Cookbook-eBook" and "ChronoForms How-to doc
Build a ‘double drop-down’ using Ajax" and i need a few explanations ...as i'm really unfamiliar with chronoforms and as i really would like to take advantages of this amazing component
Regarding the tutorial how to do ... , i don't really understand where you have to put the codes:
I want to make a drop down selection as mentionned in the sample :
Having the choice beetween a french area , then , depending from the fist choice, having a selection of french departements and then the cities ...
If i take the sample, where do i need to put the code for the form HTML , the code for the form java script and the extra code 1

Looking forward to having help

Thanks
Eric
each 17 May, 2011
Sorry i had realize that i didn't give all the elements ...
My question regarding the possibility to include extra code and JS is for the V4 version as i know how to put them in the V3 version

Thks for your help

Rgds
Eric
GreyHead 18 May, 2011
Hi each,

If i take the sample, where do i need to put the code for the form HTML , the code for the form java script and the extra code 1

For other readers: In CFv3 the form HTML goes in the 'Form HTML' box; the form java script in the 'Form JavaScript' box and the extra code 1 in the 'Extra Code 1' box all on the Form Code tab.

In CFv4 the code for the Form HTML will probably go in a Custom Code box; the JavaScript in a Load JavaScript action in the OnLoad event; and the ExtraCode 1 in a Custom Event (I haven't tested this yet so I'm not certain about exactly how this works in CFv4).

Bob
each 18 May, 2011
Hi Bob and thanks for your answer but ....it doesn't work and i don't understand why

It is not a problem of code or DB as i use strictly the same code and DB structure than you in your "ChronoForms How-to doc Build a ‘double drop-down’ using Ajax" and as it work in my CFv3 and Joomla 1.5

Did i miss something ? is there a difference beetween the code to put in CFv3 and the code to put in CFv4 ?

Thanks a lot if you have an idea of the solution

Eric
GreyHead 20 May, 2011
Hi Eric,

There's another part of the answer in this post. Still haven't found time to re-work the whole tutorial :-(

Bob
each 23 May, 2011
Hi Bob and thanks but ...

Let me explain my code in details , may be it will be better :
First in my Form code :
<?php
if ( !$mainframe->isSite() ) { return; }
$db =& JFactory::getDBO();
$query = " 
SELECT DISTINCT `ISO2`, `Region1` 
FROM `#__geopc_us` 
ORDER BY `Region1`;
";
$db->setQuery($query);
$data = $db->loadObjectList();
?>
<div class="form_item"> 
<div class="form_element cf_dropdown"> 
<label class="cf_label" style="width: 150px;">Region</label> 
<select class="cf_inputbox" id="state" size="1" title="" name="state"> 
<option value="">==??==</option>
<?php
foreach($data as $d) { 
echo "<option value='".$d->ISO2."'>".$d->Region1."</option>";
}
?> 
</select> 
</div> 


<div class="cfclear"> </div>
</div>
<div class="form_item">
<div class="form_element cf_dropdown"> 
<label class="cf_label" style="width: 150px;">County</label> 
<span id='ajax_getcounty' style='color:#444;'>Please choose a state</span> 
<span id='progress_getcounty' style='visibility:hidden;' > Searching . . .</span> 
</div> 
<div class="cfclear"> </div>
</div>
<div class="form_item"> 
<div class="form_element cf_button"> 
<input value="Submit" name="button_1" type="submit" /> 
</div> 
<div class="cfclear"> </div>
</div>

--------------------
In my on ajax event custom code ( my form name is Greyhead ... tell me if you want royalties ! )
window.addEvent('domready', function() { 
$('state').addEvent('change', function () { 
var a = $('state').value; 
if ( a != null && a != '' ) { 
getCounty(a); 
} else { 
$('ajax_getcounty').setHTML("<span id='ajax_getcounty' >Please select a state</span>") 
} 
}) ;
});
function getCounty(a){ 
var url = 'index.php?option=com_chronoforms
  &chronoform=GREYHEAD&event=ajax&format=raw';
 
new Ajax(url, { 
method: 'get', 
onRequest: function(){ 
$('progress_getcounty').setStyle('visibility', 'visible'); 
}, 
onComplete: function(){ 
$('progress_getcounty').setStyle('visibility', 'hidden'); 
}, 
update: $('ajax_getcounty'), 
data: 'sid='+a 
}).request();
};

-------------------------
And finally in another custom code ( as the extra code in v3 ), but in fact i don't kjnow where to put it , do i need to create a new event or ???
<?php
$state_id =& JRequest::getString('sid', '', 'get');
if ( $state_id ) { 
$db =& JFactory::getDBO(); 
$query = " 
SELECT DISTINCT `Region2` AS `id`, `Region2` 
FROM `#__geopc_us` 
WHERE `ISO2` = '$state_id' 
ORDER BY `Region2`; 
"; 
$db->setQuery($query); 
$data = $db->loadObjectList(); 
if ( count($data) ) { 
$m = new stdClass(); 
$m->id = 0; 
$m->Region2 = '==?==';
$data = array_merge(array($m), $data); 
$return = JHTML::_('select.genericlist', $data, 'Region2', 
'class="inputbox required select" size="1" ', 'id', 'Region2', 0); 
} else { 
$return = "Sorry, we couldn't find that state"; 
}
} else { 
$return = "Sorry, we couldn't find a state id";;
}
echo $return;
?>


Thanks for you lights

Eric
GreyHead 23 May, 2011
Hi each,

It took a while but here's the re-worked version for ChronoForms v4 (seems to be OK on Joomla! 1.5 or 1.6).

Form HTML
This is similar to your form HTML but modified to use the CFv4 layout
<?php
$db =& JFactory::getDBO();
$query = "
  SELECT DISTINCT `ISO2`, `Region1`
  FROM `#__geopc_us`
  ORDER BY `Region1`;
";
$db->setQuery($query);
$data = $db->loadObjectList();
?>
<div class="ccms_form_element cfdiv_select" id="label_text_container_div">
  <label>Region</label>
  <select class="" id="state" size="1" title="" name="state">
      <option value="">==??==</option>
<?php
foreach($data as $d) {
  echo "<option value='".$d->ISO2."'>".$d->Region1."</option>";
}
?>
  </select>
  <div class="clear"></div>
  <div id="error-message-input_select_0"></div>
</div>

<div class="ccms_form_element cfdiv_select" id="label_text_container_div">
  <label>County</label>
  <span id='ajax_getcounty' style='color:#444;'>Please choose a state</span>
  <span id='progress_getcounty' style='visibility:hidden;' > Searching . . .</span>
  <div class="clear"></div>
  <div id="error-message-input_select_0"></div>
</div>

<div class="ccms_form_element cfdiv_submit" id="input_submit_1_container_div">
  <input name="input_submit_1" class="" value="Submit" type="submit" />
  <div class="clear"></div>
  <div id="error-message-input_submit_1"></div>
</div>

Form Javascript
This goes into a Load JS action. There are a lot of small changes in here to use the new MooTools 1/2/1.3 syntax. Edit the first line to use the name of your form.
var cf_formname = 'my_form_name';
window.addEvent('domready', function() {
  $('state').addEvent('change', function () {
    var a = $('state').value;
    if ( a != null && a != '' ) {
      getCounty(a);
    } else {
      $('ajax_getcounty').setHTML("<span id='ajax_getcounty' >Please select a state</span>")
    }
  });
});
function getCounty(a){
  var url = 'index.php?option=com_chronoforms&chronoform='+cf_formname+'&event=ajax&format=raw';

  new Request.HTML({
    method: 'get',
    url : url,
    onRequest: function(){
      console.log(url);
      $('progress_getcounty').setStyle('visibility', 'visible');
    },
    onComplete: function(){
      $('progress_getcounty').setStyle('visibility', 'hidden');
    },
    update: $('ajax_getcounty'),
    data: { 'sid' : a }
  }).send();
};

Ajax event code
This goes in a Custom Code action in a new On ajax event. I think that this is the same as the previous version.
<?php
$state_id =& JRequest::getString('sid', '', 'get');
if ( $state_id ) {
  $db =& JFactory::getDBO();
  $query = "
    SELECT DISTINCT `Region2` AS `id`, `Region2`
      FROM `#__geopc_us`
      WHERE `ISO2` = '$state_id'
      ORDER BY `Region2`;
  ";
  $db->setQuery($query);
  $data = $db->loadObjectList();
  if ( count($data) ) {
    $m = new stdClass();
    $m->id = 0;
    $m->Region2 = '==?==';
    $data = array_merge(array($m), $data);
    $return = JHTML::_('select.genericlist', $data, 'Region2',
    'class="inputbox required select" size="1" ', 'id', 'Region2', 0);
  } else {
    $return = "Sorry, we couldn't find that state";
  }
} else {
  $return = "Sorry, we couldn't find a state id";
}
echo $return;
?>


Max wrote: Please check Max's post here for a very important step in AJAX implementation.

After that I hope the drop down will work OK :-)

Bob

Later: following Max's edit. It looks as though just adding $mainframe->close(); to the end of the Ajax code should do the trick. Still to be tested.
each 24 May, 2011
Hi Bob

You are marvellous , it works !

Thanks a lot for your help

Eric
each 31 May, 2011
Hi

Everything is ok except trying to make the same for a second drop down list in the same form

For example in my form code:
--------------------
<?php
    $db =& JFactory::getDBO();
    $query = "
      SELECT DISTINCT `ISO2`, `Region1`
      FROM `#__geopc_us`
      ORDER BY `Region1`;
    ";
    $db->setQuery($query);
    $data = $db->loadObjectList();
    ?>
    <div class="ccms_form_element cfdiv_select" id="label_text_container_div">
      <label>Région</label>
      <select class="" id="state" size="1" title="" name="state">
          <option value="">Sélectionner</option>
    <?php
    foreach($data as $d) {
      echo "<option value='".$d->ISO2."'>".$d->Region1."</option>";
    }
    ?>
      </select>
      <div class="clear"></div>
      <div id="error-message-input_select_0"></div>
    </div> <div class="ccms_form_element cfdiv_select" id="label_text_container_div">
      <label>Département</label>
      <span id='ajax_getcounty' style='color:#444;'>Veuillez d'abord choisir votre région</span>
      <span id='progress_getcounty' style='visibility:hidden;' > Searching . . .</span>
      <div class="clear"></div>
      <div id="error-message-input_select_0"></div>
    </div>

<?php
    $db =& JFactory::getDBO();
    $query = "
      SELECT DISTINCT `ISO2`, `Region1`
      FROM `#__competences`
      ORDER BY `Region1`;
    ";
    $db->setQuery($query);
    $data = $db->loadObjectList();
    ?>
    <div class="ccms_form_element cfdiv_select" id="label_text_container_div">
      <label>Critère</label>
      <select class="" id="state" size="1" title="" name="state">
          <option value="">Sélectionner</option>
    <?php
    foreach($data as $d) {
      echo "<option value='".$d->ISO2."'>".$d->Region1."</option>";
    }
    ?>
      </select>
      <div class="clear"></div>
      <div id="error-message-input_select_0"></div>
    </div> <div class="ccms_form_element cfdiv_select" id="label_text_container_div">
      <label>Compétence</label>
      <span id='ajax_getcounty' style='color:#444;'>Veuillez d'abord choisir votre critère</span>
      <span id='progress_getcounty' style='visibility:hidden;' > Searching . . .</span>
      <div class="clear"></div>
      <div id="error-message-input_select_0"></div>
    </div>


-------------------
And then , i add a new event called ajax1 for example with the following custom code :
--------------------
<?php
    $state_id =& JRequest::getString('sid', '', 'get');
    if ( $state_id ) {
      $db =& JFactory::getDBO();
      $query = "
        SELECT DISTINCT `Region2` AS `id`, `Region2`
          FROM `#__competences`
          WHERE `ISO2` = '$state_id'
          ORDER BY `Region2`;
      ";
      $db->setQuery($query);
      $data = $db->loadObjectList();
      if ( count($data) ) {
        $m = new stdClass();
        $m->id = 0;
        $m->Region2 = 'Sélectionner';
        $data = array_merge(array($m), $data);
        $return = JHTML::_('select.genericlist', $data, 'Region2',
        'class="inputbox required select" size="1" ', 'id', 'Region2', 0);
      } else {
        $return = "Sorry, we couldn't find that state";
      }
    } else {
      $return = "Sorry, we couldn't find a state id";
    }
    echo $return;
    ?>

------------------------------------------
and a new load js event :
------------------------------------------
 var cf_formname = 'final-Eric';
    window.addEvent('domready', function() {
      $('state').addEvent('change', function () {
        var a = $('state').value;
        if ( a != null && a != '' ) {
          getCounty(a);
        } else {
          $('ajax1_getcounty').setHTML("<span id='ajax1_getcounty' >Please select a state</span>")
        }
      });
    });
    function getCounty(a){
      var url = 'index.php?option=com_chronoforms&chronoform='+cf_formname+'&event=ajax&format=raw';

      new Request.HTML({
        method: 'get',
        url : url,
        onRequest: function(){
          console.log(url);
          $('progress_getcounty').setStyle('visibility', 'visible');
        },
        onComplete: function(){
          $('progress_getcounty').setStyle('visibility', 'hidden');
        },
        update: $('ajax1_getcounty'),
        data: { 'sid' : a }
      }).send();
    };

-----------------------------------------
But it doesn't work ...

Is it a wrong way ?

Thanks by advance

Eric
GreyHead 31 May, 2011
Hi Eric,

You should be able to add more than one *but* you need to make sure that the ids of the spans are unique. At present you are using these spans for both look-ups
    <span id='ajax_getcounty' style='color:#444;'>Veuillez d'abord choisir votre critère</span>
      <span id='progress_getcounty' style='visibility:hidden;' > Searching . . .</span>
You need to make those different maybe ajax_getcritere and progress_getcritere

And then change the rest of the code to match.

Bob
each 02 Jun, 2011
Thanks Bob

I will try to do it and keep you informed

Eric
Max_admin 15 Aug, 2011
Hi guys,

I want to post an important note here, for AJAX response to work fine in Chronoforms, you should add a "Custom Code" action to the "END" of the event with the AJAX output and inside this action write this code:


<?php
$mainframe->close();
?>


This will ensure that the response returned is completely clean without any other web page code including the Chronoforms strap link.

You don't even need to access the &format=raw version of the url, the normal version should be ok.

I will edit Bob's post above to clear this.

Regards,
Max
Max
ChronoForms developer...
Did you try ChronoMyAdmin for managing your Joomla database tables ?
fvanderkleij 19 Dec, 2011
Hi,

All the above describes exactly what i need, therefore i tried to get it to work form me. only when i test it and make a selection in the first selectbox, the second selecbox turns into a link Powered By ChronoForms - ChronoEngine.com

Can anybody tell me what i am doing wrong

thx
Frank
GreyHead 20 Dec, 2011
Hi fvanderkleij ,

Please see Max's post immediately before yours. Doing this will get rid of the ChronoForms strapline. Note you can also add those two lines at the end of your Ajax code, you don't need a separate action.

If you then don't get any further response you have a but in the Ajax code somewhere. I soudl use the Web Developer tools in my browser to check the interaction and see wher ethe code is breaking. Is valid data being sent to the ajax action? Is a valid reply being received?

Bob
GreyHead 20 Dec, 2011
Hi frank,

That looks as though it is passing the data to the ajax code OK. Exactly what do you have in the Ajax code box?

Which version of ChronoForms are you using? You can find the version from Site Admin | Extensions | Install/Uninstall | Components in Joomla! 1.5 or Site Admin | Extensions | Extension Manager | Manage in Joomla! 1.6.

Bob
fvanderkleij 20 Dec, 2011
I'm using V4 the code i have in de ajax codebox is an exact copy of the code you put on this site except for the DB name.

I created an new event and put a custom code action in there. the code is

<?php
$state_id =& JRequest::getString('sid', '', 'get');
if ( $state_id ) {
  $db =& JFactory::getDBO();
  $query = "
    SELECT DISTINCT `Region2` AS `id`, `Region2`
      FROM `jos_klus_category`
      WHERE `ISO2` = '$state_id'
      ORDER BY `Region2`;
  ";
  $db->setQuery($query);
  $data = $db->loadObjectList();
  if ( count($data) ) {
    $m = new stdClass();
    $m->id = 0;
    $m->Region2 = '==?==';
    $data = array_merge(array($m), $data);
    $return = JHTML::_('select.genericlist', $data, 'Region2',
    'class="inputbox required select" size="1" ', 'id', 'Region2', 0);
  } else {
    $return = "Sorry, we couldn't find that state";
  }
} else {
  $return = "Sorry, we couldn't find a state id";
}
echo $return;
?>

<?php
$mainframe->close();
?>
fvanderkleij 20 Dec, 2011
greyhound,

i found the problem, I called the new event Ajax with a capital A putting in new event without the capital A did the trick

thx for your help.

one last question i now have an event wich has no use, do you know how i can delete is?
GreyHead 20 Dec, 2011
Hi frank,

I don't think that you can delete an unwanted event :-( You can just delete all the actions from it - it's untidy but won't do any harm.

Bob
jmarian1 01 Feb, 2012
Hi Bob. One quick question with the v4 tutorial of the drop down list. I made it work the first drop down but am having trouble with the second one. I guess I can make it work if you will tell me where the "state_id" came from database in below's ajax code. What does it do.
<?php
$state_id =& JRequest::getString('sid', '', 'get');
if ( $state_id) {
  $db =& JFactory::getDBO();
  $query = "
    SELECT DISTINCT `Region2` AS `id`, `Region2`
      FROM `#__geopc_us`
      WHERE `ISO2` = '$state_id'
      ORDER BY `Region2`;
  ";
  $db->setQuery($query);
  $data = $db->loadObjectList();
  if ( count($data) ) {
    $m = new stdClass();
    $m->id = 0;
    $m->Region2 = '==?==';
    $data = array_merge(array($m), $data);
    $return = JHTML::_('select.genericlist', $data, 'Region2',
    'class="inputbox required select" size="1" ', 'id', 'Region2', 0);
  } else {
    $return = "Sorry, we couldn't find that state";
  }
} else {
  $return = "Sorry, we couldn't find a state id";
}
echo $return;
?>
jmarian1 01 Feb, 2012
Hi marekw. Thank you for the information. I tried and tried to change the code and been checking back and forth the tutorial if I miss something but I don't see any problem. I tried your suggestion as well and still didn't work. Somehow the second drop down is not working still. I wanted to know where the "state_id" came from if it is from the database field name or just a new name that doesn't exist from anywhere (maybe it helps). Just reiterate, I don't have a "state_id" in my db and my id for my input is "district" and "school" and db fields are SchoolCode (PK), SchoolName, DistrictCode(FK) from #_school and DistrictCode, DistrictName from #_district table. Please help. I've been trying to work this in two days now (back and forth from the turorial v4 double drop down list) and I understand what it is trying to do but I am stuck and although I don't want to get stress, I am right now. Hope you can help me find where I did wrong. I guess it is something to do with my ajax code below. I appreciate all your help. Thanks in advance.
<?php
$districts =& JRequest::getString('sid', '', 'get');
if ( $districts ) {
$db =& JFactory::getDBO();
$query = "
SELECT DISTINCT `SchoolName` AS `id`,`SchoolName`
FROM `#__school`
WHERE `DistrictCode` = '$districts'
ORDER BY `SchoolName`;
";
$db->setQuery($query);
$data = $db->loadObjectList();
if ( count($data) ) {
$m = new stdClass();
$m->id = 0;
$m->SchoolCode = 'Select School';
$data = array_merge(array($m), $data);
$return = JHTML::_('select.genericlist', $data, 'SchoolCode',
'class="inputbox required select" size="1" ', 'id', 'SchoolCode', 0);
} else {
$return = "Sorry, we couldn't find that District";
}
} else {
$return = "Sorry, we couldn't find a District id";
}
echo $return;
?>

<?php
$mainframe->close();
?>
jmarian1 03 Feb, 2012
Thank you marekw for your time and your help but somehow it still didn't work. I will try to make it work and will let you know. Thanks.🙂
GreyHead 04 Feb, 2012
Hi both,

First off, apologies for the incorrect 'get' :-( It had been changed on my master copy but I must have forgotten to upload the pdf to the site. It's done now.

As marekw says the value of $state_id comes from the data line in the Form JavaScript:
data: { 'sid': a }
This passes a variable with the name sid and the current value of the JavaSCript variable a - which was defined earlier as the current selection of the state drop-down:
var a = $('state').value;


@Jmarian1. It should be mostly a question of making sure that the names match up all the way through the process. I don't see here what the names and ids of your dropdowns are, nor the Form JavaScript you are using so I can't follow all the way through.

marekw: Could you advise how it's possible to store the results entered in the two drop down boxes to the database, and then when re-displaying the data back from the DB, for them both to show?

Provided that you have both 'state' and 'country' columns in the database and the two drop-downs have matching names i.e. 'state' and 'country' then the data should save OK. Add a Debugger action to the On Submit event to see exactlty what data is being returned from the form.

Bob
jmarian1 05 Feb, 2012
HI Bob,

I am getting there just a little problem that I am not sure where it is coming from. I am getting an error message "There is no form with this name or may be the form is unpublished, Please check the form and the url and the form management."

I guess to make it clear, I wanted to show the districts with table="district" and fieldnames="DistrictCode, DistrictName". Then my second dropdown is to get the school based on the district selection. My school tablename="school" and fieldnames="SchoolCode(PK),SchoolName,DistrictCode(FK)". I followed the tutorial and somehow the error message appear. PLease help. Thanks.

First of all, here is my html code:
<div class="form_item">
    <div class="ccms_form_element cfdiv_custom" id="district_container_div">
<label for="district">District Name:</label>
<?php
$db =& JFactory::getDBO();
$query = "
SELECT DISTINCT `DistrictCode`, `DistrictName`
FROM `#__district`
ORDER BY `DistrictName`;
";
$db->setQuery($query);
$data = $db->loadObjectList();
?>
<select size="1" class="" title="" type="select" name="district" id="district">
<option value="">Select District</option>
<?php
foreach($data as $d) {
echo " <option value='".$d->DistrictCode."'>".$d->DistrictName."</option>";
}
?>
</select>
<div class="clear"></div>
<div id="error-message-school"></div>
</div>
   <div class="ccms_form_element cfdiv_custom" id="school_container_div">
<label for="school">School Name:</label>
<span id="ajax_getschool">Please choose District</span> 
<span id="progress_getschool" style="visibility: hidden;">Searching...</span>
<div class="clear"></div>
<div id="error-message-school"></div>
</div>

here is my js code:
window.addEvent('domready', function() {
$('district').addEvent('change', function () {
// edit these two lines to match your form
var message = "Please select a District";
var a = $('district').value;
if ( a != null && a != '' ) {
getSchool(a);
} else {
$('ajax_getschool').setHTML("<span id='ajax_getschool' >"+message+"</span>")
}
}) ;
});


function getSchool(a){
var form_name = "principalform";
var url =
"index.php?option=com_chronoforms&chronoform="+principalform+"&event=ajax&format=raw";
new Request.HTML({
url: url,
method: 'post',
onRequest: function(){
$('progress_getschool').setStyle('visibility', 'visible');
},
onComplete: function(){
$('progress_getschool').setStyle('visibility', 'hidden');
},
update: $('ajax_getschool'),
data: { 'sid': a }
}).send();
};


here is my ajax code
<?php
$district =& JRequest::getString('sid', '', 'get');
if ( $district ) {
$db =& JFactory::getDBO();
$query = "
SELECT DISTINCT `SchoolName` AS `id`,`SchoolName`
FROM `#__school`
WHERE `DistrictCode` = '$district'
ORDER BY `SchoolName`;
";
$db->setQuery($query);
$data = $db->loadObjectList();
if ( count($data) ) {
$m = new stdClass();
$m->id = 0;
$m->SchoolName = 'Select School';
$data = array_merge(array($m), $data);
$return = JHTML::_('select.genericlist', $data, 'SchoolName',
'class="inputbox required select" size="1" ', 'id', 'SchoolName', 0);
} else {
$return = "Sorry, we couldn't find that District";
}
} else {
$return = "Sorry, we couldn't find a district id";
}
echo $return;
?>

$mainframe =& JFactory::getApplication(); $mainframe->close();
This topic is locked and no more replies can be posted.