Jump to content

What is the best method to use two switch statement for different output?

Olumide

By Olumide
in PHP Coding Help

 Share

Recommended Posts

Olumide Advanced Member

Olumide

Posted
Posted

Hi, in my project, I have two scripts, one is to show all the students position in a class and only available to the admin, while the second script will show the results for each student with the position. In the second script which is available to the student and admin, I added position row, but I don't know how to add the scripts for positioning. Please the experts, how can I fix this issue I am encountering.

Here is the sql and functions for the ordinal suffix for ranking, from the code, a check button was used in the first script, but the second script, the user only need to select the term. 

switch ($midend) {
    case 'M': $res = $pdo->prepare("SELECT subjectname
                                         , stname
                                         , score
                                         , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq
                                         , @rank := IF(score = @prev, @rank, @seq) as rank
                                         , @prev := score as prev
                                         , @prevs := subjectname as prevs
                                    FROM (
                                            SELECT cl.classname
                                                 , sb.subjectname
                                                 , concat(st.firstname, ' ', st.lastname) as stname
                                                 , round( sum( case exam when 'Exam' then score/70*100
                                                                         else score*10
                                                                         end 
                                                              ) / COUNT(distinct exam) ) as score
                                            FROM student_class stc 
                                                 JOIN student st ON stc.studentid = st.id
                                                 JOIN class cl ON stc.classid = cl.id
                                                 JOIN level l ON cl.levelid = l.id
                                                 JOIN course c ON l.id = c.levelid
                                                 JOIN subject sb ON c.subjectid = sb.id
                                                 JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id
                                            WHERE stc.semesterid = ?
                                                    AND exam = 'CA1'
                                                    AND cl.id = ?
                                            GROUP BY c.id, sb.id, st.id
                                            ORDER BY subjectname, score DESC
                                            LIMIT 9223372036854775807        -- MariaDB bug workaround
                                         ) ordered
                                         JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init
                                    ");
              $res->execute([ $semester, $class ]);
              $chkm = 'checked';
              $chke = $chky = '';
              break;
    case 'E':  $res = $pdo->prepare("SELECT subjectname
                                         , stname
                                         , score
                                         , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq
                                         , @rank := IF(score = @prev, @rank, @seq) as rank
                                         , @prev := score as prev
                                         , @prevs := subjectname as prevs
                                    FROM (
                                            SELECT   sb.subjectname
                                                 , concat(st.firstname, ' ', st.lastname) as stname
                                                 , round( sum( score )) as score
                                            FROM student_class stc 
                                                 JOIN student st ON stc.studentid = st.id
                                                 JOIN class cl ON stc.classid = cl.id
                                                 JOIN level l ON cl.levelid = l.id
                                                 JOIN course c ON l.id = c.levelid
                                                 JOIN subject sb ON c.subjectid = sb.id
                                                 JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id
                                            WHERE stc.semesterid = ?
                                                    AND cl.id = ?
                                            GROUP BY c.id, sb.id, st.id
                                            ORDER BY subjectname, score DESC
                                            LIMIT 9223372036854775807        -- MariaDB bug workaround
                                         ) ordered 
                                         JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init
                                    ");
              $res->execute([ $semester, $class ]);
              $chke = 'checked';
              $chkm = $chky = '';
              break;       
    default:  $res = $pdo->prepare("SELECT subjectname
                                         , stname
                                         , score
                                         , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq
                                         , @rank := IF(score = @prev, @rank, @seq) as rank
                                         , @prev := score as prev
                                         , @prevs := subjectname as prevs
                                    FROM (
                                            SELECT   sb.subjectname
                                                 , concat(st.firstname, ' ', st.lastname) as stname
                                                 , round( sum( score )/count(distinct sm.id)) as score
                                            FROM student_class stc 
                                                 JOIN student st ON stc.studentid = st.id
                                                 JOIN class cl ON stc.classid = cl.id
                                                 JOIN level l ON cl.levelid = l.id
                                                 JOIN course c ON l.id = c.levelid
                                                 JOIN subject sb ON c.subjectid = sb.id
                                                 JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id
                                                 JOIN semester sm ON stc.semesterid = sm.id
                                                 JOIN session sn ON sm.sessionid = sn.id
                                            WHERE sm.sessionid = ?
                                                    AND cl.id = ?
                                            GROUP BY cl.id, sb.id, st.id
                                            ORDER BY subjectname, score DESC
                                            LIMIT 9223372036854775807        -- MariaDB bug workaround
                                         ) ordered 
                                         JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init
                                    ORDER BY subjectname, score DESC     
                                    ");
              $res->execute([ $session, $class ]);
              $chky = 'checked';
              $chkm = $chke = '';
              break;       
}

  // calling function here
/********************************************
  * Derive ordinal suffix for $n
  * 
  * @param int $n     the number
  * @returns string   number with suffix eg 23rd   
  */
  function ordinal($n) 
  {
      $str = "$n";
      $t = $n > 9 ? substr($str,-2,1) : 0;
      $u = substr($str,-1);
      //if((($t%100) >= 11) && (($t%100) <= 13))
      if ($t==1) 
        return $str . 'th';
      else 
          switch ($u) 
          {
              case 1: return $str . 'st';
              case 2: return $str . 'nd';
              case 3: return $str . 'rd';
              default: return $str . 'th';
          }
  }


  //function end
$data = [];
  foreach ($res as $r) {
    if (!isset($data[$r['subjectname']])) {
        $

Here is the second scripts where I want to fit in the above code for the ranking to populate the position row. The reason why I need all the sql code is because of the term/semester differences. 

switch($termno) {
        case 1: $term_headings = "<th>1st<br>Term<br>100</th>
                                  <!--<th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>
                                  <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>-->";
                                  
                                  break;
        case 2: $term_headings = "<th>1st<br>Term<br>&nbsp;</th>
                                  <th>2nd<br>Term<br>100</th>
                                  <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>";
                                 
                                  break;
        default: $term_headings = "<th>1st<br>Term<br>&nbsp;</th>
                                  <th>2nd<br>Term<br>&nbsp;</th>
                                  <th>3rd<br>Term<br>100</th>";
                                  
    }
    
    $report_title = $termno == 3 ? "End of Year Results" : "End of Term Results";
    
    ##########################
    
    #############################################################################
    $res->execute( [ $session, $student, $termno, $clid ] );
    $data = [];
    // get data common to all rows from first row
    $r = $res->fetch();
    if ($r) {
        $studentname = $r['stname'];
        $studentdob = $r['dob'];
        $studentmatricno = $r['matricno'];
        $studentlevel = $r['classname'];
        $studentsession = $r['sessionname'];
        $studentterm = "- Term $termno";
        $nextterm = $r['date_until'];
        $passport = "images/" . $r['image'];                                                                      ### provide image path here
        $level = $r['level'];
        // then process the rest of the row data in the first and remaining rows
        do {
            if (!isset($data[ $r['subjectid'] ])) {
                $data[ $r['subjectid'] ] = [ 'name' => $r['subjectname'],
                                             #'exams' => ['CA1'=>'', 'CA2'=>'', 'CA3'=>'', 'Exam'=>''],
                                            'exams' => ['CA1'=>'', 'CA2'=>'', 'Exam'=>''],
                                             'scores'  => [ 1=>0, 0, 0 ],
                                             'avg' => 0
                                             //'rank' => 0 
                                           ];
            }   
            if ($r['term'] == $termno && isset($data[$r['subjectid'] ]['exams'][ $r['exam']])) {
                $data[ $r['subjectid'] ]['exams'][ $r['exam'] ] = $r['score'];
            }
            $data[ $r['subjectid'] ]['scores'][$r['term']] += $r['score'];
        } while ($r = $res->fetch());
    // get the avg scores for the class
        $avgs = classAverageScores($pdo, $clid, $session, $termno);
        foreach ($avgs as $s => $av) {
            if (isset($data[$s]))
                $data[$s]['avg'] = round($av,0);
        } 

        ###########my ranking here###############
        /***** manipulating here **************
         * 
         * 
         * 
         * /
         * */
   /********************************************
  * Derive ordinal suffix for $n
  * 
  * @param int $n     the number
  * @returns string   number with suffix eg 23rd   
  */
  function ordinal($n) 
  {
      $str = "$n";
      $t = $n > 9 ? substr($str,-2,1) : 0;
      $u = substr($str,-1);
      if ($t==1) 
        return $str . 'th';
      else 
          switch ($u) 
          {
              case 1: return $str . 'st';
              case 2: return $str . 'nd';
              case 3: return $str . 'rd';
              default: return $str . 'th';
          }
  }

  //function end
//$data = [];
  foreach ($res as $r) {
    if (!isset($data[$r['subjectname']])) {
        $data[$r['subjectname']]['students'] = [];
    }
    
    $position = ordinal($r['rank']);
    $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $position ];
}


    /*    //hffffffffff
     
        foreach ($res as $r) {
    if (!isset($data[$r['subjectname']])) {
        $data[$r['subjectname']]['students'] = [];
    }
    
    $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $r['rank'] ];
}  */
    ################################################################################
    #  Get pupil count                                                             #
    ################################################################################
    $res = $pdo->prepare("SELECT COUNT(DISTINCT stc.studentid) AS pupils
                                FROM student_class stc 
                                     JOIN semester sm ON sm.id = stc.semesterid
                                     JOIN result r ON stc.id = r.studentclassid
                                WHERE sm.id = ?
                                  AND stc.classid = ?
                        ");
    $res->execute([ $semester, $clid ]);
    $pupil_count = $res->fetchColumn();    
            
    ################################################################################
    #  Loop through the data array to construct the output table rows              #
    ################################################################################
    


        $tdata = '';
        $n = 1;
        $grand_total = 0;
        $subject_count = 0;
        foreach ($data as $subid => $subdata) {
            $tdata .= "<tr><td>$n</td><td>{$subdata['name']}</td>";
            foreach ($subdata['exams'] as $s) {
                $tdata .= "<td>" . ($s=='' ? '&ndash;' : $s) . "</td>";
            }
            foreach ($subdata['scores'] as $t => $s) {
                if ($s==0) $s = '';
                $tdata .= "<td>" . ($t <= $termno ? $s : '') . "</td>";
            }
            $temp = array_filter($subdata['scores']);
            $total = $temp ? round(array_sum($temp)/count($temp)) : 0;
            $grand_total += $total;
            if ($total) {
                list($grade, $comment) = getGradeComment($pdo, $total, $level);
                $subject_count++;
            }
            else {
                $grade = '-';
                $comment = '-';
            }
            $clr = GRADE_COLOUR[$grade] ?? '#000';
            $tdata .= "<td>$total</td><td>{$subdata['avg']}</td><td style='color:$clr; font-weight: 600;'>$grade</td><td>$comment</td></tr>\n";
            ++$n;
        }
    }
    else {
        $studentname = '';
        $studentdob = '';
        $studentmatricno = '';
        $studentlevel = '';
        $studentsession = '';
        $studentsemester = '';
        #$nextterm = '';
        $studentterm = '';
        $passport = '';
        $level = '4';
        $pupil_count = 0;
        $grand_total = 0;
        $subject_count = 1;
//      $clid = 0;
        $tdata = "<tr><td colspan='13'>No results found</td></tr>\n";
    }

 

sample5.png

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.