41 Posts
RatingAction
5 years ago
Topic

I've written a script to populate certain fields on the fly (see this thread). This script achieves what I want it to:

$db = JFactory::getDbo();
$query = $db->getQuery(true);


$query
  ->select('AVG(bc) AS value_sum') 
  ->from($db->quoteName('#__cck_store_item_content'))
  ->where($db->quoteName('actionheld')." = ".$fields['art_id']->value);

$db->setQuery($query);
$result = $db->loadResult();

$result = round($result);
$fields['bc_avg']->value = $result;

Since I need about 20 of these calculations I've tried to come up with a script that cycles through arrays holding components of the calculations and field names I want to use. However, I can't get the below script to work:

$db = JFactory::getDbo();


$calc = array( "SUM", "AVG", "MAX", "MIN" );
$fieldcalc = array ( "total", "avg", "max", "min" );
$stats = array( "bc", "s5", "fsk", "ex", "ga", "sb" );
$i = 0;
$j = 0;


while ( $i <= 3 ):
  while ( $j <= 5 ):
    $query = $db->getQuery(true);
    $query
      ->select("'".$calc[i]."(".$stats[j].") AS value_sum'") 
      ->from($db->quoteName('#__cck_store_item_content'))
      ->where($db->quoteName('actionheld')." = ".$fields['art_id']->value);
    $db->setQuery($query);


    $result = $db->loadResult();
    $result = round($result);
    $fields["'".$stats[j]."_".$fieldcalc[i]."'"]->value = $result;
    $j++;
  endwhile;
  $i++;
endwhile;

Between php syntax, Joomla syntax, SEBLOD syntax and MySQL syntax I am not exactly sure where I am going wrong. Any ideas?

Get a Book for SEBLOD
1283 Posts
Bucklash
5 years ago
4
Level 1
$stats[j] should be $stats[$j]???
$calc[i] should be $calc[$i]???
41 Posts
RatingAction
5 years ago
3
Level 2

Hey, mate! Good catch, have changed this for all instances. Still the same result unfortunately, though, so seems to have other issues, too. Code now reads:

$db = JFactory::getDbo();
$calc = array( "SUM", "AVG", "MAX", "MIN" );
$fieldcalc = array ( "total", "avg", "max", "min" );
$stats = array( "bc", "s5", "fsk", "ex", "ga", "sb" );
$i = 0;
$j = 0;

while ( $i <= 3 ):
  while ( $j <= 5 ):
    $query = $db->getQuery(true);
    $query
      ->select("'".$calc[$i]."(".$stats[$j].") AS value_sum'")  
      ->from($db->quoteName('#__cck_store_item_content'))
      ->where($db->quoteName('actionheld')." = ".$fields['art_id']->value);

    $db->setQuery($query);
    $result = $db->loadResult();
    $result = round($result);

    $fields["'".$stats[$j]."_".$fieldcalc[$i]."'"]->value = $result;
    $j++;
  endwhile;
  $i++;
endwhile;
1283 Posts
Bucklash
5 years ago
2
Level 3

So does $result have a result?

Is there defo a value passed to $fields?

When I try this sort of stuff (as am no guru) I find that doing things like this helps for some reason:

// NOT THIS:
$fields["'".$stats[$j]."_".$fieldcalc[$i]."'"]->value = $result;

// THIS:
$field_name = $stats[$j]."_".$fieldcalc[$i];
$fields[$field_name]->value = $result;


But that might be me covering up some crappy coding on my part....
41 Posts
RatingAction
5 years ago
0
Level 4

Sorry for the late reply, I was travelling in Russia the last few days.

Your response definitely helped. I get the first row of results correctly now (the sums). The other calculations still don't return a result, yet. I'll continue to work on this and will post the final code here once I've got it to work correctly.

41 Posts
RatingAction
5 years ago
0
Level 4

Managed to get it to work now by replacing the while-loops with foreach-loops:

$db = JFactory::getDbo();
$calc = array("SUM","AVG","MAX","MIN");
$fieldcalc = array ("total","avg","max","min");
$stats = array("bc","s5","fsk","ex","ga","sb");
$i = 0;
$j = 0;

foreach ($calc as $i => $calc_value) {
  foreach ($stats as $j => $stats_value) {
    $query = $db->getQuery(true);
    $stat_name = $calc_value."(".$stats_value.")";

    $query
      ->select($stat_name) 
      ->from($db->quoteName('#__cck_store_item_content'))
      ->where($db->quoteName('actionheld')." = ".$fields['art_id']->value);

    $db->setQuery($query);
    $result = $db->loadResult();
    $result = round($result);

    $field_name = $stats_value."_".$fieldcalc[$i];
    $fields[$field_name]->value = $result;
  }
} 

Thanks a lot for pointing me in the right direction to solve the issue of getting the field calls right, Bucklash! This code is now way more efficient than the 22 separate BeforeRender fields I used before. And it feels way more elegant. :-)

Get a Book for SEBLOD