Problem by inserting a select in a custom code dropdown

Hi,
I have a problem with a select field in a dropdown custom code.
The code is

<?php
$options = array();
if ( !$form->data['Datadrop'] || count($form->data['Datadrop']) < 1 ) {
  // no result was found
  $options[] = 'Please select a category';
} else {
  foreach ( $form->data['Datadrop'] as $d ) {
    $options[$d['idcat']] =  $d['idcat'];
   $db =& JFactory::getDBO();
   $query = "
   SELECT `descrizionecategoria`
   FROM `hij7p_chronoengine_chronoforms_datatable_categoria`
   WHERE `id` = $d['idcat'];
   $db->setQuery($query);
   $data = $db->loadResult();
  $options[$d['idcat']] =  $data;
  }
}
echo json_encode($options);
?>

The problem is that in the dropdown I would like to show a text of category not the index.
Any suggestoins about it?


Thanks

Kiwiup

Hi Kiwiup,

Where is the description saved? Is it in the same table? Then you can get both the id and the description and use those as the value and text for your drop-down. (You can use a DB Read action and the Dynamic settings in the Select Dropdown to avoid the Custom code but both methods will work Ok).

Bob

Hi Bob,
thanks a lot for your answer.
I try to explain my task:
I have a chained dropdown.
In the first dropdown I select a value from a table called table1.
In the second dropdown I would like to get only value from the same table (table1) filtered by the value of rhe first dropdown.
I applied a code shown in FAQ and it works.
In the second dropdown appear a numeric value.
His text value (description) belongs to another table, table2. So I would like to show in the second dropdown this description not a numeric value.
This is the latest code that i developed

<?php
$options = array();
$vedere = array();
if ( !$form->data['Datadrop'] || count($form->data['Datadrop']) < 1 ) {
// no result was found
$options[] = 'Please select a category';
} else {
foreach ( $form->data['Datadrop'] as $d ) {
$options[$d['idcat']] = $d['idcat'];
$db = &JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('description');
$query->from('`table2`');
$query->where('id="1"');
$db->setQuery($query);
$category = $db->loadObjectList();
$vedere[$d['idcat']] = $category[0]['description'];
}
}
echo json_encode($vedere);
?>

The problem is in the WHERE conditions. I have to achieve all value not 1.
I tried to write id = $d['idcat'] but didn't work.

Any idea?

Thanks

Hi Kiwiuup,

Ok - then you need to add a Join to your Custom Query to add table 2 and get the description from that. You can probably also use a MySQL IN clause to save the looped queries. Something like this:

<?php
. . . 
$datadrop = implode(', ', $form->data['Datadrop']);
$db =& JFactory::getDBO();
   $query = "
   SELECT c.`descrizionecategoria`
      FROM `hij7p_chronoengine_chronoforms_datatable_categoria` AS c
      JOIN `some_other_table` AS t ON c.`xxx` = t.`yyy`
   WHERE c.`id`  IN ({$datadrop}) ;
$db->setQuery($query);
$data = $db->loadAssocList();
foreach ( $data as $d ) {
   . . // build options array 

Bob

Hi Bob,
thanks for your explanation but I didn't solved yet.
I made changes by following your hints but maybe I wrote some wrong code.
I would like to connect dynamically three dropdown.
This is the master table:
mastertable
id, idtiprap,idcat,idliv

This is the tabletiprap table (id = idtiprap)
id, tiprapdescription

This is the tablecat table (id = idcat)
id, categorydescription

I followed your suggestions for the second dropdown element but no category description appear.

<?php
$options = array();
$vedere = array();
if ( !$form->data['Datadrop'] || count($form->data['Datadrop']) < 1 ) {
  // no result was found
  $options[] = 'Please select a category';
} else {
 $datadrop = implode(', ', $form->data['Datadrop']);
 $db =& JFactory::getDBO();
 $query = $db->getQuery(true);
 $query->select('c.`categorydescription`');
 $query->from('`tablecat` AS c  JOIN    `mastertable` AS t ON c.`id` = t.`idcat`);
 $query->where('c.`id` IN ({$datadrop})');  
 $db->setQuery($query);
 $data = $db->loadAssocList();
  foreach ( $data as $d ) {
   $vedere[$d['idcat']] =  $data[0][categorydescription];
  }
}
echo json_encode($vedere);
?>

This is the tableliv table (id = idliv)
id, leveldescription

By following the FAQ I made a three dropdown dynamically chained but with numeric vslue not their description (tiprapdesciptiob, categorydescription and leveldescription in the dropdown)

Thanks for your help.

Kiwiup

Hi Kiwiup,

I can only suggest that you debug the code, I think that you probably need to get both the id and the description in the query and this line looks odd to me:

$vedere[$d['idcat']] = $category[0]['description'];

Bob

Hi Bob,
Thanks.
I am looking for a solution snd I am following your hint about Join between two tables.

Kiwiup

Hi Kiwiup,

Yes, I understand that you are trying the join. I'd test the query in PHPMyAdmin to make sure that it is outputting what I need, and then test the PHP to make sure that I am building a valid options array.

If you add debug code to the Ajax event you can usually see the output using your browser web-developer tools (on the Network tab in Chrome).

Bob

Hi Bob,
I found a right query with SQL but I didn't find out how to pass a right values in the loop.
Later I will send to you my step.
Thanks.

Kiwiup

Hi Bob,
this is my last result but doesn't properly
Any idea?
A little explanation:
in the first dropdown I choose an index (idtiprapporto belongs to table t). After in the second dropdown appears an index (idcat belongd to table t) connected to the first choice. The description of this index is in the another table c.
Look at this utl to view what is going to.
http://www.fabiocenci.it/bd/index.php?option=com_chronoforms5&chronoform=esempiotabrapporto
I believe to be close to the solution.
Thanks.

<?php
$options = array();
$vedere = array();
$datatiprap = implode(', ', $form->data['Datadrop']);
if ( !$form->data['Datadrop'] || count($form->data['Datadrop']) < 1 ) {
  // no result was found
  $options[] = 'Please select a category';
} else {
  foreach ( $form->data['Datadrop'] as $d ) {
   $options[$d['idcat']] =  $d['idcat'];
  $db = &JFactory::getDBO();
  $query = $db->getQuery(true);
  $query->select('`descrizionecategoria`');    $query->from('`hij7p_chronoengine_chronoforms_datatable_categoria` AS c,`hij7p_chronoengine_chronoforms_datatable_tableminimi` AS t ');
$query->where('c.id=t.idcat AND t.idtiprapporto ={$datatiprap}');
$db->setQuery($query);
$category = $db->loadObjectList();
   $vedere[$d['idcat']] =  $category[$d['idcat']]->descrizionecategoria;
  }
}
echo json_encode($vedere);
?>