3";$db->setQuery($query);$form->data['ixce2'] = $db->loadResult();?>​Just I don't know where tu put it... and where to set which is the value and which the label.​Any suggest?"> Dynamic value in dropdpwn from a sql query - Forums

Forums

Dynamic value in dropdpwn from a sql query

M.Abletech 01 Jun, 2018
Hi,

I need to dynamically populate a dropdown from an sql query.

I think what I need is this:

<?php
$db = \JFactory::getDBO();
$query = "
SELECT `id` , `archivio`
FROM `kizgv_arxivar_IXCE_giugno2018`
WHERE `id` >3
";
$db->setQuery($query);
$form->data['ixce2'] = $db->loadResult();
?>

Just I don't know where tu put it... and where to set which is the value and which the label.

Any suggest?
GreyHead 02 Jun, 2018
Hi M.Abletech,

You can read the data with a DB Read action, or include your PHP in a Custom Code action. Then use the Dynamic Data tab of the Select DropDown element to add the results to the element settings.

Please see this FAQ for more info

Bob
jamiel91 14 May, 2019
Hi,

I have got a simpler issue to this. I am currently building an equipment booking system and I am collecting data from a SQL using custom code and looking to use the data to populate a dynamic checkbox group. The SQL query appears to work and populates the array with the correct data but I am unable to place this data into the checkbox group. What should I be putting in the 'datapath', 'value key' and 'text key' options for the dynamic data? I am looking for the 'es_equitmentid' field to populate the value key of the checkbox and the 'es_equitmentname' field to populate the text key. Do I need to make any modifications to the custom PHP code for the SQL query to format the array or parse the data first? my custom PHP code the the SQL query and the result stored in $form->data['dbdata'] are shown bellow. thanks for the help!

Custom PHP code:
<?php
$db = JFactory::getDBO();

$query="SELECT `es_equitmentid`, `es_equitmentname` FROM `jos_customtables_table_equitmentlist` WHERE `es_equitmentid` IN (SELECT`es_equitmentid` FROM `jos_customtables_table_bookedequitment` WHERE (`es_datebookedstart` < '2019-05-01' and `es_datebookedend` < '2019-05-01') OR (`es_datebookedstart` > '2019-05-30' and `es_datebookedend` > '2019-05-30')) and `es_equitmenttype`='x'";

$db->setQuery($query);
$data = $db->loadAssocList();
$form->data['dbdata'] = $data;
?>

print_r() of $form->data['dbdata']:
Array
(
[0] => Array
(
[es_equitmentid] => 101
[es_equitmentname] => Equitmentx 1
)

[1] => Array
(
[es_equitmentid] => 102
[es_equitmentname] => Equitmentx 2
)
)
jamiel91 17 May, 2019
Hi. 
Just an update on this. I have managed to resolve my issue. Thought i would post some info here to help anyone with similar questions. 
My issue turned out to be related to the fact that my form is multi page and the variable I was using to hold the data received from the SQL table ($form->data['dbdata']) gets killed when the form goes to the next page. I am not sure how to prevent this but managed to work around it another way allowing the sql query code to be run on the same page as the combobox group. I’m sure someone else will know how to get around the issue properly. 
Anyway for info for anyone else doing the same thing with the example above you need to put the following into the dynamic data fields of the checkbox: 
datapath= dbdata
value key= es_equitmentid
Text key= es_equitmentname
GreyHead 18 May, 2019
Hi jamiel91 ,

You can save the table data to the User session between pages, or, less securely, you can add it to the form in a hidden input.

Bob
This topic is locked and no more replies can be posted.