PHP Events list plugin - Save Calc Value

[nbradshaw]

Has anyone successfully used the PHP Events list plugin to update the value of an element/column with a calculated value in the db? (without user interaction on the form).

I have some calculations that I need to make but want those to be made on the db side (before the list is shown).

Here is the plugin docs - but I don't see an example of my use case...but thinking it could be done?
http://fabrikar.com/forums/index.php?wiki/php-events-list-plugin/


Here is the calc element that I am using - I would just want this moved into the event list to save it to the db onload:

  //Calc Element Query - Single Return
    $cc27 = '{gci_aggregate_2___CATCODE27}';
    $db = JFactory::getDbo();
    $query = "select `description` from `cgi_annex1_cc27_codes_description` WHERE cc27 = " . $db->quote($cc27);
    $db->setQuery($query);
    $cc27_desc = $db->loadResult();
    return $cc27_desc;

 

[lousyfool]

If I understand you right, you could do so with the PHP Events list plugin. There's a somewhat similar example in the Wiki here: https://fabrikar.com/forums/index.p...-date-element-to-show-how-many-days-old-it-is
You see, you'd need to iterate through the list's records, and in your case (if you want to actually change DB fields) you'd need to run an "update" DB query instead of the code in the example.

However, the calc element is generally able to run on list load as well, eventually doing what you want. Just read the Wiki on it carefully and check out the available settings, particularly "Only Calc on Save".

In any case, be aware of the overhead any of this may create, especially for lists with many records.

 

[lousyfool]

Just thinking... perhaps you should also look at the option of using a MySQL view instead, which you could base a "new" Fabrik list on. Other than being able to achieve your "calc" there as well, it might be the most efficient way of doing so.

 

[juuser]

If you still need to do it in list php events plugin then something like this should do in "onPreLoadData":

$mydb = JFactory::getDBO();
$mydb->setQuery("SELECT a.id, a.CATCODE27, b.description FROM gci_aggregate_2 AS a
LEFT JOIN cgi_annex1_cc27_codes AS b ON a.CATCODE27 = b.cc27");
$results = $mydb->loadObjectList();

foreach ($results as $result) {

  $mydb->setQuery("UPDATE gci_aggregate_2 SET your-element-name-to-be-updated = ".$mydb->Quote($result->description)." WHERE id = ".$mydb->Quote($result->id));
  $mydb->execute();
 
}

Do not forget to change "your-element-name-to-be-updated" in the query to match yours.

In most cases with these simple updates you will not notice list loading time to increase (of course it also depends in server setup and resources).

P.S. Make sure you have a backup before testing these kind of things!