PHP script that can calculate average of columns in MySQL database

Question

I need some help on how to code my php script so that it will find the average of six columns in mysql database and store the new results in a new column inside the mysql database.

For example

the MSQL Database has the following columns

 Name    EXAM1       EXAM2     EXAM3    EXAM4    EXAM5

Then the following records are entered into the dataabse

 Name    EXAM1       EXAM2     EXAM3    EXAM4    EXAM5


Eric     10          20        10        20       10

Then the PHP script will generate this result

 Name    EXAM1       EXAM2     EXAM3    EXAM4    EXAM5     AVERAGE


Eric     10          20        10        20       10         14

So that in the MYSQL database, the records will show just like the PHP script results.

Any help?

Thanks.

Revise your design. You should have three columns: user_id, exam_id, score — Strawberry, Feb 05 '17 at 08:35
I agree with @Strawberry. Also, you don't need PHP to calculate averages. See here: http://stackoverflow.com/questions/14787561/find-the-average-of-two-combined-columns-in-sql — Stuart Wagner, Feb 05 '17 at 08:38

score 1 · Answer 1 · answered Feb 05 '17 at 09:14

You can do it direct in SQL like

UPDATE yourTable set AVERAGE = (EXAM1+EXAM2+EXAM3+EXAM4+EXAM5)/5;

Or you automatic generate the AVERAGE if you define your table like this:

CREATE TABLE yourTable (
  title VARCHAR(32) DEFAULT NULL,
  exam1 DOUBLE,
  exam2 DOUBLE,
  exam3 DOUBLE,
  exam4 DOUBLE,
  exam5 DOUBLE,
  average DOUBLE GENERATED ALWAYS AS ((exam1 + exam2 + exam3 + exam4 + exam5) / 5 ) STORED
);

sample

mysql> CREATE TABLE yourTable (
    ->   title VARCHAR(32) DEFAULT NULL,
    ->   exam1 DOUBLE,
    ->   exam2 DOUBLE,
    ->   exam3 DOUBLE,
    ->   exam4 DOUBLE,
    ->   exam5 DOUBLE,
    ->   average DOUBLE GENERATED ALWAYS AS ((exam1 + exam2 + exam3 + exam4 + exam5) / 5 ) STORED
    -> );
Query OK, 0 rows affected (0,02 sec)

mysql> INSERT INTO yourTable (title, exam1, exam2, exam3, exam4, exam5)
    -> VALUES('test1',1,1,22,33,44),('Test2',333,24,11,45,97);
Query OK, 2 rows affected (0,00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM yourTable;
+-------+-------+-------+-------+-------+-------+---------+
| title | exam1 | exam2 | exam3 | exam4 | exam5 | average |
+-------+-------+-------+-------+-------+-------+---------+
| test1 |     1 |     1 |    22 |    33 |    44 |    20.2 |
| Test2 |   333 |    24 |    11 |    45 |    97 |     102 |
+-------+-------+-------+-------+-------+-------+---------+
2 rows in set (0,00 sec)

mysql>

thanks for the help. However, I get this error when I tried to follow the code you gave above. == #1064 - 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 'GENERATED ALWAYS AS ((exam1 + exam2 + exam3 + exam4 + exam5) / 5 ) STORED )' at line 8 — Zolly Zollyzo, Feb 13 '17 at 20:25

PHP script that can calculate average of columns in MySQL database

1 Answers1