Generate students postions based on marks scored in an exam

[sule]

Mysql Query

SELECT school.school, school.logo, student.studentid,student.name, student.gender, student.program, student.class, student.house, student.enstatus, student.image, marks.studentid, marks.name, marks.acayear, marks.semester, marks.form, marks.class, marks.subject, marks.class_score1, marks.exam_score1, marks.total, marks.remarks,

                    RANK() OVER (PARTITION BY marks.lessonsid, marks.acayear, marks.form, marks.semester, marks.subject, marks.class ORDER BY marks.total DESC) as rank

                    FROM marks

                    LEFT JOIN student ON marks.studentid=student.studentid

                    LEFT JOIN school ON student.schoolid=school.schoolid WHERE marks.acayear='2024/2025' AND marks.semester='1' AND marks.form='3' AND marks.class='SCI A' AND student.enstatus='INSCHOOL'

Check the file containing the sql table
 

Table.txt

[sule]

[Barand]

I couldn't find anything wrong with your query, although I had to limit my version to the marks table only as the other tables required seem to have fallen down the back of your sofa.

 

I assumed you want the positions in their class.

 SELECT 
 --       marks.acayear
 --     , marks.semester
 --     , marks.form
 --     , marks.class
        marks.studentid
      , marks.name
      , marks.subject
      , marks.class_score1
      , marks.exam_score1
      , marks.total
      , marks.remarks
      , RANK() OVER (PARTITION BY marks.lessonsid, marks.acayear, marks.form, marks.semester, marks.subject, marks.class ORDER BY marks.total DESC) as rank

FROM marks

WHERE marks.acayear='2024/2025' 
      AND marks.semester='1' 
		AND marks.form='3' 
		AND marks.class='SCI A'
		
ORDER BY name, SUBJECT;

+-------------+--------------------------+----------------+--------------+-------------+-------+-----------+------+
| studentid   | name                     | subject        | class_score1 | exam_score1 | total | remarks   | rank |
+-------------+--------------------------+----------------+--------------+-------------+-------+-----------+------+
| 82611900422 | ABDUL-HANAN SA-EED       | BIOLOGY        |            0 |           0 |     0 |           |   23 |
| 82611900422 | ABDUL-HANAN SA-EED       | CHEMISTRY      |            0 |           0 |     0 |           |   29 |
| 82611900422 | ABDUL-HANAN SA-EED       | ELECTIVE MATHS |            0 |           0 |     0 |           |   29 |
| 82611900422 | ABDUL-HANAN SA-EED       | ENGLISH LANG   |            0 |           0 |     0 |           |   29 |
| 82611900422 | ABDUL-HANAN SA-EED       | ICT            |            0 |           0 |     0 |           |   26 |
| 82611900422 | ABDUL-HANAN SA-EED       | INT SCIENCE    |            0 |           0 |     0 |           |   29 |
| 82611900422 | ABDUL-HANAN SA-EED       | MATHEMATICS    |            0 |           0 |     0 |           |   25 |
| 82611900422 | ABDUL-HANAN SA-EED       | PHYSICS        |            0 |           0 |     0 |           |   27 |
| 82611900422 | ABDUL-HANAN SA-EED       | SOCIAL STUDIES |            0 |           0 |     0 |           |   26 |
+-------------+--------------------------+----------------+--------------+-------------+-------+-----------+------+
| 80106802422 | ABDUL-RAHMAN NASIBA      | BIOLOGY        |         23.4 |        53.9 |  77.3 | Very Good |    3 |
| 80106802422 | ABDUL-RAHMAN NASIBA      | CHEMISTRY      |         16.8 |        53.2 |    70 | Very Good |    6 |
| 80106802422 | ABDUL-RAHMAN NASIBA      | ELECTIVE MATHS |         16.8 |        53.9 |  70.7 | Very Good |   11 |
| 80106802422 | ABDUL-RAHMAN NASIBA      | ENGLISH LANG   |         20.1 |        39.2 |  59.3 | Credit    |   18 |
| 80106802422 | ABDUL-RAHMAN NASIBA      | ICT            |         16.8 |        23.8 |  40.6 | Pass      |   25 |
| 80106802422 | ABDUL-RAHMAN NASIBA      | INT SCIENCE    |         20.1 |        39.2 |  59.3 | Credit    |   14 |
| 80106802422 | ABDUL-RAHMAN NASIBA      | MATHEMATICS    |         16.8 |        30.8 |  47.6 | Pass      |   21 |
| 80106802422 | ABDUL-RAHMAN NASIBA      | PHYSICS        |         16.8 |        53.9 |  70.7 | Very Good |    8 |
| 80106802422 | ABDUL-RAHMAN NASIBA      | SOCIAL STUDIES |         13.5 |        23.1 |  36.6 | Fail      |   25 |
+-------------+--------------------------+----------------+--------------+-------------+-------+-----------+------+
| 82606700822 | ABUBAKARI ABDUL HALIK    | BIOLOGY        |         13.5 |        45.5 |    59 | Credit    |   11 |
| 82606700822 | ABUBAKARI ABDUL HALIK    | CHEMISTRY      |         22.8 |        53.9 |  76.7 | Very Good |    5 |
| 82606700822 | ABUBAKARI ABDUL HALIK    | ELECTIVE MATHS |         13.5 |        39.2 |  52.7 | Crdeit    |   26 |
| 82606700822 | ABUBAKARI ABDUL HALIK    | ENGLISH LANG   |         20.1 |        46.2 |  66.3 | Good      |   14 |
| 82606700822 | ABUBAKARI ABDUL HALIK    | ICT            |         13.5 |        46.2 |  59.7 | Credit    |   15 |
| 82606700822 | ABUBAKARI ABDUL HALIK    | INT SCIENCE    |         22.8 |        53.9 |  76.7 | Very Good |    5 |
| 82606700822 | ABUBAKARI ABDUL HALIK    | MATHEMATICS    |         23.1 |        45.5 |  68.6 | Good      |   10 |
| 82606700822 | ABUBAKARI ABDUL HALIK    | PHYSICS        |         13.5 |        23.8 |  37.3 | Fail      |   24 |
| 82606700822 | ABUBAKARI ABDUL HALIK    | SOCIAL STUDIES |         16.8 |        30.8 |  47.6 | Pass      |   21 |
+-------------+--------------------------+----------------+--------------+-------------+-------+-----------+------+
| 80115900922 | ALAWI ABDALLAH           | BIOLOGY        |         10.2 |        37.8 |    48 | Pass      |   13 |
| 80115900922 | ALAWI ABDALLAH           | CHEMISTRY      |         22.8 |        38.5 |  61.3 | Credit    |    8 |
| 80115900922 | ALAWI ABDALLAH           | ELECTIVE MATHS |         23.1 |        39.2 |  62.3 | Credit    |   20 |
| 80115900922 | ALAWI ABDALLAH           | ENGLISH LANG   |         16.8 |        53.9 |  70.7 | Very Good |   12 |
| 80115900922 | ALAWI ABDALLAH           | ICT            |         13.5 |        39.2 |  52.7 | Crdeit    |   22 |
| 80115900922 | ALAWI ABDALLAH           | INT SCIENCE    |         19.8 |        37.8 |  57.6 | Credit    |   16 |
| 80115900922 | ALAWI ABDALLAH           | MATHEMATICS    |         26.4 |        46.9 |  73.3 | Very Good |    3 |
| 80115900922 | ALAWI ABDALLAH           | PHYSICS        |         16.8 |        38.5 |  55.3 | Credit    |   17 |
| 80115900922 | ALAWI ABDALLAH           | SOCIAL STUDIES |         13.5 |        23.8 |  37.3 | Fail      |   24 |
+-------------+--------------------------+----------------+--------------+-------------+-------+-----------+------+
|   etc...

 

