Help with PHP MySQL percentage reports with loops?

[malakiahs]

I have to use a database with thousands of records all in just one table to create reports with percentages.

The table contains questions with ratings from 1 to 5.

 

The records in the database look something like these:

 

RecordQuestion1Question2... Question25

155...4

245...5

355...5

435...3

514...3

...[/td]

900023...2

 

 

As I mentioned before I need to create a report with percentages that will look like this.

 

Q#    Ratings of 1        Ratings of 2      ...      Ratings of 5   

1890(total) 9.8% (total) total/9000  ...  (total) total/9000

2(total) total/9000(total) total/9000  ...  (total) total/9000

3(total) total/9000(total) total/9000  ...  (total) total/9000

4(total) total/9000(total) total/9000  ...  (total) total/9000

5(total) total/9000(total) total/9000  ...  (total) total/9000

...

252050(total) 22.7%(total) total/9000...(total) total/9000

 

 

In order to obtain these results I use the following queries...

 

I first query the count of all the records...

 

$overall_total = "SELECT COUNT(*) as count FROM table_name";

$total_result = mysql_query($overall_total);

if ($total_result){

while ($row = mysql_fetch_array($total_result)){

$totalCount = $row['count'];

}

}

 

//Then I get the total of each specific question and each rating

 

$query = "SELECT

SUM(IF(Question1= '1',1,0)) as Question1_1,

SUM(IF(Question1= '2',1,0)) as Question1_2,

SUM(IF(Question1= '3',1,0)) as Question1_3,

SUM(IF(Question1= '4',1,0)) as Question1_4,

SUM(IF(Question1= '5',1,0)) as Question1_5

 

.

.

.

 

SUM(IF(Question25= '1',1,0)) as Question25_1,

SUM(IF(Question25= '2',1,0)) as Question25_2,

SUM(IF(Question25= '3',1,0)) as Question25_3,

SUM(IF(Question25= '4',1,0)) as Question25_4,

SUM(IF(Question25= '5',1,0)) as Question25_5

FROM table_name";

 

//Run the query

$result = mysql_query($query);

 

if ($result) {

 

while ($row = mysql_fetch_array($result, MYSQL_BOTH)){

 

//$totalCount variable comes from previous query

 

$Question1_1 = str_pad($row[Question1_1], 3, "0", STR_PAD_LEFT);

$Question1_1_percent = number_format((($Question1_1 / $totalCount) * 100), 2) ;

$Question1_2 = str_pad($row[Question1_2], 3, "0", STR_PAD_LEFT);

$Question1_2_percent = number_format((($Question1_2 / $totalCount) * 100), 2) ;

.

.

.

$Question25_1 = str_pad($row[Question25_1], 3, "0", STR_PAD_LEFT);

$Question25_1_percent = number_format((($Question25_1 / $totalCount) * 100), 2) ;

}

 

echo "

 

Q#    Ratings  of 1        Ratings  of 2      ...      Ratings of 3   

1$Question1_1 $Question1_1_percent $Question2_1 $Question2_1_percent  ...  ...

2$Question2_1 $Question2_1_percent(total) total/9000  ...  (total) total/9000

3(total) total/9000(total) total/9000  ...  (total) total/9000

4(total) total/9000(total) total/9000  ...  (total) total/9000

5(total) total/9000(total) total/9000  ...  (total) total/9000

...[td]

25Question25_1 Question25_1_percent(total) total/9000...(total) total/9000

 

";

 

As you might have already noticed this is a very inefficient way, because if questions are added or deleted, I will have to modify all of the code.  Therefore my questions is, is there a way of doing this more efficiently maybe with loops or arrays? Any help will be appreciated tremendously.

 

Thank you all in advance for taking your time in reading this and providing any help.

 

 

 

[Psycho]

As you might have already noticed this is a very inefficient way, because if questions are added or deleted, I will have to modify all of the code.  Therefore my questions is, is there a way of doing this more efficiently maybe with loops or arrays? Any help will be appreciated tremendously.

 

You are correct, that is inefficient. But he solution has nothing to with loops or array - you should never run queries in loops and you should not need to run that many queries. the problem is your data structure. You should have a table for the records - which holds just the basic data for the records. You example only shows a record ID, but I would assume you might have other data such as user_id, date, etc. But you should NOT store the results of the questions. You should do that in a separate table where each record is the result of ONE question

 

results

record_id | question_id | result
record_id | question_id | result
1           1             5
1           2             5
1           25            4
2           1             4
2           2             5
2           25            6

 

You can then easily create one query to get the count and another to get the count for each number of each question.

[malakiahs]

Thank you Psycho for your time and advice.

 

Unfortunately, this data was collected some time ago and I was asked to work with it.  If loops and arrays are not the answer, is there a way to reproduce what is needed?

 

Thank you, once again.

 

 

[malakiahs]

Or should I restructure the data within the database?

[Psycho]

The best approach would be to restructure the database. But, that would mean having to go back and rewrite any functionality that currently writes/reads/deletes from the affected tables. but, that is the best solution since it will remove the complexity that you now face. If you stick with the current structure then I don't see any easy solutions that don't require a lot of loops and multiple queries.

 

But, just to show you how easy it would be to get the data you need with the proper structure, let's assume you have a table for each result as I showed above. You could run just one query such as

SELECT question_id, result, COUNT(result) as count
FROM results
ORDER BY question_id, result
GROUP BY question_id, result

 

That should produce a results set such as this

question_id | result | count
1             1        10
1             2        13
1             3        8
1             4        3
1             5        22
2             1        3
2             2        10
2             3        6
2             4        13
2             5        4
.
.
.

 

So, with that single query you get all the data you need. From that you can tell that Question 1 had 10 responses for result 1, 13 responses for result 2, etc.

[malakiahs]

I decided to use loops along with arrays for now.  I think for now this is the best solution.  Thank you for your help Psycho!