Time Calculation [Solved]

[jeanl]

Hi Hugh

Thank you very much for the assistance. Where would I add the scripts? On the form or the list, or the element?

 

[mamies]

The form.

You want it to perform the calculation when you enter the data.

 

[jeanl]

Thank you very much. I will try this this afternoon.

 

[jeanl]

Hello Hugh

I pasted the script in the form linked to list two. When I save the form, I simply get a white screen. See attached.

Can you advise?

 

[jeanl]

In general, to handle an array of values you want to match in a query, you use IN() rather than =, and "implode" the array into a comma seperated list. The PHP explode() and implode() functions are very useful, allowing you to 'explode' a delimited string (i.e. list of numbers separated by commas) into an array, or implode an array into a delimited string.

So an array of integers can be imploded into a comma separated string with implode(',', $service), which will produce a string like "1,2,3,4,5". Which can then be used in the IN() part of the query.

Like this:

$db->setQuery("SELECT duration_total FROM services WHERE id IN(" . implode(',', $service) . ")");

Also ... when doing straight forward summing, quicker and easier to let MySQL do that rather than doing it in PHP ...

$db->setQuery("SELECT SUM(duration_total) AS total FROM services WHERE id IN(" . implode(',', $service) . ")");

Also ... although not required, it's good to get in the habit of building queries using the "query builder" syntax. Not so important for simple queries like this, but a definite advantage when you start getting in to building more complex queries.

$query = $db->getQuery(true);
$query->select('SUM(duration_total) AS total ')
  ->from('services')
  ->where('id IN(' . implode(',', $service) . ')');
$db->setQuery($query);
$calc = $db->loadResult();

NOTE - this assumes $service is numbers (which it is). When dealing with an array of string you want to use in an IN(), you have to go through another step to quote the strings.

-- hugh

Hugh, do I need to add all three sets of code or only the last portion?

 

[cheesegrits]

Hugh, do I need to add all three sets of code or only the last portion?

Just the last, I was showing you progressively better ways of doing the same thing.

However, it was just a fragment, you still need to set up the $service variable before it, and set the form data after. That was just the bit to get the sum

Hugh

 

[cheesegrits]

So the complete code, building on what Matthew gave you, would be ...

$service = $formModel->formData['jobcard___services_raw'];
$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('SUM(duration_total) AS total ')
   ->from('services')
   ->where('id IN(' . implode(',', $service) . ')');
$db->setQuery($query);
$calc = $db->loadResult();
$formModel->updateFormData('jobcard___test_duration', $calc, true);

I'm not promising this is the code you need, as I don't know for sure what y'all are doing - I just saw Matthew's comment about $service being an array.

-- hugh

 

[jeanl]

Hugh

Thank you. So I copy this into the php plugin on the second form and set it to process on save?

 

[mamies]

Use onBeforeStore

Also that looks perfect Hugh.

Thanks,

 

[jeanl]

Hugh

Thank you. Your code works perfectly. Thank you also Matthew for your kind assistance. I appreciate it very much. I now get the result in minutes which I needed.

Jean