webguync Posted August 6, 2008 Share Posted August 6, 2008 Hello everyone, I need some assistance with determining scoring results for an online test I am working on. I have some code which interacts with a MySQL table to display results to display scoring for participants who are in another MySQL table. One Score is uploaded in the morning and one in the evening. There are calculations to determine the difference between morning score and evening score and an overall percentage. What I need to determine is an overall score per class, a highest score per district and the highest overall improvement from morning to evening. A typical result would look like what is included below. What I want to do is highlight each (overall highest score, highest district score, most improvement between morning and evening score) with CSS code. I already have the CSS ready to go. (.OverallWinner, .DistrictWinner, .MostImproved) here is the code I have so far. I took out the DB connection info and anything not necessary to what I am trying to achieve. <?php $myQuery = "SELECT CONCAT(last_name,', ',first_name) AS name, roster0808.employee_id, class,district, score1, score2, score3, score4, assessor_name, assessor_id, date_created, date_uploaded, id FROM roster0808 LEFT JOIN clinical0808 USING (employee_id)"; //if (isset($_GET['dist'])) { //$district = $_GET['dist']; //$glQuery .= "WHERE class LIKE '".$district."%'"; //} $myQuery .= 'ORDER BY class, district, date_created'; $report = $db->query($myQuery); if ($report->num_rows) { //loop to create arrays for each column while ($row = $report->fetch_assoc()) { $empName[] = $row['name']; $empID[] = $row['employee_id']; $district[] = $row['district']; $class[] = $row['class']; $score[] = $row['score1'] + $row['score2'] + $row['score3'] + $row['score4']; $assDate[] = $row['date_created']; $upldDate[] = $row['date_uploaded']; $recordNm[] = $row['score_id']; } //total elements in each array (currently same for all) $numResults = count($empID); //counter for creating "scores" subarray $counter = 0; //this loop creates an array for each employee for ($i=0, $k = 0; $k<$numResults; $k++) { $employeeResults[($class[$k])][$i]['employee_name'] = $empName[$k]; $employeeResults[($class[$k])][$i]['employee_id'] = $empID[$k]; $employeeResults[($class[$k])][$i]['district'] = $district[$k]; if (date('A',$assDate[$k]) == 'AM') { $employeeResults[($class[$k])][$i]['scores']['am']['score'][] = $score[$k]; $employeeResults[($class[$k])][$i]['scores']['am']['time'][] = $assDate[$k]; } else { $employeeResults[($class[$k])][$i]['scores']['pm']['score'][] = $score[$k]; $employeeResults[($class[$k])][$i]['scores']['pm']['time'][] = $assDate[$k]; } //if the next employee is different, then increment the employee counter, and reset score counter if (($k<$numResults-1) && (($empID[$k+1] != $empID[$k]) || ($empID[$k]==NULL))) { $i++; $counter = 0; } if (($k<$numResults-1) && ($class[$k+1] != $class[$k])) { $i = 0; } } for ($p=1; $p<=count($employeeResults); $p++) { for ($q=0; $q<count($employeeResults[$p]); $q++) { $morning = $employeeResults[$p][$q]['scores']['am']['score']; $mornAvg = 0; for ($r=0; $r<count($morning); $r++) { $mornAvg += $morning[$r]; } $employeeResults[$p][$q]['scores']['am']['avg'] = $mornAvg/(count($morning)); $afternoon = $employeeResults[$p][$q]['scores']['pm']['score']; $aftnAvg = 0; for ($r=0; $r<count($afternoon); $r++) { $aftnAvg += $afternoon[$r]; } $employeeResults[$p][$q]['scores']['pm']['avg'] = $aftnAvg/(count($afternoon)); } } } $db->close(); $classes = array(NULL,'11 August 2008','12 August 2008','13 August 2008','Test'); ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <title>Assessment Tool Aug 11-13, 2008</title> <link href="report.css" rel="stylesheet" type="text/css" /> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> </head> <body> <?php for ($c=1; $c<count($classes); $c++) { ?> <div> <h1><?php echo($classes[$c]); ?></h1> <table id="resultlist"> <tr> <th scope="col">Employee Name</th> <th scope="col">Emp. ID</th> <th scope="col">District</th> <th scope="col">Morning Score 1</th> <th scope="col">Time</th> <th scope="col">Morning Score 2</th> <th scope="col">Assr.</th> <th scope="col">Time</th> <th scope="col">MORNING AVG</th> <th scope="col">Afternoon Score 1</th> <th scope="col">Assr.</th> <th scope="col">Time</th> <th scope="col">Afternoon Score 2</th> <th scope="col">Assr.</th> <th scope="col">Time</th> <th scope="col">AFTERNOON AVG</th> <th scope="col">Improvement</th> <th scope="col">DAY AVG</th> <th scope="col">AVG %</th> </tr> <?php if (!isset($employeeResults[$c])) { ?> <tr><td colspan="11">There are no scores to display</td></tr> <?php } else { for ($i=0; $i<count($employeeResults[$c]); $i++) { ?> <tr class="<?php echo $i%2 ? 'hilite' : 'nohilite'; ?>"> <td><?php echo $employeeResults[$c][$i]['employee_name'];?></td> <td><?php echo $employeeResults[$c][$i]['employee_id'];?></td> <td><?php echo $employeeResults[$c][$i]['district'];?></td> <td><?php if (isset($employeeResults[$c][$i]['scores']['am']['score'][0]) && $employeeResults[$c][$i]['scores']['am']['score'][0]!=0) { echo $employeeResults[$c][$i]['scores']['am']['score'][0]; } else { echo ' ';} ?></td> <td><?php if (isset($employeeResults[$c][$i]['scores']['am']['score'][0]) && $employeeResults[$c][$i]['scores']['am']['score'][0]!=0) { echo date('M j, g:i A',$employeeResults[$c][$i]['scores']['am']['time'][0]); } else { echo ' ';} ?></td> <td><?php if (isset($employeeResults[$c][$i]['scores']['am']['score'][1]) && $employeeResults[$c][$i]['scores']['am']['score'][1]!=0) { echo $employeeResults[$c][$i]['scores']['am']['score'][1]; } else { echo ' ';} ?></td> <td><?php if (isset($employeeResults[$c][$i]['scores']['am']['score'][1]) && $employeeResults[$c][$i]['scores']['am']['score'][1]!=0) { echo $employeeResults[$c][$i]['scores']['am']['assessor_id'][1]; } else { echo ' ';} ?></td> <td><?php if (isset($employeeResults[$c][$i]['scores']['am']['score'][1]) && $employeeResults[$c][$i]['scores']['am']['score'][1]!=0) { echo date('M j, g:i A',$employeeResults[$c][$i]['scores']['am']['time'][1]); } else { echo ' ';} ?></td> <td CLASS="AVG1"><?php if (isset($employeeResults[$c][$i]['scores']['am']['avg']) && $employeeResults[$c][$i]['scores']['am']['avg']!=0) { echo number_format($employeeResults[$c][$i]['scores']['am']['avg'],2); } else { echo ' ';} ?></td> <td><?php if (isset($employeeResults[$c][$i]['scores']['pm']['score'][0]) && $employeeResults[$c][$i]['scores']['pm']['score'][0]!=0) { echo $employeeResults[$c][$i]['scores']['pm']['score'][0]; } else { echo ' ';} ?></td> <td><?php if (isset($employeeResults[$c][$i]['scores']['pm']['score'][0]) && $employeeResults[$c][$i]['scores']['pm']['score'][0]!=0) { echo $employeeResults[$c][$i]['scores']['pm']['assessor_id'][0]; } else { echo ' ';} ?></td> <td><?php if (isset($employeeResults[$c][$i]['scores']['pm']['score'][0]) && $employeeResults[$c][$i]['scores']['pm']['score'][0]!=0) { echo date('M j, g:i A',$employeeResults[$c][$i]['scores']['pm']['time'][0]); } else { echo ' ';} ?></td> <td><?php if (isset($employeeResults[$c][$i]['scores']['pm']['score'][1]) && $employeeResults[$c][$i]['scores']['pm']['score'][1]!=0) { echo $employeeResults[$c][$i]['scores']['pm']['score'][1]; } else { echo ' ';} ?></td> <td><?php if (isset($employeeResults[$c][$i]['scores']['pm']['score'][1]) && $employeeResults[$c][$i]['scores']['pm']['score'][1]!=0) { echo $employeeResults[$c][$i]['scores']['pm']['assessor_id'][1]; } else { echo ' ';} ?></td> <td><?php if (isset($employeeResults[$c][$i]['scores']['pm']['score'][1]) && $employeeResults[$c][$i]['scores']['pm']['score'][1]!=0) { echo date('M j, g:i A',$employeeResults[$c][$i]['scores']['pm']['time'][1]); } else { echo ' ';} ?></td> <td class="AVG2"><?php if (isset($employeeResults[$c][$i]['scores']['pm']['avg']) && $employeeResults[$c][$i]['scores']['pm']['avg']!=0) { echo number_format($employeeResults[$c][$i]['scores']['pm']['avg'],2); } else { echo ' ';} ?></td> <td class="improv"><?php if (isset($employeeResults[$c][$i]['scores']['am']['avg']) && isset($employeeResults[$c][$i]['scores']['pm']['avg']) && $employeeResults[$c][$i]['scores']['am']['avg']!=0 && $employeeResults[$c][$i]['scores']['pm']['avg']!=0) { $improv = $employeeResults[$c][$i]['scores']['pm']['avg'] - $employeeResults[$c][$i]['scores']['am']['avg']; //if($improv != 0) { echo number_format($improv,2); //}else { echo ' ';} } else { echo ' ';} ?></td> <td><?php if (isset($employeeResults[$c][$i]['scores']['am']['score'][0]) || isset($employeeResults[$c][$i]['scores']['pm']['score'][0])) { $morning = $employeeResults[$c][$i]['scores']['am']['score']; $avg = 0; for ($r=0; $r<count($morning); $r++) { $avg += $morning[$r]; } //$employeeResults[$c][$i]['scores']['am']['avg'] = $mornAvg/(count($morning)); $afternoon = $employeeResults[$c][$i]['scores']['pm']['score']; for ($r=0; $r<count($afternoon); $r++) { $avg += $afternoon[$r]; } //$employeeResults[$p][$q]['scores']['pm']['avg'] = $aftnAvg/(count($afternoon)); $avg = $avg/(count($morning)+ count($afternoon)); if ($avg !=0) { echo number_format($avg, 2); }else { echo(' ');} }else { echo(' ');} ?></td> <td><?php if ($avg != 0) { echo number_format(($avg/16)*100, 2); }else {echo(' ');} ?></td> </tr> <?php } } ?> </table> </div> <?php } ?> </body> </html> right now in the database, for the district, it's set as varchar and the value is a city such as Atlanta, Ga. I don't know if I need to create another column with a numerical identifier to achieve what I want. let me know if I can provide any other information to assist with helping resolve my needs. thanks in advance! [pre] Employee Name District Morning Score 1 Morning Score 2 Morning Average Afternoon Score 1 Afternoon Score 2 Afternoon Average Improvement Day Average Average % Daffy Duck Atlanta Ga 5 9 7 18 20 19 12 12.50 75 Bugs Bunny Atlanta Ga 10 20 15 15 30 20 5 18.50 85 Taz Devil Los Angelas, CA 15 15 15 20 30 25 10 20.00 95 Elmer Fudd Los Angelas, CA 10 12 11 12 14 13 2 12.00 70 [/pre] Quote Link to comment Share on other sites More sharing options...
webguync Posted August 6, 2008 Author Share Posted August 6, 2008 here is my attempt to do this, but I think I am off. Also this code is causing an error, and not sure why? $valedictorian = 0; $DistrictWinner = 0; $MostImproved = 0; for($z=1; $z<count($employeeResults); $z++) { if($employeeResults[$z]['scores']['avg'] > $employeeResults[$valedictorian]['scores']['avg']) { $DistrictWinner = $valedictorian; $valedictorian = $z; continue; } if($employeeResults[$z]['scores']['avg'] > $employeeResults[$DistrictWinner]['scores']['avg']) { $DistrictWinner = $z; continue; } if($employeeResults[$z]['scores']['avg'] > $employeeResults[$MostImproved]['scores']['avg']) { $DistrictWinner = $z; } } $employeeResults[$valedictorian]['winner'] = "v"; $employeeResults[$DistrictWinner]['winner'] = "DW"; $employeeResults[$MostImproved]['winner'] = "IMP"; <?php } else { for ($i=0; $i<count($employeeResults[$c]); $i++) { ?> <tr class=" <?php } else { for ($i=0; $i<count($employeeResults); $i++) { ?> <tr class="<?php if (isset($employeeResults[$i]['winner'])) { if($employeeResults[$i]['winner'] == "v") { echo 'valedictorian'; }else if($employeeResults[$i]['winner'] == "DW"){ echo 'DistrictWinner'; }else if($employeeResults[$i]['winner'] == "IMP"){ echo 'MostImproved'; }else { echo $i%2 ? 'hilite' : 'nohilite'; } ?>"> Quote Link to comment Share on other sites More sharing options...
webguync Posted August 7, 2008 Author Share Posted August 7, 2008 anyone see what might be causing an error in the code above? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.