PHP Matrix - with Cumulative

[FooKelvin]

Hello, I have stuck in here. Let me explain the background.

Each Fiscal Year have Q1 until Q4. Each Quarter have different type form name. In a quarter may have more than 4 forms.

So, each form is to track the points that the participant earn.

One form allow more than one participant. One participant can participate in multiple form. 

So each participant have a total marks for each form.

 

Please Have A Look The Attachment.

 

Right now, I need to design a report to show Top 20 highest score participant. It can be cumulative from Q1 + Q2 + Q3 + Q4.

Mean that, for example:-

In This case. The data is less, so i highlighted Top 3 highest score.

 

+---------------+

|Bill       |   41 |

|Aaron  |   36 |

|Gates  |   27 |

+---------------+

 

To produce this filter, the only way i can think off is to sum all the data in a database by name??

is that a good way? or is there any better way?

 

Thank You

Edited September 1, 2015 by FooKelvin

[scootstah]

Well, you need to model this for a relational database and not for a spreadsheet. Here is a basic schema example:

category
----------
id | name
----------
1  | attendance
2  | contribution


question
----------
id | question
----------
1  | Attend more than 5 times?
2  | Attend more than 20 times?


form
----------
id | name   | quarter | year
----------
1  | Form 1 | 1       | 2015
2  | Form 2 | 2       | 2015
3  | Form 3 | 3       | 2015
4  | Form 4 | 4       | 2015


user
----------
id | name
----------
1  | James
2  | Gates


result
----------
id | form_id | category_id | question_id | user_id | points
----------
1  | 1       | 1           | 1           | 1       | 2
2  | 1       | 1           | 1           | 2       | 3

Then you can use a simple query to get the number of points for each user:

select sum(points) from result group by user_id;

You could additionally group by category, question, or form.

[Barand]

I'd suggest this schema

       +------------+                     +-------------+                          +-------------+ 
       | category   |                     | form        |                          | user        |
       +------------+                     +-------------+                          +-------------+ 
  +----| catid (PK) |                  +--| formid (PK) |                      +---| userid (PK) | 
  |    | catname    |                  |  | year        |                      |   | name        | 
  |    +----+-------+                  |  | quarter     |                      |   +-------------+ 
  |                                    |  +-------------+                      |             
  |                                    |                                       |             
  |                                    |                                       |             
  |                                    |                                       |            
  |    +----------+                    |                    +--------------+   |           
  |    | question |                    |                    | score        |   |           
  |    +----------+                    |                    +--------------+   |             
  |    | qid (PK) |-------------+      |                    | scoreid (PK) |   |             
  |    | qtext    |             |      +-------------------<| formid       |   |             
  +---<| catid    |             |                           | userid       |>--+                 
       | points   |             +--------------------------<| qid          |                
       +----------+                                         | points       |                 
                                                            +--------------+