CODEWITHSUNDEEP
phpdatabaseperformancemethodologystatistics
nickifrandsen
nickifrandsen

Reputation: 433

Statistics based on MySQL and PHP

I'm struggling with a problem I hope some of you might be able to help me with.

I got the following table structure for a table which contains lots of actions. I then would like to make a php script which generates some statistics. 

+---------------+-------------+------+-----+-------------------+----------------+
| Field         | Type        | Null | Key | Default           | Extra          |
+---------------+-------------+------+-----+-------------------+----------------+
| id            | int(11)     | NO   | PRI | NULL              | auto_increment |
| MemberID      | int(11)     | NO   |     | NULL              |                |
| MemberNumber  | int(11)     | NO   |     | NULL              |                |
| Status        | varchar(20) | NO   |     |                   |                |
| ClubID        | int(11)     | NO   |     | NULL              |                |
| Clubtype      | varchar(5)  | NO   |     | NULL              |                |
| Time          | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| Gender        | varchar(10) | NO   |     |                   |                |
+---------------+-------------+------+-----+-------------------+----------------+

I'm wondering if it's best to let php or mysql manipulate the data. I would like to get some stats based on Time e.g year,month,week and some stats based on Clubtype e.g FK,UK and finally some stats based on Gender e.g Male,Woman. So is it best to work with count() in the mysql queries or is it better to get all the data and the let php generate the stats.

I hope this makes sense. Thx for the help :)

EDIT: To explain my plan further. I would like to generate for every year grouped by month and for each month I want to get the

count(Status) total

count(Status) where Clubtype = $value

count(Status) where Gender = $value

furthermore for each month i want to get the stats grouped by week and finally I want to get the stats for each week grouped by day. These stats makes use of same count as above.

I hope this gives an better idea of what i want to do.

Upvotes: 3

Views: 2883

Answers (1)

dqhendricks
dqhendricks

Reputation: 19251

Use MySQL. It will be able to calculate and organize things much faster due to indexing. You will have to make heavy use of GROUP BY and the math functions.

Upvotes: 5

Related Questions