[sule]

I am very grateful for your quick response. i am havin a challenge displaying this in a table using php while loop. All the results are joined together but want each student details and marks seperated. can have any assistance?

[Barand]

As you loop throught the query results, check for a change of studentid. Each time to reach a new student, out put the report headings (name, photo etc) then continue listing subject rows until the next change.

 

Pseudocode...

previd = 0;

foreach result row
{
    if studentid != previd
        {
            previd = studentid
            output report headings
        }
     output subject data
}

 

[sule]

I am very grateful for your quick response. i am havin a challenge displaying this in a table using php while loop. All the results are joined together but want each student details and marks seperated. can have any assistance?

[Barand]

I've just described a method to you.

What have you now tried?

(or are you just going to keep repeating the same question without trying the suggestion?)

[sule]

I am still confuse if you can help

My code

 

<div class="card shadow">

                        <div class="card-header"><span class="text-primary"></span></div>

                        <div class="card-body">

                            <div class="formwrap">

                                <!--###########updateButton Second Form ###########-->

                                <form action="report1.php" method="post" enctype="multipart/form-data" autocomplete="off">

                                    <div class="row form-group">

                                        <?php

                                        $select1 = "SELECT * FROM acayear";

                                        $result1 = mysqli_query($con, $select1);

                                        ?>

                                        <div class="col-sm-3">

                                            <label>Academic Year</label>

                                            <select name="acayear" class="form-select form-select-sm" aria-label=".form-select-sm" required="required">

                                                <?php while ($row = mysqli_fetch_array($result1)):; ?>

                                                    <option value="<?php echo $row[1]; ?>">

                                                        <?php echo $row[1]; ?>

                                                    </option>

                                                <?php endwhile; ?>

                                            </select>

                                        </div>

                                        <?php

                                        $select2 = "SELECT * FROM semester";

                                        $result2 = mysqli_query($con, $select2); ?>

                                        <div class="col-sm-3">

                                            <label>Semseter</label>

                                            <select name="semester" class="form-select form-select-sm" aria-label=".form-select-sm" required="required">

                                                <?php while ($row = mysqli_fetch_array($result2)):; ?>

                                                    <option value="<?php echo $row[1]; ?>">

                                                        <?php echo $row[1]; ?>

                                                    </option>

                                                <?php endwhile; ?>

                                            </select>

                                        </div>

                                        <?php

                                        $select3 = "SELECT * FROM form WHERE form.form<>'PAST' ORDER BY form.form ASC";

                                        $result3 = mysqli_query($con, $select3); ?>

                                        <div class="col-sm-3">

                                            <label>Form</label>

                                            <select name="form" class="form-select form-select-sm" aria-label=".form-select-sm" required="required">

                                                <option value=""></option>

                                                <?php while ($row = mysqli_fetch_array($result3)):; ?>

                                                    <option value="<?php echo $row[1]; ?>">

                                                        <?php echo $row[1]; ?>

                                                    </option>

                                                <?php endwhile; ?>

                                            </select>

                                        </div>

                                        <?php

                                        $select4 = "SELECT * FROM class ORDER BY class.class ASC";

                                        $result4 = mysqli_query($con, $select4); ?>

                                        <div class="col-sm-3">

                                            <label>Select Class</label>

                                            <select name="class" class="form-select form-select-sm" aria-label=".form-select-sm" required="required">

                                                <option value=""></option>

                                                <?php while ($row = mysqli_fetch_array($result4)):; ?>

                                                    <option value="<?php echo $row[2]; ?>">

                                                        <?php echo $row[2]; ?>

                                                    </option>

                                                <?php endwhile; ?>

                                            </select>

                                        </div>

                                    </div>

                                    <div class="mt-2 d-grid gap-2 col-6 mx-auto">

                                        <input name="submit" type="submit" class="btn btn-primary btn-sm btn-responsive" value="View Class">

                                    </div>

                                </form>

                            </div>

                        </div>

                    </div>

                </div>

                <!--###################################################################################################-->

                <div class="heading mt-2">

                    <?php error_reporting(0); ?>

                    <?php

                    if (isset($_POST['submit'])) {

                        $acayear = mysqli_real_escape_string($con, $_POST['acayear']);

                        $semester = mysqli_real_escape_string($con, $_POST['semester']);

                        $form = mysqli_real_escape_string($con, $_POST['form']);

                        $class = mysqli_real_escape_string($con, $_POST['class']);

                    }

                    $select = "SELECT school.school, school.logo, student.studentid,student.name, student.gender, student.program, student.class, student.house, student.enstatus, student.image, marks1.studentid, marks1.name, marks1.acayear, marks1.semester, marks1.form, marks1.class, marks1.subject, marks1.class_score1, marks1.exam_score1, marks1.total, marks1.remarks,marks1.rank

                    FROM marks1

                    LEFT JOIN student ON marks1.studentid=student.studentid

                    LEFT JOIN school ON student.schoolid=school.schoolid WHERE marks1.acayear='{$acayear}' AND marks1.semester='{$semester}' AND marks1.form='{$form}' AND marks1.class='{$class}' AND student.enstatus='INSCHOOL' GROUP BY marks1.studentid, marks1.name, marks1.class, marks1.subject";

                    $run = mysqli_query($con, $select);

                    $row = mysqli_fetch_array($run);

                    $marksid = $row['marksid'];

                    $lessonsid = $row['lessonsid'];

                    $acayear = $row['acayear'];

                    $form = $row['form'];

                    $semester = $row['semester'];

                    $class = $row['class'];

                    $school = $row['school'];

                    $logo = $row['logo'];

                    $studentid = $row['studentid'];

                    $name = $row['name'];

                    $gender = $row['gender'];

                    $program = $row['program'];

                    $class = $row['class'];

                    $house = $row['house'];

                    $image = $row['image'];

 

                    ?>

                </div>

                <div id="heading">

                    <?php echo "$school"; ?>

                </div>

 

                <div id="logo1">

                    <img src="../uploads/student/<?php echo $image; ?>" width="100px" class="rounded">

                </div>

 

                <div id="heading2">

                    <b> STUDENT ID:</b> <?php echo $studentid; ?><?php echo "&nbsp"; ?> <?php echo "&nbsp"; ?> <b>GENDER:</b> <?php echo $gender; ?> <?php echo "&nbsp"; ?> <?php echo "&nbsp"; ?><b>PROGRAM:</b><?php echo "&nbsp"; ?><?php echo $program; ?><br><b>CLASS:</b><?php echo "&nbsp"; ?><?php echo $class; ?> <?php echo "&nbsp"; ?><b>HOUSE:</b> <?php echo "&nbsp"; ?><?php echo $house; ?><?php echo "&nbsp"; ?><b> ACADEMIC YEAR:</b><?php echo "&nbsp"; ?><?php echo $acayear; ?><?php echo "&nbsp"; ?> <br><b> NAME OF STUDENT:</b> <?php echo $name; ?>

                </div>

                <div id="logo">

                    <img src="../uploads/logo/<?php echo $logo; ?>" width="100px" class="rounded">

                </div>

                <br>

                <div id="heading1">

                    STUDENT SEMESTER REPORT

                </div>

                <div id="tablewrap">

                    <table class="table  table-bordered table-sm" cellspacing="0"

                        width="100%">

                        <thead>

                            <tr class="text-center">

                                <th class="th-sm text-center">Subject

                                </th>

                                <th class="th-sm text-center">Class Score <br>(30%)

                                </th>

                                <th class="th-sm text-center">Exam Score <br>(70%)

                                </th>

                                <th class="th-sm text-center">Total <br>(100%)

                                </th>

                                <th class="th-sm text-center">Position

                                </th>

                                <th class="th-sm text-center">Remarks

                                </th>

                            </tr>

                        </thead>

                        <tbody>

                            <?php

                            while ($row = mysqli_fetch_array($run)) {

                                $subject = $row['subject'];

                                $class_score1 = $row['class_score1'];

                                $exam_score1 = $row['exam_score1'];

                                $total = $row['total'];

                                $rank = $row['rank'];

                                $remarks = $row['remarks'];

                            ?>

                                <tr>

                                    <td class="text-left">

                                        <?php echo $subject; ?>

                                    </td>

                                    <td class="text-center">

                                        <?php echo $class_score1; ?>

                                    </td>

                                    <td class="text-center">

                                        <?php echo $exam_score1; ?>

                                    </td>

                                    <td class="text-center">

                                        <?php echo $total; ?>

                                    </td>

                                    <td class="text-center">

                                        <?php echo $rank; ?>

                                    </td>

                                    <td class="text-left">

                                        <?php echo $remarks; ?>

                                    </td>

                                </tr>

                            <?php } ?>

                        </tbody>

                        <tfoot>

                        </tfoot>

                    </table>

                </div>

            </div>

