Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Sep 2006
    Posts
    32
    Thanks
    5
    Thanked 0 Times in 0 Posts

    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:
    PHP Code:
    $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.



    Thanks
    jlimited

    Entire PHP script:

    PHP Code:
    $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-$avg2);
       }
       
    $deviation sqrt(average($variance));
       return 
    $deviation;

    Last edited by jlimited; 01-24-2007 at 02:56 PM. Reason: editing

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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" )

  • #3
    New Coder
    Join Date
    Sep 2006
    Posts
    32
    Thanks
    5
    Thanked 0 Times in 0 Posts
    The major problem is my calpha is not coming out correct.

    https://secure.churchworker.org/anal...nbachalpha.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
    Last edited by jlimited; 01-24-2007 at 10:39 PM.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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:

    PHP Code:
    $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:
    PHP Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •