Question 257734 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to display a school broadsheet

Am building a mini project on how to display a school broadsheet, i have try all i know best but no solution, please i need your help.
I have three tables Students, Scores, Subject.

This is how i want my display to look like.

Reg_no Names of Students Mathematics English Total Average
TGD102 Benjamin King 60 55 115 57.5
TGD103 David Mathew 50 45 95 47.5
TGD104 Mary James 45 50 95 47.5
TGD105 Solomom David 70 45 115 57.5

I have try my little best but only was able to display it this way below

Reg_no Student Names Subject marks
TGD102 Benjamin King Mathematics 50
TGD103 David Mathew Mathematics 45
TGD104 Mary James Mathematics 35
TGD105 Solomom David Mathematics 55
TGD102 Benjamin King English 45
TGD103 David Mathew English 35
TGD104 Mary James English 40
TGD105 Solomom David English 50

This was my code i used to display the table above.

<table id="myTable" class="table table-bordered table-striped">
    <thead>
        <th>Reg</th>
        <th>Name</th>
        <th>Subject</th>
        <th>marks</th>
    </thead>
    <tbody>
    <?php
        include('db/conn.php');

        $query=mysqli_query($conn,"select * from student,score,subject 
                where student.stu_id=score.scostu_id
                AND score.scosbj_id=subject.subject_id ");
                
        while($row2=mysqli_fetch_array($query)){
    ?>
        <tr>
            <td align="center"><?php echo $row2['stu_id']; ?></td>
            <td><span style="text-transform:uppercase;"><?php echo $row2['name']; ?></span></td>
            <td><span style="text-transform:uppercase;"><?php echo $row2['subject_title']; ?></span></td>
            <td><span style="text-transform:uppercase;"><?php echo $row2['exam']; ?></span></td>
        </tr>
    <?php
        }
    ?>
    </tbody>
</table>

How can i modified the above code to give me some thing like this.

Reg_no Names of Students Mathematics English Total Average
TGD102 Benjamin King 45 50 95 47.5
TGD103 David Mathew 50 45 95 47.5
TGD104 Mary James 60 30 90 45
TGD105 Solomon Dvid 35 50 85 42.5
Tags:

2

Answers


  1. 0

    This code is not optimize, but will do the job as you need.

    <?php

$subjects = array();
$query = mysqli_query($conn,"select * from subject");
while($row = mysqli_fetch_array($query)){
    $subjects[$row['subject_id']] = $row['subject_title'];
}

?>

<table id="myTable" class="table table-bordered table-striped">
<thead>
<th>Reg</th>
<th>Name</th>
<?php foreach($subjects as $sbj) { ?>
<th><?= $sbj; ?></th>
<?php } ?>
<th>Total</th>
<th>Average</th>
</thead>
<tbody>
<?php
$query1 = mysqli_query($conn,"select * from student");
while($row1 = mysqli_fetch_array($query1)){
    
    $scores = array();
    $query2 = mysqli_query($conn, "select * from score where scostu_id = " . $row1['stu_id']);
    while($row2 = mysqli_fetch_array($query2)){
        $scores[$row2['scosbj_id']] = $row2['score_value'];
    }
    
    $total = 0;
    $count = 0;
?>
<tr>
<td align="center"><?= $row2['stu_id']; ?></td>
<td><?= $row2['name']; ?></td>
<?php
foreach($subjects as $sbj_id => $sbj_title) {
    $total += $scores[$sbj_id];
    $count++;
?>
<td><span><?= $scores[$sbj_id]; ?></span></td>
<?php } ?>
<td><?= intval($total); ?></td>
<td><?= (($total > 0 && $count > 0)?(round($total / $count)):0); ?></td>
</tr>
<?php } ?>
    Login or Signup to reply.
  2. 0

    Use following query to fetch the data in desired format

    select stu_id, name, 
SUM(CASE WHEN subject_title = 'Mathematics' THEN exam ELSE 0 END) as Mathematics, 
SUM(CASE WHEN subject_title = 'English' THEN exam ELSE 0 END) as English, 
sum(exam) as Total, round(avg(exam),1) as Average from (select stu_id, name, subject_title, 
exam from student inner join (select scostu_id, subject_title, exam from score left join 
subject on score.scosbj_id = subject.subject_id) as t1 on student.stu_id = t1.scostu_id) 
as t2 GROUP BY t2.stu_id

    It will give an output similar to this

    enter image description here

    Modify your HTML code accordingly to accommodate for additional columns. Enjoy.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search