mysql add columns and write the result in the same table

Question

I have a student attendance table in mysql 19BE1attendance. The columns are number, name, total, Week_2, Week_1

Week_X is either 1 or 0

Of course the number of weeks will increase as the term goes on.

The column total should be the sum of the Week_X values.

This does what I want, all the values are added nicely in the third column total:

SELECT Week_2, Week_1, (Week_1 + Week_2) as total FROM 19BE1attendance;

EDIT: I made php which can import a .csv into a table. That is how the attendance values will get into the attendance table.

Now I have total. Assume I have another table totals with 3 columns number name totals

How do I get total in the column totals for each student?

Or, alternatively, add the attendance values on the fly and output them in a webpage with a foreach() when a student wishes to consult his or her scores.

I can already output fictitious score values using php.

(number is the student number and is a unique identifier for each student)

Answer 1

You should really consider changing your table design to this:

name | week_no | total

The week_no column optionally could also be a date or a timestamp, from which you could easily extract the week number. With this design, if you want totals for each name, you need only use:

SELECT name, SUM(total) AS total
FROM yourTable
GROUP BY name;

The problem with your current design is that adding weeks as new columns does not scale well at all. In addition, you should probably avoid storing the grand total as a separate column, as it is derived data. Should the underlying totals change over time, your suggested grand total column could become out of date, requiring frequent updates.

Answer 2

I'm affraid there is no easy solution for your problem, because the layout of the table is designed poorly. I would strongly suggest to refactor this table if you can. A more appropiate layout would be something like:

CREATE TABLE attendance (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Entry identifier',
 name VARCHAR NOT NULL COMMENT 'The persons name',
 year INT UNSIGNED NOT NULL COMMENT 'Year of week in which attendance is noted',
 week     INT UNSIGNED NOT NULL COMMENT 'Weeknumber for which attendance is noted',
 is_present TINYINT UNSIGNED NOT NULL COMMENT 'Whether person was present (1) or not (0)'
) ENGINE Innodb;

Using this layout you could make a very simple SELECT statement getting the overall attendance stats per person.