Forums

ChronoForms v8 is now WordPress ready, v6 & v7 forms import is now supported, latest v8.0.29 release details

The JED needs volunteers!

Special query with DB Read

my
myky2k1 07 Apr, 2016
Hello all. I have a very big problem with a special query.

I have this query: 
SELECT Distinct 
I31.codmod,
I31.desmod
FROM IDAT2410F I24
Left Join IDAT3200F I32 on I32.CODALL = I24.CODALL
Left Join IDAT3100F I31 on I31.CODMAR = I32.CODMAR And I31.CODMOD = I32.CODMOD
Where
I24.ANNOXX = 2004                          
And I32.codmar = '33'                                        
Group By I31.codmod,I31.desmod
Order by I31.desmod


And i have to put it on a DB read action on an event, to display the result of the query on a dropdown.

Please, help me, how can i do this?

GreyHead, any ideea?
Gr
GreyHead 07 Apr, 2016
Answer
Hi myky2k1,

Rather than using a DB Read action for a query like this you can use a Custom Code action and save the results to the $form->data[''] array
<?php
$db = \JFactory::getDBO();
$query = "
SELECT Distinct 
  `I31`.`codmod`,
  `I31`.`desmod`
FROM `IDAT2410F` AS I24
  LEFT JOIN `IDAT3200F` AS I32 
    ON `I32`.`CODALL` = `I24`.`CODALL`
  LEFT JOIN `IDAT3100F` AS I31 
    ON `I31`.`CODMAR` = `I32`.`CODMAR` AND `I31`.`CODMOD` = `I32`.`CODMOD`
WHERE
`I24`.`ANNOXX` = 2004                          
  AND `I32`.`codmar` = '33'                                        
GROUP BY `I31`.`codmod`, `I31`.`desmod`
ORDER BY `I31`.`desmod`
";
$db->setQuery($query);
$form->data['data'] = $db->loadAssocList();
?>
Do the column names all need to be Upper case - is `I31`.`codmod` the same as `I31`.`CODMOD` ?

Bob
my
myky2k1 07 Apr, 2016
yes. is the same thing codmod or CODMOD.

Greyhead, you are my hero. I don't have enough words to thank you.

Last question. If you look in the query, you will see that ANNOXX = 2004 and CODMAR = 33, i need to pick these values, from 2 dropdowns, and build a third one with the result.
my
myky2k1 07 Apr, 2016
If you need, i can give you access to admin panel.
my
myky2k1 07 Apr, 2016
I've figured out something...

now, i only need to put on ANNOXX value and CODMAR value, 2 values from 2 dropdowns, on the same page.

How can i get those value in the same page?
my
myky2k1 07 Apr, 2016
thanks🙂
Gr
GreyHead 07 Apr, 2016
Hi myky2k1,

I'm not sure where you are getting the drop-down values from - if they are from a previous form page you can use
. . .
WHERE
`I24`.`ANNOXX` = '{$form->data['dropdown_a']}'                    
  AND `I32`.`codmar` = '{$form->data['dropdown_b']}' 
. . .

If it's in the same page then you could do it with Custom Javascript and an Ajax query back to a form event.

Bob
my
myky2k1 07 Apr, 2016
I've solved the issue.

You are a genius. Thanks thanks, many many thanks.😀
This topic is locked and no more replies can be posted.