Output

 

[sule]

EXPECTED OUTPUT

[Barand]

Use the <> button when posting code

[sule]

1.  

<div class="card shadow">
                        <div class="card-header"><span class="text-primary"></span></div>
                        <div class="card-body">
                            <div class="formwrap">
                                <!--###########updateButton Second Form ###########-->
                                <form action="report.php" method="post" enctype="multipart/form-data" autocomplete="off">
                                    <div class="row form-group">
                                        <?php
                                        $select1 = "SELECT * FROM acayear";
                                        $result1 = mysqli_query($con, $select1);
                                        ?>
                                        <div class="col-sm-3">
                                            <label>Academic Year</label>
                                            <select name="acayear" class="form-select form-select-sm" aria-label=".form-select-sm" required="required">
                                                <?php while ($row = mysqli_fetch_array($result1)):; ?>
                                                    <option value="<?php echo $row[1]; ?>">
                                                        <?php echo $row[1]; ?>
                                                    </option>
                                                <?php endwhile; ?>
                                            </select>
                                        </div>
                                        <?php
                                        $select2 = "SELECT * FROM semester";
                                        $result2 = mysqli_query($con, $select2); ?>
                                        <div class="col-sm-3">
                                            <label>Semseter</label>
                                            <select name="semester" class="form-select form-select-sm" aria-label=".form-select-sm" required="required">
                                                <?php while ($row = mysqli_fetch_array($result2)):; ?>
                                                    <option value="<?php echo $row[1]; ?>">
                                                        <?php echo $row[1]; ?>
                                                    </option>
                                                <?php endwhile; ?>
                                            </select>
                                        </div>
                                        <?php
                                        $select3 = "SELECT * FROM form WHERE form.form<>'PAST' ORDER BY form.form ASC";
                                        $result3 = mysqli_query($con, $select3); ?>
                                        <div class="col-sm-3">
                                            <label>Form</label>
                                            <select name="form" class="form-select form-select-sm" aria-label=".form-select-sm" required="required">
                                                <option value=""></option>
                                                <?php while ($row = mysqli_fetch_array($result3)):; ?>
                                                    <option value="<?php echo $row[1]; ?>">
                                                        <?php echo $row[1]; ?>
                                                    </option>
                                                <?php endwhile; ?>
                                            </select>
                                        </div>
                                        <?php
                                        $select4 = "SELECT * FROM class ORDER BY class.class ASC";
                                        $result4 = mysqli_query($con, $select4); ?>
                                        <div class="col-sm-3">
                                            <label>Select Class</label>
                                            <select name="class" class="form-select form-select-sm" aria-label=".form-select-sm" required="required">
                                                <option value=""></option>
                                                <?php while ($row = mysqli_fetch_array($result4)):; ?>
                                                    <option value="<?php echo $row[2]; ?>">
                                                        <?php echo $row[2]; ?>
                                                    </option>
                                                <?php endwhile; ?>
                                            </select>
                                        </div>
                                    </div>
                                    <div class="mt-2 d-grid gap-2 col-6 mx-auto">
                                        <input name="submit" type="submit" class="btn btn-primary btn-sm btn-responsive" value="View Class">
                                    </div>
                                </form>
                            </div>
                        </div>
                    </div>
                </div>
                <!--###################################################################################################-->
                <div class="heading mt-2">
                    <?php error_reporting(0); ?>
                    <?php
                    if (isset($_POST['submit'])) {
                        $acayear = mysqli_real_escape_string($con, $_POST['acayear']);
                        $semester = mysqli_real_escape_string($con, $_POST['semester']);
                        $form = mysqli_real_escape_string($con, $_POST['form']);
                        $class = mysqli_real_escape_string($con, $_POST['class']);
                    }
                    $select = "SELECT school.school, school.logo, student.gender, student.program, student.house, student.enstatus, student.image, marks.studentid, marks.name, marks.acayear, marks.semester, marks.form, marks.class, marks.subject, marks.class_score1, marks.exam_score1, marks.total, marks.grade, marks.remarks,
                    RANK() OVER (PARTITION BY marks.lessonsid, marks.acayear, marks.form, marks.semester, marks.subject, marks.class ORDER BY marks.total DESC) as rank
                    FROM marks
                    LEFT JOIN student ON marks.studentid=student.studentid
                    LEFT JOIN school ON student.schoolid=school.schoolid
                    WHERE marks.acayear='2024/2025' AND marks.semester='1' AND marks.form='3' AND marks.class='SCI A' ORDER BY marks.name, marks.subject";

                    ?>
                    <div id="heading">
                        <?php echo "$school"; ?>
                    </div>

                    <div id="logo1">
                        <img src="../uploads/student/<?php echo $image; ?>" width="100px" class="rounded">
                    </div>

                    <div id="heading2">
                        <b> STUDENT ID:</b> <?php echo $studentid; ?><?php echo "&nbsp"; ?> <?php echo "&nbsp"; ?> <b>GENDER:</b> <?php echo $gender; ?> <?php echo "&nbsp"; ?> <?php echo "&nbsp"; ?><b>PROGRAM:</b><?php echo "&nbsp"; ?><?php echo $program; ?><br><b>CLASS:</b><?php echo "&nbsp"; ?><?php echo $class; ?> <?php echo "&nbsp"; ?><b>HOUSE:</b> <?php echo "&nbsp"; ?><?php echo $house; ?><?php echo "&nbsp"; ?><b> ACADEMIC YEAR:</b><?php echo "&nbsp"; ?><?php echo $acayear; ?><?php echo "&nbsp"; ?> <br><b> NAME OF STUDENT:</b> <?php echo $name; ?>
                    </div>
                    <div id="logo">
                        <img src="../uploads/logo/<?php echo $logo; ?>" width="100px" class="rounded">
                    </div>
                    <br>
                    <div id="heading1">
                        STUDENT SEMESTER REPORT
                    </div>


                    <div id="tablewrap">
                        <table class="table  table-bordered table-sm" cellspacing="0"
                            width="100%">
                            <thead>
                                <tr class="text-center">
                                    <th class="th-sm text-center">Subject
                                    </th>
                                    <th class="th-sm text-center">Class Score <br>(30%)
                                    </th>
                                    <th class="th-sm text-center">Exam Score <br>(70%)
                                    </th>
                                    <th class="th-sm text-center">Total <br>(100%)
                                    </th>
                                    <th class="th-sm text-center">Grade
                                    </th>
                                    <th class="th-sm text-center">Position
                                    </th>
                                    <th class="th-sm text-center">Remarks
                                    </th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php

                                $run = mysqli_query($con, $select);
                                if (mysqli_num_rows($run) > 0) {
                                    $previd = 0;
                                    foreach ($run as $row) {
                                        if ($studentid != $previd) {
                                            $previd = $studentid;
                                        }
                                ?>
                                        <tr>
                                            <td><?php echo $row['subject']; ?></td>
                                            <td><?php echo $row['class_score1']; ?></td>
                                            <td><?php echo $row['exam_score1']; ?></td>
                                            <td><?php echo $row['total']; ?></td>
                                            <td><?php echo $row['grade']; ?></td>
                                            <td><?php echo $row['rank']; ?></td>
                                            <td><?php echo $row['remarks']; ?></td>

                                        </tr>
                                <?php
                                    }
                                } else {
                                    echo "No Record Found";
                                }

                                ?>
                            </tbody>
                            <tfoot>
                            </tfoot>
                        </table>
                    </div>
                </div>
            </div>
            <!--end::

 

