Jump to content
FooKelvin

PHP Matrix - with Cumulative

Recommended Posts

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

post-179514-0-39267000-1441119762_thumb.png

post-179514-0-99557000-1441119797_thumb.png

Edited by FooKelvin

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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       |                 
                                                            +--------------+     

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.