2. PHPRunner General questions
  3. [SOLVED] Calculate a field before displaying the table
This topic is locked
[SOLVED]

Calculate a field before displaying the table

 		6/16/2022 21:24:21
PHPRunner General questions
gehrenfeld author

I have a table passing and the fields att,comp,yards,td,int,rate
I want to use this formual to calculate the passing rating

function cal()
{
if ($att ==0){
$att = 1;
}
$a = ($comp / $att - .3) 5;
if ($a > 2.375) {
$a = 2.375;
}
$b = ($yards / $att - 3) .25;
if ($b > 2.375) {
$b = 2.375;
}
$c = ($td / $att) 20;
if ($c > 2.375) {
$c = 2.375;
}
$d = 2.375 - ($int / $att 25);
if ($d > 2.375) {
$d = 2.375;
}
return round((($a + $b + $c + $d) / 6) * 100, 1);
}

How can I update the rate field before displaying the table using this formual?
mbintex 6/17/2022

Why not calculating your value right in the SQL query? Then you would have your calculated value as a normal field for all list pages and view pages plus print pages without any overhead. Something like

Select
a,
b,
4*b+a as c
from test
gehrenfeld author 6/17/2022

I was able to get this to work.
SELECT Att,comp,PassYDS,TD,Inter, IF((comp/Att - .3)5<2.375,(comp/Att - .3)5,2.375) AS a,
IF((PassYDS/Att-3).25<2.375,(PassYDS/Att-3).25,2.375) AS b,
IF((TD/Att)20<2.375,(TD/Att)20,2.375) AS c,
IF(2.375-(Inter/Att25)<2.375,2.375-(Inter/Att25),2.375) AS d
FROM passing;

What does not work is this
((a+b+c+d)/6)*100 as r
mbintex 6/19/2022

You have to declare the results of a, b and c as variables before you can calculate with these

https://www.mysqltutorial.org/mysql-variables/
gehrenfeld author 6/19/2022

I got it to work.

SELECT *
, ((a+b+c+d) / 6) * 100 as r
FROM ( SELECT Att
, Cmp
, PassYDS
, TD
, `INT`
, (Cmp/Att - .3) * 5 AS a
, (PassYDS/Att - 3) * .25 AS b
, (TD/Att) * 20 AS c
, 2.375-(`INT`/Att*25) AS d
FROM passing
) AS data