[sule]

The expected out is the above picture. Please the query is correct but i use while loop it display as one page

[sule]

Mr Baran I salute you: I am almost done with the help you offered me. I am left with something small. My first record displayed without the data but on heading, the rest of the data is perfect. see my code

$select = "SELECT school.school, school.logo, student.gender, student.program, student.house, student.enstatus, student.image, marks.studentid, marks.name, marks.acayear, marks.semester, marks.form, marks.class, marks.subject, marks.class_score1, marks.exam_score1, marks.total, marks.grade, marks.remarks,
                    RANK() OVER (PARTITION BY marks.lessonsid, marks.acayear, marks.form, marks.semester, marks.subject, marks.class ORDER BY marks.total DESC) as rank
                    FROM marks
                    LEFT JOIN student ON marks.studentid=student.studentid
                    LEFT JOIN school ON student.schoolid=school.schoolid
                    WHERE marks.acayear='2024/2025' AND marks.semester='1' AND marks.form='3' AND marks.class='SCI A' ORDER BY marks.name, marks.subject";

                        $run = mysqli_query($con, $select);
                         while ($row = mysqli_fetch_array($run)){
                            if ($studentid != $row['studentid']) {
                                $acayear = $row['acayear'];
                                $form = $row['form'];
                                $semester = $row['semester'];
                                $class = $row['class'];
                                $school = $row['school'];
                                $logo = $row['logo'];
                                $studentid = $row['studentid'];
                                $name = $row['name'];
                                $gender = $row['gender'];
                                $program = $row['program'];
                                $class = $row['class'];
                                $house = $row['house'];
                                $image = $row['image'];
                                
                                ?>
                    <div id="heading">
                    <?php echo "$school"; ?>
                    </div>

                    <div id="logo1">
                        <img src="../uploads/student/<?php echo $image; ?>" width="100px" class="rounded">
                    </div>

                    <div id="heading2">
                        <b> STUDENT ID:</b> <?php echo $studentid; ?><?php echo "&nbsp"; ?> <?php echo "&nbsp"; ?> <b>GENDER:</b> <?php echo $gender; ?> <?php echo "&nbsp"; ?> <?php echo "&nbsp"; ?><b>PROGRAM:</b><?php echo "&nbsp"; ?><?php echo $program; ?><br><b>CLASS:</b><?php echo "&nbsp"; ?><?php echo $class; ?> <?php echo "&nbsp"; ?><b>HOUSE:</b> <?php echo "&nbsp"; ?><?php echo $house; ?><?php echo "&nbsp"; ?><b> ACADEMIC YEAR:</b><?php echo "&nbsp"; ?><?php echo $acayear; ?><?php echo "&nbsp"; ?> <br><b> NAME OF STUDENT:</b> <?php echo $name; ?>
                    </div>
                    <div id="logo">
                        <img src="../uploads/logo/<?php echo $logo; ?>" width="100px" class="rounded">
                    </div>

                    <br>
                    <div id="heading1">
                        STUDENT SEMESTER REPORT
                    </div>
                    <div id="tablewrap">
                        <table class="table  table-bordered table-sm" cellspacing="0"
                            width="100%">
                            <thead>
                                <tr class="text-center">
                                    <th class="th-sm text-center">Subject
                                    </th>
                                    <th class="th-sm text-center">Class Score <br>(30%)
                                    </th>
                                    <th class="th-sm text-center">Exam Score <br>(70%)
                                    </th>
                                    <th class="th-sm text-center">Total <br>(100%)
                                    </th>
                                    <th class="th-sm text-center">Grade
                                    </th>
                                    <th class="th-sm text-center">Position
                                    </th>
                                    <th class="th-sm text-center">Remarks
                                    </th>
                                </tr>
                            </thead>

                             <?php }?>  

                            <tbody>
                                <?php
                                $subject = $row['subject'];
                                $class_score1 = $row['class_score1'];
                                $exam_score1 = $row['exam_score1'];
                                $total = $row['total'];
                                $grade = $row['grade'];
                                $rank = $row['rank'];
                                $remarks = $row['remarks'];
                                ?>
                                <tr>
                                    <td class="text-left">
                                        <?php echo $subject; ?>
                                    </td>
                                    <td class="text-center">
                                        <?php echo $class_score1; ?>
                                    </td>
                                    <td class="text-center">
                                        <?php echo $exam_score1; ?>
                                    </td>
                                    <td class="text-center">
                                        <?php echo $total; ?>
                                    </td>
                                    <td class="text-center">
                                        <?php echo $grade; ?>
                                    </td>
                                    <td class="text-center">
                                        <?php echo $rank; ?>
                                    </td>
                                    <td class="text-left">
                                        <?php echo $remarks; ?>
                                    </td>
                                </tr>
                                <?php } ?>
                        </tbody>
                        <tfoot>
                        </tfoot>
                    </table>

 

