adding specific results from a db

[dkemas]

I have tables that store scores of 1-3. i.e.

table 1
id | userid | t1-score1 | t1-score2 | t1-score3 | t1-score4 | t1-score5 | t1-score6 | t1-score7 | t1-score8 | t1-score9 | t1-score10

table 2
id | userid | t2-score1 | t2-score2 | t2-score3 | t2-score4 | t2-score5 | t2-score6 | t2-score7 | t2-score8 | t2-score9 | t2-score10

there are 8 of these tables.

The userid links back to the users table so we know sho scored what.

I have been asked to create a report for 100 random people to calculate

total number of 1's
total number of 2's
total number of 3's

I'm not sure how to go about this, I guess I need to

generate 100 random id's from the users table

use this id as userid in the score table and grab all scores from all 8 tables for each person

count the 1's, 2's and 3's. How do I count the number of 1's etc?

Can someone help?

Thanks

 

[jpadie]

can you elucidate?

when you say 'the number of ones' do you mean:

the number of times for each user that the value of tXscoreX equals 1?

by the way, the eight tables would be better articulated as a single table like so

id (optional)
userID
scoreNumber [set of 1 to 10]
scoreType [set of 1 to 8]
score [arbitrary integer/number]

that would make the answer to your question quite a lot easier.

 

[dkemas]

A typical set of answers for person with id 999 would be

id | userid | t1-score1 | t1-score2 | t1-score3 | t1-score4 | t1-score5 | t1-score6 | t1-score7 | t1-score8 | t1-score9 | t1-score10

1 | 999 | 3 | 3 | 2 | 3 | 3 | 1 | 3 | 2 | 3 | 1

so they have a set of data similar to this for all 8 tables

I need to count from all 8 tables how many times the answer 1 is given, how many times the answer 2 is given and how many times the answer 3 is given.

I then need to do this on a sample of 100 people :-
The tables cannot be changed unfortunately.

 

[jpadie]

not tested at all.
There is also a (far) more optimal method of doing this predominantly in mysql. But since you are asking in the php forum, here is a php solution.
I do repeat though that your table structure is flawed for the purpose of doing _any_ statistical analysis on it and is very far from normalised.

the code presumes that the tables are named tX-scores where X is a number between 1 and 8. It also presumes that the individual columns are called tX-scoreY where X is a number between 1 and 8 and Y is a number between 1 and 10.

<?php
$sql = 'SELECT DISTINCT userid from t%s-scores';
$ids = array();
//get all userids
for($i=1; $i<=8; $i++):
	$query = sprintf($sql, $i);
	$result = mysql_query($query) or die(mysql_error());
	while($row = mysql_fetch_assoc($result):
		$ids[] = $row['userid'];
	endwhile;
endfor;
//make the userids unique
$uniqueIDs = array_unique($ids, SORT_REGULAR);

//now randomly extract 100 keys (if less than 100 uniques then select all)
$randomArray = array();
if(count($uniqueIDs) > 100):
	$randomKeys = array_rand($uniqueIDs,100);
	foreach($randomKeys as $key):
		$randomArray[] = $uniqueIDs[$key];
	endforeach;
else:
	$randomArray = $uniqueIDs;
endif;
$uniqueIDs = $ids = array(); //save memory
function escape_enquote($value){
	return "'" . mysql_real_escape_string($value) . '"';
}
$randomArray = array_map('escape_enquote', $randomArray);
$sql = "SELECT * from t%s-scores where userid IN (%s)";

/* create an array to hold the score counts */
/* add/subtract from the array to capture different values */
$scores = array(1=>0,2=>0,3=>0,4=>0,5=>0,6=>0,7=>0,8=>0,9=>0,10=>0);

for($i=1; $i<=8; $i++):
	$query = sprintf($sql, $i, implode(',',$randomArray);
	$result = mysql_query($query) or die(mysql_error());
	while($row = mysql_query($result):
		for($answer = 1; $answer<= count($scores); $answer++):
			for($j=1; $j<=10; $j++):
				if($answer == $row['t'.$i.'-score' . $j]):
					$scores[$answer]++;
				endif;	
			endfor;
		endfor;
	endwhile;
endfor;

echo '<table><thead><tr><th>Score></th>';
for($i=1;$i<=count($scores);$i++):
	echo '<th>' . $i . '<th>';
endfor;
echo '</tr></thead>';
echo '<tbody><tr><th>Count</th>';
echo '<td>' . implode('</td><td>', $scores) .'</td></tr></tbody></table>';
?>

 

[vacunita]

While jpadie has provided a functional answer I will echo and stress what he said, that your table structure is no where near being conducive to statistical analysis the way it is set up.

There's really no reason to have separate tables for what is essentially the same type of data.

You should look into normalizing and having a single table of results for easier querying and analysis.

As it stands the more complex the analysis gets the less likely it is you are going to be able to find a way to actually do it.



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech