Display values from sql table into the correct html table columns

Question

I have values from my database that I want to display in an html table. The values are data containing information about students' fees payment.

I want to display them on a table to create some kind of a fee collection report or a financial statement.

This is what I want the table to look like:

adm no | class   | student name | Tuition | Transportation | Boarding | Total
------ |---------|--------------|---------|----------------|----------|--------
001    | class 1 | Mary Mordi   |  25000  |                | 5000     | 30000 
025    | class 3 | Daniel Ify   |  35000  |   12000        |          | 47000  

I can't seem to get values properly like this. They are just all over the place, scattered. I'm guessing it could be my query or the foreach loop.

-- Table structure for table student_fees_master

CREATE TABLE `student_fees_master` (
  `id` int(11) NOT NULL,
  `is_system` int(1) NOT NULL DEFAULT 0,
  `student_session_id` int(11) DEFAULT NULL,
  `fee_session_group_id` int(11) DEFAULT NULL,
  `amount` float(10,2) DEFAULT 0.00,
  `is_active` varchar(10) NOT NULL DEFAULT 'no',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Table structure for table fee_session_groups

CREATE TABLE `fee_session_groups` (
  `id` int(11) NOT NULL,
  `fee_groups_id` int(11) DEFAULT NULL,
  `session_id` int(11) DEFAULT NULL,
  `is_active` varchar(10) NOT NULL DEFAULT 'no',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Table structure for table fee_groups

CREATE TABLE `fee_groups` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  `is_system` int(1) NOT NULL DEFAULT 0,
  `description` text DEFAULT NULL,
  `is_active` varchar(10) NOT NULL DEFAULT 'no',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

-- -- Table structure for table fee_groups_feetype

CREATE TABLE `fee_groups_feetype` (
  `id` int(11) NOT NULL,
  `fee_session_group_id` int(11) DEFAULT NULL,
  `fee_groups_id` int(11) DEFAULT NULL,
  `feetype_id` int(11) DEFAULT NULL,
  `session_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `fine_type` varchar(50) NOT NULL DEFAULT 'none',
  `due_date` date DEFAULT NULL,
  `fine_percentage` float(10,2) NOT NULL DEFAULT 0.00,
  `fine_amount` float(10,2) NOT NULL DEFAULT 0.00,
  `is_active` varchar(10) NOT NULL DEFAULT 'no',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Table structure for table student_fees_deposite

CREATE TABLE `student_fees_deposite` (
  `id` int(11) NOT NULL,
  `student_fees_master_id` int(11) DEFAULT NULL,
  `fee_groups_feetype_id` int(11) DEFAULT NULL,
  `amount_detail` text DEFAULT NULL,
  `is_active` varchar(10) NOT NULL DEFAULT 'no',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Model code

 $sql = "SELECT student_fees_master.id,
student_fees_master.student_session_id,
student_fees_master.fee_session_group_id,students.firstname,
students.admission_no,students.lastname,fee_groups.name,
IFNULL(student_fees_deposite.id,0) as `student_fees_deposite_id`,
JSON_VALUE(student_fees_deposite.amount_detail, '$.*.amount') AS `deposit`
FROM `student_fees_master` 
INNER JOIN fee_session_groups on fee_session_groups.id = student_fees_master.fee_session_group_id 
INNER JOIN fee_groups_feetype on  fee_groups_feetype.fee_session_group_id = fee_session_groups.id 
INNER JOIN fee_groups on fee_groups.id=fee_groups_feetype.fee_groups_id 
INNER JOIN feetype on feetype.id=fee_groups_feetype.feetype_id 
LEFT JOIN student_fees_deposite on student_fees_deposite.student_fees_master_id=student_fees_master.id and student_fees_deposite.fee_groups_feetype_id=fee_groups_feetype.id 
INNER JOIN student_session on student_session.id= student_fees_master.student_session_id  
INNER JOIN students on students.id=student_session.student_id 
GROUP BY student_fees_master.id";

 $query = $this->db->query($sql);
        return $query->result();
    } 

controller

function financialreport() {

        if (!$this->rbac->hasPrivilege('balance_fees_report', 'can_view')) {
            access_denied();
        }

        $this->session->set_userdata('top_menu', 'Reports');
        $this->session->set_userdata('sub_menu', 'Reports/finance');
        $this->session->set_userdata('subsub_menu', 'Reports/finance/financialreport');
        $data['title'] = 'student fee';
        $data['title'] = 'student fee';
        $data['sch_setting'] = $this->sch_setting_detail;
        $this->form_validation->set_rules('class_id', $this->lang->line('class'), 'trim|required|xss_clean');
        $this->form_validation->set_rules('section_id', $this->lang->line('section'), 'trim|required|xss_clean');
        
        $data['feedetails'] = $this->studentfeemaster_model->getFullFinancialRecord($fee_session_groups_id, $student_fees_master_id, $fee_groups_feetype_id);

       $data['feegroup'] = $this->studentfeemaster_model->getFeeGroupName();
       
        
        $this->load->view('layout/header', $data);
        $this->load->view('admin/transaction/financialreport', $data);
        $this->load->view('layout/footer', $data);

     }

**view **

 <table class="table table-striped table-hover" id="headerTable">
                                        <thead>
                                            <tr>
       <th class="text text-left" style="border: 1px solid black;"><?php echo $this->lang->line('admission_no'); ?></th>
        <th class="text-left" style="border: 1px solid black;"><?php echo $this->lang->line('class'); ?></th>
       <th class="text text-left" style="border: 1px solid black;"><?php echo $this->lang->line('student_name'); ?></th> 
            <?php
            foreach ($feedetails as $fee_key => $fee_value) {
                                                    ?>
        <th style="border: 1px solid black;"><?php echo $fee_value->name;?> </th>
                                                <?php
                                             ?>
      <th class="text text-left" style="border: 1px solid black;">Total</th>
                                           

</tr>
</thead>
<tbody>
<tr>
            <td style="border: 1px solid black;"><?php echo $fee_value->admission_no; ?></td>
            <td style="border: 1px solid black;"><?php echo "{$fee_value->class} {$fee_value->section}"; ?></td>
        <td style="border: 1px solid black;"><?php echo "{$fee_value->lastname} {$fee_value->firstname}"; ?></td>
       
          <td style="border: 1px solid black;"><?php echo $fee_value->deposit;?> </td>
               
             <td style="border: 1px solid black;"><?php echo $fee_value->amount;?>
                                                            </td>                                  

</tr>
 <?php 

}
?>   
</tbody>
</table>

Answer 1

Check the output of your HTML in your browsers code viewer. It looks like your for loop includes the end of the table header as well as the data, so you are echoing out two table row closures </tr> in each loop, and extra <tbody> elements, etc.

You effectively need two data sets, a list of fields, and a list of people/fees. You can then use one loop to export the header row, and two nested loops to output the data.
You'll need to ensure the data is sorted in the same order as the table fields, and export empty <td></td> pairs where there is no data.
You could obtain the header data by calling array functions on the main data, or via a separate database query.

It might be easier to use a pivot query in the database to transform rows into columns so it is the same format as the table.

Edit: Perhaps something like the following

1. Extract a list of Fee Column Names
2. Aggregate data into an array
3. Output HTML table

(I've used student name here for grouping the data, however you'll want to use your master ID mentioned in your SQL, I missed it as it wasn't in your sample data output)

<?php
 // Sample data
$feedetails = [
    ['id' => 1, 'admissionnumber' => 123, 'firstname'=>'Fred', 'name' => 'tuition', 'deposit'=>1000],
    ['id' => 2, 'admissionnumber' => 123, 'firstname'=>'Fred', 'name' => 'room', 'deposit'=>2000],
    ['id' => 3, 'admissionnumber' => 111, 'firstname'=>'John', 'name' => 'tuition', 'deposit'=>1000],
    ['id' => 4, 'admissionnumber' => 111, 'firstname'=>'John', 'name' => 'car', 'deposit'=>3000],
    ];


    // Get a list of fee types (for columns in html table)
    $feeTypes= array_count_values(array_column($feedetails, 'name'));
// $feeTypes = array(3) { ["tuition"]=> int(2) ["room"]=> int(1) ["car"]=> int(1) } 

    // Aggregate the data against each person
    $fees = [];
    foreach($feedetails as $feedetail){
        $fees[$feedetail['firstname']]['admissionno'] = $feedetail['admissionnumber'];
        $fees[$feedetail['firstname']][$feedetail['name']] = $feedetail['deposit'];
    }

// $feedetail = array(2) {
// ["Fred"]=> array(3) { ["admissionno"]=> int(123) ["tuition"]=> int(1000) ["room"]=> int(2000) }
// ["John"]=> array(3) { ["admissionno"]=> int(111) ["tuition"]=> int(1000) ["car"]=> int(3000) }
//  } 

    // Print table
    ?>
<table class="table table-striped table-hover" id="headerTable">
<thead>
<tr>
  <th class="text text-left" style="border: 1px solid black;">admission_no</th>
    <th class="text text-left" style="border: 1px solid black;">student_name</th> 
  <?php     foreach ($feeTypes as $feeName => $count) {
        echo '<th style="border: 1px solid black;">' . $feeName . '</th>';
            }
?>
    <th class="text text-left" style="border: 1px solid black;">Total</th>
</tr>
</thead>
<tbody>

<?php
// Table Header Above

// Table Body Below
foreach ($fees as $firstname => $feedetail){
    $total = 0;
?>
<tr>
        <td style="border: 1px solid black;"><?php echo $feedetail['admissionno'] ?></td>
        <td style="border: 1px solid black;"><?php echo $firstname; ?></td>
        <?php
        // Go through each fee type printing data, or an empty table grid if no data
        foreach ($feeTypes as $feeName => $count) {
            if(array_key_exists($feeName, $feedetail)) {
                // fee type found for this student, echo data and add to total
                echo '<td style="border: 1px solid black;">' .  $feedetail[$feeName] . '</td>';
                $total += $feedetail[$feeName];
            } // otherwise echo empty table cell
            else echo '<td style="border: 1px solid black;"></td>';
        }
        ?>
        <td style="border: 1px solid black;"><?php echo $total;?>
    </td>
</tr>
<?php
}
?>
</tbody>
</table>