[mac_gyver]

i recommend indexing/pivoting the data using the 'studentid' as a main array index when you fetch the data. this will give you an array of sub-arrays, with one sub-array for each student. this eliminates the need for conditional logic to test when the student changes and eliminates repetitive code and logic to close out each student section, but not for the very first one, before starting a new student section, and again after the end of the output.

// index/pivot the data using the studentid as the main array index
$data = [];
while ($row = mysqli_fetch_assoc($run))
{
	$data[$row['studentid']][] = $row;
}

this also allows you to use print_r() or var_dump() on the data to make sure it is what you expect, before trying to produce the output.

you can then use two nested foreach loops to produce the output. you also need to define the markup you are trying to produce, before writing any code, something like - 

// foreach data as student

	// reference the zero'th row of student data to get the one-time values for the heading
	// output heading information

	// start table
		// start the thead section
			// output the tr
		// end the thead section

		// start the tbody section
			// foreach student as row
				// output the tr
			// end foreach
		// end the tbody section

		// start the tfoot section
		// end the tfoot section
	// end the table
// end foreach

 

[mac_gyver]

in looking at the markup you are creating, id's must be unique. if you are using the id attributes for styling, you need to use css classes instead.

also, since the thead section is the same, you should build it once, in a php variable, before the start of any looping, then just echo that variable when needed.

