Forums

Hi,

I've been trying to add a column with the average grade of the students but I can't figure out how to do it.

The exams table contains 3 columns with the grades (calificacion1, calificacion2, calificacion3), but the tricky part is that not every student has all 3 grades (some grades are NULL), and if they are NULL then they shouldn't be counted.

What I have tried is:
1- At the model tab, I tried adding the fields I need plus the calculated one in the following way:

Fileds:

 `examen.cf_id`, `examen.estudiante`, `examen.fecha`,(SUM(ifnull(calificacion1,0))+SUM(ifnull(calificacion2,0))+SUM(ifnull(calificacion3,0)) ) / (COUNT(calificacion1)+Count(calificacion2)+COUNT(calificacion3)) as `examen.promedio`

But the query gets completely mishmashed, here is the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) ) / (`examen`.`COUNT(calificacion1`)+Count(calificacion2)+COUNT(calific' at line 1 SQL=SELECT `examen`.`cf_id` AS `examen.cf_id`, `examen`.`estudiante` AS `examen.estudiante`, `examen`.`fecha` AS `examen.fecha`, `examen`.`(SUM(ifnull(calificacion1` AS `examen.(SUM(ifnull(calificacion1`, `trabajolibre`.`0))+SUM(ifnull(calificacion2` AS `trabajolibre.0))+SUM(ifnull(calificacion2`, `trabajolibre`.`0))+SUM(ifnull(calificacion3` AS `examen.0))+SUM(ifnull(calificacion3`, 0)) ) / (`examen`.`COUNT(calificacion1`)+Count(calificacion2)+COUNT(calificacion3)) as `examen.promedio` AS `examen.COUNT(calificacion1` FROM `jos_chronoforms_data_examen` AS `examen` LIMIT 30 OFFSET 180

2- Based in some posts I saw I tried also the following code (for simplicity I removed the id and the name of the student, until I find a working solution):

Fileds:

<?php return array( "((SUM(ifnull(calificacion1,0))+SUM(ifnull(calificacion2,0))+SUM(ifnull(calificacion3,0)) ) / (COUNT(calificacion1)+Count(calificacion2)+COUNT(calificacion3)))" => "examen.promedio");

But the error is similar:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) ) / (`examen`.`COUNT(calificacion1`)+Count(calificacion2)+COUNT(calific' at line 1 SQL=SELECT `examen`.` "examen.promedio"); AS `examen.COUNT(calificacion1` FROM `jos_chronoforms_data_examen` AS `examen` LIMIT 30 OFFSET 180

3- So I tried by calculating the average by using the PHP Functions at the Front List Settings but also didn't work:

PHP Functions:

examen.promedio: {if( is_null($row["examen"]["calificacion1"])){$divisor = 0; $firstCalif = 0;} else {$divisor = 1; $firstCalif = $row["examen"]["calificacion1"];}; if ( is_null($row["examen"]["calificacion2"])) {$divisor = $divisor; $secondCalif = 0;} else {$divisor++; $secondCalif = $row["examen"]["calificacion2"] ;} if ( is_null($row["examen"]["calificacion1"])){ $divisor= $divisor; $thirdCalif = 0; } else { $divisor++; $thirdCalif = $row["examen"]["calificacion3"] ;} $promedio = ($firstCalif + $secondCalif + $thirdCalif) / $divisor; return $promedio};

but the average column is empty, I did enable error reporting in my .htaccess but I don't see any error messages.

Any suggestions would be greatly appreciated.

Thanks an advance

Am interested in your approach as I have something a little simpler, but I can't get it to work. Maybe you can shed some light.

In my table I have one field named "school" and another named "other_school". The form uses a radio box from which the user selects a school ("school"); one choice is "Other" if the school isn't listed. If that's selected, a text box is opened allowing the user to enter the name of a different school ("other_school").

In my CC connection in the Columns list of the Front List Settings, I have an "academic.school:School" column (academic is the model ID). If a user selected "Other" for school, that's shown in the list. Instead, I want to show what the user entered for "other_school".

Following your example, here's what I tried doing.
I created an "academic.institute:School" column - this would show either the user selected school or the school entered by the user.
In the PHP Functions box I entered the following code. It sets $institute to either the school selected or the one entered if "Other" was selected.

  academic.institute:
   if($row["academic"]["school"] == "Other") {
      $institute=$row["academic"]["other_school"];
   } else {
      $institute=$row["academic"]["school"];
   }
   return $institute;
  

Instead of working, I get a number of error statements:
Parse error: syntax error, unexpected end of file in... on line 2
Parse error: syntax error, unexpected 'else' (T_ELSE) in .. on Line 1.
Parse error: syntax error, unexpected '}' in ... on line 2
and the School column is blank in the listing.

If you can see what I'm doing wrong, I'd appreciate you're letting me know.

Hi,

Your code seems fine to me, but I would suggest typing it into one line -you don't need to change the code it self just try removing all the new lines keeping it all as along string.

If that doesn't work, please post the exact error message, that would help identifying the error.

Regards
Ilan

Thanks, that worked.
So much for entering code in a format that's easily readable.
I appreciate the help.
JMahun

I'm guessing that the process of parsing multi line code on the field definitions could have greatly complicated the development of CC.

I'm happy to hear that that worked.

Regards
Ilan