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?
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
)
)
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
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
