MySQL to Array Coding Help

Hello,

My query is not working correctly and I wanted to know if anyone can help.
My database has the following structure:
answerid (Primary Key), sid, aid,answervalue

There are 30 rows per sid.

Those 30 rows are generated by the answers given on a survey.
Each question has a group assigned to it.
Group 1 - 7 Questions
Group 2 - 7 Questions
Group 3 - 11 Questions
Group 4 - 5 Questions

What I need this script to do:
For each group, I need the value from the following calculation:
$calpha = (($qcount/($qcount-1))*(1-($varsum/$stdevtotal)));

These functions call out in this calculation need the following inputs:
1. For each SID there needs to an array, which contain the answervalues for the associated questions. The end result should be 4 arrays contain the answervalues, one for each group for each SID.
2. For each AID there needs to be an array that contains all answervalues in the database. The end result should be 4 arrays (one for each group) with sub-arrays (one for each question). Each sub-array should have the answervalue for all records in the database base for that AID.

I believe my issue is coming from the switch ($aid) command and $varsum calculation.

The math is correct for the tvar, average, and deviation functions.

This image should provide you with an example of answervalues and the calpha calculations for two groups.

http://limitedwebgroup.com/calpha.gif

Thanks
jlimited

Entire PHP script:


$completed = mysql_query("SELECT issueid, sid FROM issued WHERE complete='1'")
or die("Invalid query: " . mysql_error());
while ( $survey = mysql_fetch_array($completed) ) {
$sid = $survey["sid"];
$issueid = $survey["issueid"];
$answers = mysql_query("SELECT * FROM answers WHERE sid='$sid'")
or die("Invalid query: " . mysql_error());
while ( $p = mysql_fetch_array($answers) ) {
$ptotal[$issueid] += $p["answervalue"];
$aid = $p["aid"];
switch ($aid) {
case '1':
$q1[] = $p["answervalue"];
break;
case '2':
$q2[] = $p["answervalue"];
break;
case '3':
$q3[] = $p["answervalue"];
break;
case '4':
$q4[] = $p["answervalue"];
break;
case '5':
$q5[] = $p["answervalue"];
break;
case '6':
$q6[] = $p["answervalue"];
break;
case '7':
$q7[] = $p["answervalue"];
break;
case '8':
$q8[] = $p["answervalue"];
break;
case '9':
$q9[] = $p["answervalue"];
break;
case '10':
$q10[] = $p["answervalue"];
break;
case '11':
$q11[] = $p["answervalue"];
break;
case '12':
$q12[] = $p["answervalue"];
break;
case '13':
$q13[] = $p["answervalue"];
break;
case '14':
$q14[] = $p["answervalue"];
break;
case '15':
$q15[] = $p["answervalue"];
break;
case '16':
$q16[] = $p["answervalue"];
break;
case '17':
$q17[] = $p["answervalue"];
break;
case '18':
$q18[] = $p["answervalue"];
break;
case '19':
$q19[] = $p["answervalue"];
break;
case '20':
$q20[] = $p["answervalue"];
break;
case '21':
$q21[] = $p["answervalue"];
break;
case '22':
$q22[] = $p["answervalue"];
break;
case '23':
$q23[] = $p["answervalue"];
break;
case '24':
$q24[] = $p["answervalue"];
break;
case '25':
$q25[] = $p["answervalue"];
break;
case '26':
$q26[] = $p["answervalue"];
break;
case '27':
$q27[] = $p["answervalue"];
break;
case '28':
$q28[] = $p["answervalue"];
break;
case '29':
$q29[] = $p["answervalue"];
break;
case '30':
$q30[] = $p["answervalue"];
break;
}
}
$pt[] = $ptotal[$issueid];

}

$stdevtotal = tvar($pt);
$varsum = tvar($q1) + tvar($q2) + tvar($q3) + tvar($q4) + tvar($q5) + tvar($q6) + tvar($q7) + tvar($q8) + tvar($q9) + tvar($q10) + tvar($q11) + tvar($q12) + tvar($q13) + tvar($q14) + tvar($q15) + tvar($q16) + tvar($q17) + tvar($q18) + tvar($q19) + tvar($q20) + tvar($q21) + tvar($q22) + tvar($q23) + tvar($q24) + tvar($q25) + tvar($q26) + tvar($q27) + tvar($q28) + tvar($q29) + tvar($q30);

$i = 1;
echo "<table border=1>";
echo "<tr>";
echo "<td>Question Grouping</td><td>Cronbach's Alpha</td>";
echo "</tr>";
while ($i <= 4):
$questions = mysql_query("SELECT * FROM questions WHERE grouping='$i'")
or die("Invalid query: " . mysql_error());
$qcount = mysql_num_rows($questions);
while ( $question = mysql_fetch_array($questions) ) {
$calpha = (($qcount/($qcount-1))*(1-($varsum/$stdevtotal)));
}
echo "<tr>";
echo "<td>".$i."</td><td>".$calpha."</td>";
echo "</tr>";
END;
$i++;
endwhile;
echo "</table>";

function tvar($n) {
$totalvar = pow(deviation($n),2);
return $totalvar;
}

function average($n) {
$sum = array_sum($n);
$count = count($n);
return $sum/$count;
}

function deviation ($n){
$avg = average($n);
foreach ($n as $value) {
$variance[] = pow($value-$avg, 2);
}
$deviation = sqrt(average($variance));
return $deviation;
}

You provided a lot of good detail, but you never actually described the problem you're having. From my perspective, to help you out at all, that's where I'd need to start-- the problem.

(please don't say "it doesn't work" :D )

The major problem is my calpha is not coming out correct.

https://secure.churchworker.org/analysis/cronbachalpha.php

The numbers should be less than 1.0

I am pretty sure the answer to that problem stems from the way I am calculating my $varsum. I am looking for a better way to calculate that value.

Currently, I use the switch($aid) command to get all answervalues for question 1 from all $sid. And then repeat it from all 30 questions.

So, the end result should be like the following (I am not sure the exact syntax):

$group[$i][$aid][] = $p["answervalue"];

$varsum += tvar($group[$i][$aid]);

I would like it to loop through all questions from groups 1, 2, 3, and 4.

Does that help?

jlimited

Thanks, that was helpful.

I would say you can simplify some of your code. Typically if you find yourself creating variables such as $q1, $q2, etc, you can probably find a way to use a loop and an array instead. You could do this:


$i = 0;
while ($p = mysql_fetch_array($answers) ) {
$ptotal[$issueid] += $p["answervalue"];
$answerData[$i] = $p;
$i++;
}

Now all your query data is stored in the $answerData array, rather than 30 separate $q1..$q30 variables.

Calculate $varsum like how you mentioned:

foreach($answerData as $val) {
$varsum += tvar($val['answervalue']);
}


This really doesn't solve your calculation problems though. For that I would suggest echoing out every interim result along the way until you discover the error.