Buy Now
Sign in

Dynamic value in dropdpwn from a sql query

M.Abletech , June 01 2018, 14:47
M
M.Abletech 62
June 01 2018, 14:47 #378086
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 64
June 02 2018, 09:01 #378096
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
ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much
J
jamiel91 2
May 14 2019, 10:04 #386837
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
)
)
J
jamiel91 2
May 17 2019, 17:04 #386926

Hi.&nbsp;

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.&nbsp;

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.&nbsp;

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:&nbsp;

datapath= dbdata

value key= es_equitmentid

Text key= es_equitmentname

GreyHead 64
May 18 2019, 07:31 #386931
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
ChronoForms technical support
If you'd like to buy me a coffee or two, thank you very much