How can I make two separate data into one value?

Question

I am trying to display student's grades, prelim, midterm and finals.

The problem is the grade os being doubled with separate grade period grades.

This image shows the problem:

Expected Output: Is there a way to join the grades together like this image?

View:

<?php foreach($grades as $grade): ?>
<tr>
    <th scope="row"><?php echo $grade['subCode']; ?></th>
    <td><?php echo $grade['subject']; ?></td>
        <?php if($grade['gradePeriod']=="Prelim"): ?>
            <td><?php echo $grade['grade']; ?></td>
        <?php else: ?>
            <td>0</td>
        <?php endif; ?>

        <?php if($grade['gradePeriod']=="Midterm"): ?>
            <td><?php echo $grade['grade']; ?></td>
        <?php else: ?>
            <td>0</td>
        <?php endif; ?>

        <?php if($grade['gradePeriod']=="Finals"): ?>
            <td><?php echo $grade['grade']; ?></td>
        <?php else: ?>
            <td>0</td>
        <?php endif; ?>

    <td><?php echo $grade['remarks']; ?></td>
</tr>
<?php endforeach; ?>

Model:

public function gradePeriod(){
    $this->db->select('subCode,subject,grade,gradePeriod,remarks');
    $this->db->from('tbl_college_grades');
    $this->db->where('studentID', ' 200171419');
    $this->db->where('schoolYear','2022-2023');
    $this->db->where('semester','First Semester');
    $query = $this->db->get();
    return $query->result_array();

}

@Vickel - hello, when I do group_by studentID then it only returns Prelim grade but the Midterm or Finals grade does not include — Coder Codes, Aug 13 '22 at 16:02
@CoderCodes No, but if you are grouping then you need to extract the relevant grades for each period, and `SUM()` works for that, e.g.: `SUM(IF(gradPeriod='Midterm',grade,0)) AS midterm_grade, SUM(IF(gradePeriod='Prelim',grade,0)) AS prelim_grade, SUM(IF(gradePeriod='Finals',grade,0)) AS finals_grade`. — kmoser, Aug 13 '22 at 16:08
@kmoser - hello sir, can I do that query using active record in codeigniter? — Coder Codes, Aug 13 '22 at 16:10
@kmoser - can you please help me to use that query in active record, please? — Coder Codes, Aug 13 '22 at 16:11
@kmoser - i've done it, thanks it worked! can you pls add it as an answer below so I can marked it up? — Coder Codes, Aug 13 '22 at 16:19
@kmoser - there's only 1 problem, when I add new subject then it groups everything and not add the new subject needs to be graded — Coder Codes, Aug 13 '22 at 16:23
you can use a join for that, joining the same table, one query for midterm and another one for prelim, use aliases, more at https://stackoverflow.com/questions/6024800/codeigniter-table-join and [CI join()](https://codeigniter.com/userguide3/database/query_builder.html#CI_DB_query_builder::join) — Vickel, Aug 13 '22 at 16:35
@CoderCodes Are you grouping by `studentID`? It sounds like you need to `GROUP BY studentID, subject`? — kmoser, Aug 13 '22 at 17:41

score 0 · Answer 1 · answered Aug 15 '22 at 08:27

Based on this MySQL - Rows to Columns

I assume your table value is like this:

subCode	subject	grade	gradePeriod	remarks
AE 7	Accounting-Internship	82	Prelim	PASSED
AE 7	Accounting-Internship	96	Midterm	PASSED
AE 7	Accounting-Internship	99	Finals	PASSED

And you want to convert ROW to COLUMN

subCode	subject	Prelim	Midterm	Finals	remarks
AE 7	Accounting-Internship	82	96	99	PASSED

Step 1 : Create View in DB

create view grade_extended as (
  select
    subCode,
    subject,
    grade,
    gradePeriod,
    case when gradePeriod = "Prelim" then grade end as Prelim,
    case when gradePeriod = "Prelim" then remarks end as Prelim_remarks,
    case when gradePeriod = "Midterm" then grade end as Midterm,
    case when gradePeriod = "Midterm" then remarks end as Midterm_remarks,
    case when gradePeriod = "Finals" then grade end as Finals,
    case when gradePeriod = "Finals" then remarks end as Finals_remarks,
    remarks
  from tbl_college_grades
);

Step 2 : Create pivot table view

create view grade_pivot as (
  select
    subCode,
    subject,
    sum(Prelim) as Prelim,
    group_concat(Prelim_remarks) as Prelim_remarks,
    sum(Midterm) as Midterm,
    group_concat(Midterm_remarks) as Midterm_remarks,
    sum(Finals) as Finals,
    group_concat(Finals_remarks) as Finals_remarks
  from grade_extended
  group by subCode, subject
);

Step 3 : Select view

select * from grade_pivot

How can I make two separate data into one value?

1 Answers1