Forums

Hi!
​
Can the results of a complex sql statement be displayed on the form?
​
I've tried below to get a very basic sql/php statement to display in an HTML view, but I don't know what PHP to use (I'm finding all kinds of different things in the forum and I'm not sure what's current). I'm working up towards having a drop down selection feed a value into the where section of more complex query, but I figured I'd start with a basic one and then work my way up. Can you get me started please? Let's say I want to get this basic statement to display...
​
<?php
$db = \JFactory::getDBO();
$query = "
SELECT *
FROM `tbltrainedtopics`;
";
$db->setQuery($query);
$data = $db->loadResult();
print $data;
?>

Hi jformicola,
​
There is a DB Read action that you can use; or you can include your PHP in a PHP action - check the notes for the format to use (I think it is without PHP tags, and you can set a variable name to make the result available to later actions.
​
Bob

Hi Bob!
​
I don't think the DB read will work as the statement is complex multiple IN statements sort of nested. What notes? The manual? I don't really see anything there that will help. Can you please just provide an example statement that will run a sql and display the results?Please?
​
Thanks!

Hi Bob!
​
I've got the PHP working and I think it's being fed one value from the dropdown selection which was one of my goals. Now I want to have the results display in a frame below the dropdown choice. How would I accomplish that? How can I have the results displayed on form and refresh/chnage any time the employee is changed?
​
Thanks so much!
j
​
​
$db =& JFactory::getDBO();
$query = "
SELECT `tbltrainedtopics`.ID , `tbltrainedtopics`.`Subject` as subject, `tbltrainedtopics`.`Instruction_Resource_1`, `tbltrainedtopics`.`Instruction_Resource_2`
FROM `tbltrainedtopics`
WHERE `tbltrainedtopics`.ID NOT IN (SELECT `tbltrainedtopics_parsed`.TrainedTopics FROM `tbltrainedtopics_parsed` WHERE `tbltrainedtopics_parsed`.aid IN (SELECT `kam_chronoforms_data_trainingchecklist`.aid FROM `kam_chronoforms_data_trainingchecklist` WHERE `kam_chronoforms_data_trainingchecklist`.TraineeEmpID = '{data:employee}') );
";
$db->setQuery($query);
$data = $db->loadAssocList();
foreach ( $data as $d ) {
echo ''.$d[OTHERCOLUMNHERELATER].' '.$d['subject'].'';
}
echo '';
?>

You can't use shortcodes in PHP.
​
$this->data('field') for {data:
$this->get("variable") for {var:

Oh, ok thanks. I thought it was working, I was using a custom code element, doesn't that work? Ok I'll swap that out.
​
BUT, can you please supply a solution to how to design the rest of the form?
How can I have the results displayed on the form below the dropdown where the employee is selected and have the display refresh/change any time the employee is changed in the drop down box?

Custom Code can do shortcodes, but the PHP part needs to be within tags <?php ?>
​
Return the data from the code, use {var:codeblockname.value_name} as your field values. Have the dropdown on change event reload the section containing the fields, set the reload URL on the section itself, have that SQL code in the reload event

Hmm... I must be doing something wrong as it's bugging out. Keeps reloading the choose employee dropdown, stuck in some sort of loop. Can you see what I've done wrong?
Thanks!
​

Do you have an event (or "page") called custom_code12? I bet you don't🙂

Oh, so putting the custom_code name there doesn't work? Can you tell me the exactl name of the item I have to add please? Does it go in the "Views" or "Actions" tab?
​
Thanks for all your help!!!

There's a dynamic reload demo form you could have a look at.

So frustrated. I ended up simplifying the design as nothing was working, only to discover the sql statement isn't working after all it seems. Specifically the shortcode in there doesn't seem to be working. I can't seem to get your suggestions above to work either. Can you please insert into the highlighted box below what's needed?
​
Basically I just want the selection for the employee drop down to get passed to that sql statement. The drop down employee is selected, the user submits and the results of the query are displayed... but the query isn't getting the employee. Also, I'd like the results to be one per line, not all combine on one line. Can you please help?
​
​
​
<?php
$db =& JFactory::getDBO();
$query = "
SELECT `tbltrainedtopics`.ID , `tbltrainedtopics`.Subject as subject, `tbltrainedtopics`.Instruction_Resource_1, `tbltrainedtopics`.Instruction_Resource_2
FROM `tbltrainedtopics`
WHERE `tbltrainedtopics`.ID NOT IN (SELECT `tbltrainedtopics_parsed`.TrainedTopics FROM `tbltrainedtopics_parsed` WHERE `tbltrainedtopics_parsed`.aid IN (SELECT `kam_chronoforms_data_trainingchecklist`.aid FROM `kam_chronoforms_data_trainingchecklist` WHERE `kam_chronoforms_data_trainingchecklist`.TraineeEmpID = '{data:employee}'));
";
$db->setQuery($query);
$data = $db->loadAssocList();
foreach ( $data as $d ) {
echo ''.$d['subject'].'';
}
?>
​

Likei said you can't use the short code in PHP.
​
$this->data("employee")

Thanks, but I tried a bunch of combinations, with quotes around it without, etc. to no avail. Here are the errors I get for each, can you please see what's wrong?
​
WHERE `kam_chronoforms_data_trainingchecklist`.TraineeEmpID = $this->data("employee"))); / ERROR = 0 syntax error, unexpected 'employee' (T_STRING)
WHERE `kam_chronoforms_data_trainingchecklist`.TraineeEmpID = '$this->data("employee")')) / ERROR = (same as above)
WHERE `kam_chronoforms_data_trainingchecklist`.TraineeEmpID = $this->data('"employee"'))); / ERROR =(same as above)
WHERE `kam_chronoforms_data_trainingchecklist`.TraineeEmpID = "$this->data("employee")")); / ERROR = 0 syntax error, unexpected '$this' (T-Variable)
WHERE `kam_chronoforms_data_trainingchecklist`.TraineeEmpID = $this->data('employee'))); / ERROR = syntax error or access violoation 1305 Function... array does not exist
​
The extra parenthesis at the end are closing other where statemetns and should be there. Here is a version that works, just without the subsituation.
​
SELECT `tbltrainedtopics`.ID , `tbltrainedtopics`.Subject as subject, `tbltrainedtopics`.Instruction_Resource_1, `tbltrainedtopics`.Instruction_Resource_2
FROM `tbltrainedtopics`
WHERE `tbltrainedtopics`.ID NOT IN (SELECT `tbltrainedtopics_parsed`.TrainedTopics FROM `tbltrainedtopics_parsed` WHERE `tbltrainedtopics_parsed`.aid IN (SELECT `kam_chronoforms_data_trainingchecklist`.aid FROM `kam_chronoforms_data_trainingchecklist` WHERE `kam_chronoforms_data_trainingchecklist`.TraineeEmpID = '2'));
​
FULL PHP STATEMENT IN CASE YOU NEED IT:
​
<?php
$db =& JFactory::getDBO();
$query = "
SELECT `tbltrainedtopics`.ID , `tbltrainedtopics`.Subject as subject, `tbltrainedtopics`.Instruction_Resource_1, `tbltrainedtopics`.Instruction_Resource_2
FROM `tbltrainedtopics`
WHERE `tbltrainedtopics`.ID NOT IN (SELECT `tbltrainedtopics_parsed`.TrainedTopics FROM `tbltrainedtopics_parsed` WHERE `tbltrainedtopics_parsed`.aid IN (SELECT `kam_chronoforms_data_trainingchecklist`.aid FROM `kam_chronoforms_data_trainingchecklist` WHERE `kam_chronoforms_data_trainingchecklist`.TraineeEmpID = $this->data("employee")));
";
$db->setQuery($query);
$data = $db->loadAssocList();
foreach ( $data as $d ) {
echo ''.$d['subject'].'';
}
?>

You left the $this->data inside the string, so there's a syntax error where the string breaks back out. I should've paid more attention, and you should've put it through a syntax checker.
​

<?php
$db =& JFactory::getDBO();
$query = "
SELECT `tbltrainedtopics`.ID , `tbltrainedtopics`.Subject as subject, `tbltrainedtopics`.Instruction_Resource_1, `tbltrainedtopics`.Instruction_Resource_2
FROM `tbltrainedtopics`
WHERE `tbltrainedtopics`.ID NOT IN (SELECT `tbltrainedtopics_parsed`.TrainedTopics FROM `tbltrainedtopics_parsed` WHERE `tbltrainedtopics_parsed`.aid IN (SELECT `kam_chronoforms_data_trainingchecklist`.aid FROM `kam_chronoforms_data_trainingchecklist` WHERE `kam_chronoforms_data_trainingchecklist`.TraineeEmpID = {$this->data("employee")}));
";
$db->setQuery($query);
$data = $db->loadAssocList();
foreach ( $data as $d ) {
echo ''.$d['subject'].'';
}
?>

It's working, praise God! Thanks so much. Thank you for the suggestion on the syntax checker.