Calculate average from form fields
I have a form that consists of rating in 8 different categories. Everything is in the same table, so no relationship. The fields are 'Kat1Rating', 'Kat2Rating' up to 8. Now I want to auto-generate an average rating from these categories, which should be displayed on the detail- and results-page.
On the Internet I found a solution that was to create a new query on the detail-page. This turned out to work perfectly, on the detail page. But since it's in a separate query, it doesn't work on the results-page. Even if I re-create the same query there, I get the same average on all records.
Unfortunately, it is a rather advanced query, and I do not know how to incorporate it in the original query created by DA. Unfortunately, Query builder does not seem to be able to help me here.
Here is the queries...
Average query on detail-page (this is working on the detail-page):
<?php
$rsAverage = new WA_MySQLi_RS("rsAverage",$matematikSverige,1000);
$rsAverage->setQuery("SELECT (CAST(AVG(`kat1Betyg`+`kat2Betyg`+`kat3Betyg`+`kat4Betyg`+`kat5Betyg`+`kat6Betyg`+`kat7Betyg`+`kat8Betyg`) /8 AS DECIMAL(10,1))) AS average FROM laromedel");
$rsAverage->execute();
?>
---
Query on results-page generated by DA:
<?php
$WADAlaromedel = new WA_MySQLi_RS("WADAlaromedel",$matematikSverige,1000);
$WADAlaromedel->setQuery("SELECT id, bild, laromedelsnamn, arskurser FROM laromedel");
$WADAlaromedel->execute();
?>
---
Query on detail-page generated by DA:
<?php
$WADAlaromedel_details = new WA_MySQLi_RS("WADAlaromedel_details",$matematikSverige,1);
$WADAlaromedel_details->setQuery("SELECT laromedel.id, laromedel.laromedelsnamn, laromedel.bild, laromedel.arskurser, laromedel.utvecklare, laromedel.pris, laromedel.adaptivt, laromedel.heltackande, laromedel.testad, laromedel.kat1Betyg, laromedel.kat1Kommentar, laromedel.kat2Betyg, laromedel.kat2Kommentar, laromedel.kat3Betyg, laromedel.kat3Kommentar, laromedel.kat4Betyg, laromedel.kat4Kommentar, laromedel.kat5Betyg, laromedel.kat5Kommentar, laromedel.kat6Betyg, laromedel.kat6Kommentar, laromedel.kat7Betyg, laromedel.kat7Kommentar, laromedel.kat8Betyg, laromedel.kat8Kommentar, former.`text` AS former_text, avsettfor.`text` AS avsettfor_text FROM laromedel LEFT OUTER JOIN former ON laromedel.form_id = former.id LEFT OUTER JOIN avsettfor ON laromedel.avsettfor_id = avsettfor.id WHERE laromedel.id = ?");
$WADAlaromedel_details->bindParam("i", "".($_GET['id']) ."", "-1"); //WAQB_Param1
$WADAlaromedel_details->execute();
?>
Maybe I'm overworking. Is there any easier way? Grateful for help.