[sule]

The table head is not one because each student have a table which need to be looped. I can't just figure out why the first row data appears ones without its tables 

[mac_gyver]

this is the thead section -

                            <thead>
                                <tr class="text-center">
                                    <th class="th-sm text-center">Subject
                                    </th>
                                    <th class="th-sm text-center">Class Score <br>(30%)
                                    </th>
                                    <th class="th-sm text-center">Exam Score <br>(70%)
                                    </th>
                                    <th class="th-sm text-center">Total <br>(100%)
                                    </th>
                                    <th class="th-sm text-center">Grade
                                    </th>
                                    <th class="th-sm text-center">Position
                                    </th>
                                    <th class="th-sm text-center">Remarks
                                    </th>
                                </tr>
                            </thead>

it is the same for every student.

your output is incorrect because the markup you are producing is broken. the code inside the loop is incorrect. the reason I posted an outline of what the code should do is to help you to be able to produce the correct output that will work.

i recommend that you validate the resulting web page at validator.w3.org

Edited March 17 by mac_gyver

[sule]

Thank you for assistance. I am still a newbie who is learning php mysql myself without any mentor. It is my passion of using the web to solve problems at my work place. After my first degree in information technology i wanted to help my people use the web at local level to solve their problems. I am grateful for your response and advice given me. By the way I am from Ghana

Edited March 17 by sule

[Barand]

@sule,

Here are two version of the program

1. A single loop tracking changes in studentid (as I suggested)
2. Stores data in a muti-dimensional array and uses nested foreach() loops

Version 1

<?php
define("HOST", 'localhost');
define("USERNAME", '????');
define("PASSWORD", '????');
define("DATABASE", '????');                         // default database name


function pdoConnect($dbname=DATABASE) 
{
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $db->setAttribute(PDO::MYSQL_ATTR_LOCAL_INFILE, true);
    return $db;
}

$pdo = pdoConnect();

$res = $pdo->query("SELECT 
                        marks.acayear
                      , marks.semester
                      , marks.form
                      , marks.class
                      , marks.studentid
                      , marks.name
                      , marks.subject
                      , marks.class_score1
                      , marks.exam_score1
                      , ROUND(marks.total, 1) as total
                      , marks.grade
                      , marks.remarks
                      , RANK() OVER (PARTITION BY marks.acayear, marks.semester, marks.lessonsid 
                           ORDER BY marks.total DESC) as rank

                    FROM marks

                    WHERE marks.acayear='2024/2025' 
                            AND marks.semester='1' 
                            AND marks.form='3' 
                            AND marks.class='SCI A'
                            
                    ORDER BY name, subject;

                    ");

$previd = 0;
$tdata = '';

foreach ($res as $r)   {
    if ($r['studentid'] != $previd)   {
        if ($previd != 0)  {                                        // if not the first
            $tdata .= "\n</table>\n</div>\n</div>\n\n";             // close preceding report
        }
        $tdata .= outputReportHeading(...array_slice($r, 0, 6));
        $previd = $r['studentid'];
    }
    $tdata .= outputSubject(...array_slice($r, 6));                 // close last report
}
$tdata .= "\n</table>\n</div>\n</div>\n\n";

#
#  FUNCTIONS
#

function outputReportHeading($acayear, $semester, $form, $class, $studentid, $name)
{
    return <<<HEAD
    <div class='report w3-container w3-white'>    
    <div class='report-head'>
        SAVELUGU SENIOR HIGH SCHOOL
    </div>
    <div class='report-head'>
        $acayear ACADEMIC YEAR
    </div>
    <div class='w3-row w3-margin-top'>
        <div class='w3-col m1 w3-center'>
           [SCHOOL<br>CREST]
        </div>
        <div class='w3-col m5 report-subhead'>
           Student ID: <span class='hval'>$studentid</span>
           <br>
           Name: <span class='hval'>$name</span>
           <br>
           Semester: <span class='hval'>$semester</span>
        </div>
        <div class='w3-col m5 report-subhead'>
           House: <span class='hval'></span> 
           <br>
           Gender: <span class='hval'></span>
           <br>
           Class: <span class='hval'>$form $class</span>
        </div>
        <div class='w3-col m1 w3-center'>
           [PHOTO]
        </div>
    </div>
    <div class='report-head w3-margin-top'>
        STUDENT TERMINAL REPORT
    </div>
    <div class='w3-responsive'>
    <table border='1' class='scores'>
        <tr>
            <th>Subject</th>
            <th class='w3-hide-small'>Class Score</th>
            <th class='w3-hide-small'>Exam Score</th>
            <th>Total</th>
            <th>Grade</th>
            <th>Pos</th>
            <th>Remarks</th>
        </tr>
HEAD;
    
}

function outputSubject($subject, $class_score1, $exam_score1, $total, $grade, $remarks, $rank)
{
    return <<<SUB
    <tr>
        <td>$subject</td>
        <td class='w3-hide-small ca'>$class_score1</td>
        <td class='w3-hide-small ca'>$exam_score1</td>
        <td class='ca'>$total</td>
        <td class='ca'>$grade</td>
        <td class='ca'>$rank</td>
        <td>$remarks</td>
    </tr>
SUB;
}


?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Example Reports 1</title>
<meta charset="utf-8">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> 
<style type='text/css'>
    body {
        background-color: #FCF8E8;
    }
    .report {
        margin: 16px;
        padding: 8px;
        border: 1px solid gray;
    }
    .report-head {
        font-size: 18pt;
        font-weight: 600;
        text-align: center;
    }
    .report-subhead {
        font-size: 14pt;
        font-weight: 300;
    }
    .hval {
        font-weight: 600;
        color: blue;
    }
    .scores {
        width: 100%;
        border-collapse: collapse;
    }
    th {
        padding: 8px 2px;
        background-color: gray;
        color: white;
    }
    td {
        padding: 8px 4px;
    }
    .ca {
        text-align: center;
    }
</style>
</head>
<body>
    <?= $tdata ?>
</body>
</html>

Version 2

<?php
define("HOST", 'localhost');
define("USERNAME", '????');
define("PASSWORD", '????');
define("DATABASE", '????');                         // default database name


function pdoConnect($dbname=DATABASE) 
{
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $db->setAttribute(PDO::MYSQL_ATTR_LOCAL_INFILE, true);
    return $db;
}

$pdo = pdoConnect();


$res = $pdo->query("SELECT 
                        marks.acayear
                      , marks.semester
                      , marks.form
                      , marks.class
                      , marks.studentid
                      , marks.name
                      , marks.subject
                      , marks.class_score1
                      , marks.exam_score1
                      , ROUND(marks.total, 1) as total
                      , marks.grade
                      , marks.remarks
                      , RANK() OVER (PARTITION BY marks.acayear, marks.semester, marks.lessonsid 
                           ORDER BY marks.total DESC) as rank

                    FROM marks

                    WHERE marks.acayear='2024/2025' 
                            AND marks.semester='1' 
                            AND marks.form='3' 
                            AND marks.class='SCI A'
                            
                    ORDER BY name, subject;

                    ");

$tdata = '';

#
#  store the data in a 3D array
#
$data = [];
foreach ($res as $r)   {
    if (!isset($data[$r['studentid']]))  {
        $data[$r['studentid']] = [
                                     'head' => array_slice($r, 0, 6),
                                     'scores' => []
                                 ];
    }
    $data[$r['studentid']]['scores'][] = array_slice($r, 6);
}

    # echo '<pre>' . print_r($data, 1) . '</pre>';    // uncomment to view array structure

#
# PROCESS THE ARRAY
#

    foreach ($data as $sdata)  {
        $tdata .= outputReportHeading(...$sdata['head']);
        foreach ($sdata['scores'] as $subdata)  {
            $tdata .= outputSubject(...$subdata);
        }
        $tdata .= "\n</table>\n</div>\n</div>\n\n";
    }


#
#  FUNCTIONS
#

function outputReportHeading($acayear, $semester, $form, $class, $studentid, $name)
{
    return <<<HEAD
    <div class='report w3-container w3-white'>    
    <div class='report-head'>
        SAVELUGU SENIOR HIGH SCHOOL
    </div>
    <div class='report-head'>
        $acayear ACADEMIC YEAR
    </div>
    <div class='w3-row w3-margin-top'>
        <div class='w3-col m1 w3-center'>
           [SCHOOL<br>CREST]
        </div>
        <div class='w3-col m5 report-subhead'>
           Student ID: <span class='hval'>$studentid</span>
           <br>
           Name: <span class='hval'>$name</span>
           <br>
           Semester: <span class='hval'>$semester</span>
        </div>
        <div class='w3-col m5 report-subhead'>
           House: <span class='hval'></span> 
           <br>
           Gender: <span class='hval'></span>
           <br>
           Class: <span class='hval'>$form $class</span>
        </div>
        <div class='w3-col m1 w3-center'>
           [PHOTO]
        </div>
    </div>
    <div class='report-head w3-margin-top'>
        STUDENT TERMINAL REPORT
    </div>
    <div class='w3-responsive'>
    <table border='1' class='scores'>
        <tr>
            <th>Subject</th>
            <th class='w3-hide-small'>Class Score</th>
            <th class='w3-hide-small'>Exam Score</th>
            <th>Total</th>
            <th>Grade</th>
            <th>Pos</th>
            <th>Remarks</th>
        </tr>
HEAD;
    
}

function outputSubject($subject, $class_score1, $exam_score1, $total, $grade, $remarks, $rank)
{
    return <<<SUB
    <tr>
        <td>$subject</td>
        <td class='w3-hide-small ca'>$class_score1</td>
        <td class='w3-hide-small ca'>$exam_score1</td>
        <td class='ca'>$total</td>
        <td class='ca'>$grade</td>
        <td class='ca'>$rank</td>
        <td>$remarks</td>
    </tr>
SUB;
}


?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Example Reports 2</title>
<meta charset="utf-8">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> 
<style type='text/css'>
    body {
        background-color: #FCF8E8;
    }
    .report {
        margin: 16px;
        padding: 16px;
        border: 1px solid gray;
    }
    .report-head {
        font-size: 18pt;
        font-weight: 600;
        text-align: center;
    }
    .report-subhead {
        font-size: 14pt;
        font-weight: 300;
    }
    .hval {
        font-weight: 600;
        color: blue;
    }
    .scores {
        width: 100%;
        border-collapse: collapse;
    }
    th {
        padding: 8px 2px;
        background-color: gray;
        color: white;
    }
    td {
        padding: 8px 4px;
    }
    .ca {
        text-align: center;
    }
</style>
</head>
<body>
    <?= $tdata ?>

</body>
</html>

Samples

[sule]

Woow!!!, the world is full of knowledgeable people. Mr. Barand, how can i learn this coding you have done? I will be grateful if you can support me as the saying goes ''It is better teaching how to fish than giving me fish". Words alone cannot express my happiness. I am so much grateful you. Than you.

[Barand]

BTW, it seems a pity that that those students who attain a C6 grade should receive only an anagram instead of a Credit

[sule]

Please i saw your code but i am not able to use it because you used PDO instead of MySQLI. can you code it in mysqli for me to see if i can use it. Thank you.

<?php
define("HOST", 'localhost');
define("USERNAME", '????');
define("PASSWORD", '????');
define("DATABASE", '????');                         // default database name


function pdoConnect($dbname=DATABASE) 
{
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $db->setAttribute(PDO::MYSQL_ATTR_LOCAL_INFILE, true);
    return $db;
}

$pdo = pdoConnect();

$res = $pdo->query("SELECT 
                        marks.acayear
                      , marks.semester
                      , marks.form
                      , marks.class
                      , marks.studentid
                      , marks.name
                      , marks.subject
                      , marks.class_score1
                      , marks.exam_score1
                      , ROUND(marks.total, 1) as total
                      , marks.grade
                      , marks.remarks
                      , RANK() OVER (PARTITION BY marks.acayear, marks.semester, marks.lessonsid 
                           ORDER BY marks.total DESC) as rank

                    FROM marks

                    WHERE marks.acayear='2024/2025' 
                            AND marks.semester='1' 
                            AND marks.form='3' 
                            AND marks.class='SCI A'
                            
                    ORDER BY name, subject;

                    ");

$previd = 0;
$tdata = '';

foreach ($res as $r)   {
    if ($r['studentid'] != $previd)   {
        if ($previd != 0)  {                                        // if not the first
            $tdata .= "\n</table>\n</div>\n</div>\n\n";             // close preceding report
        }
        $tdata .= outputReportHeading(...array_slice($r, 0, 6));
        $previd = $r['studentid'];
    }
    $tdata .= outputSubject(...array_slice($r, 6));                 // close last report
}
$tdata .= "\n</table>\n</div>\n</div>\n\n";

#
#  FUNCTIONS
#

function outputReportHeading($acayear, $semester, $form, $class, $studentid, $name)
{
    return <<<HEAD
    <div class='report w3-container w3-white'>    
    <div class='report-head'>
        SAVELUGU SENIOR HIGH SCHOOL
    </div>
    <div class='report-head'>
        $acayear ACADEMIC YEAR
    </div>
    <div class='w3-row w3-margin-top'>
        <div class='w3-col m1 w3-center'>
           [SCHOOL<br>CREST]
        </div>
        <div class='w3-col m5 report-subhead'>
           Student ID: <span class='hval'>$studentid</span>
           <br>
           Name: <span class='hval'>$name</span>
           <br>
           Semester: <span class='hval'>$semester</span>
        </div>
        <div class='w3-col m5 report-subhead'>
           House: <span class='hval'></span> 
           <br>
           Gender: <span class='hval'></span>
           <br>
           Class: <span class='hval'>$form $class</span>
        </div>
        <div class='w3-col m1 w3-center'>
           [PHOTO]
        </div>
    </div>
    <div class='report-head w3-margin-top'>
        STUDENT TERMINAL REPORT
    </div>
    <div class='w3-responsive'>
    <table border='1' class='scores'>
        <tr>
            <th>Subject</th>
            <th class='w3-hide-small'>Class Score</th>
            <th class='w3-hide-small'>Exam Score</th>
            <th>Total</th>
            <th>Grade</th>
            <th>Pos</th>
            <th>Remarks</th>
        </tr>
HEAD;
    
}

function outputSubject($subject, $class_score1, $exam_score1, $total, $grade, $remarks, $rank)
{
    return <<<SUB
    <tr>
        <td>$subject</td>
        <td class='w3-hide-small ca'>$class_score1</td>
        <td class='w3-hide-small ca'>$exam_score1</td>
        <td class='ca'>$total</td>
        <td class='ca'>$grade</td>
        <td class='ca'>$rank</td>
        <td>$remarks</td>
    </tr>
SUB;
}


?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Example Reports 1</title>
<meta charset="utf-8">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> 
<style type='text/css'>
    body {
        background-color: #FCF8E8;
    }
    .report {
        margin: 16px;
        padding: 8px;
        border: 1px solid gray;
    }
    .report-head {
        font-size: 18pt;
        font-weight: 600;
        text-align: center;
    }
    .report-subhead {
        font-size: 14pt;
        font-weight: 300;
    }
    .hval {
        font-weight: 600;
        color: blue;
    }
    .scores {
        width: 100%;
        border-collapse: collapse;
    }
    th {
        padding: 8px 2px;
        background-color: gray;
        color: white;
    }
    td {
        padding: 8px 4px;
    }
    .ca {
        text-align: center;
    }
</style>
</head>
<body>
    <?= $tdata ?>
</body>
</html>

 

Edited March 18 by sule

[Barand]

Change

$pdo = pdoConnect();

to

$pdo = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);

Job done.

[Barand]

PS

You aren't doing yourself any favours by insisting on mysqli. PDO is a superior library and easier to use. While you are still learning, put your effort into PDO

[sule]

Please i tried to modified my table with some related tables joins so that i can have the results dynamic but the my table is not appearing again. Please i need your help. How can i contact you privately?

SELECT school.school, school.logo, student.gender, student.program, student.house, student.enstatus, student.image, marks.acayear, marks.semester, marks.form ,marks.class, marks.studentid, marks.name, marks.subject, marks.class_score1, marks.exam_score1,marks.total, marks.grade, marks.remarks, RANK() OVER (PARTITION BY marks.acayear, marks.semester, marks.lessonsid ORDER BY marks.total DESC) as rank FROM marks 
                    LEFT JOIN student ON marks.studentid=student.studentid
                    LEFT JOIN school ON student.schoolid=school.schoolid
                    WHERE marks.acayear='2024/2025' AND marks.semester='1' AND marks.form='3' AND marks.class='SCI A' ORDER BY name, subject