How to hide repeating part of the retrieved data values using php and codeigniter

Question

I have obtained following table as a result of this MySQL statement

SELECT * FROM expenses, income 
WHERE expenses.projectname=income.projectname 
AND expenses.task=income.task

These are the fields in my project table

These are the fields of my task table

In this table, one project has many tasks. Therefore project, client, project start and end date column repeat meaninglessly. How can I show them only once for all tasks? How can I apply PHP hide logic here? Following diagram shows what I need to achieve. Data are retrieved through MySQL query. But how can I hide unnecessary values repeating This is the CodeIgniter view page

<table class="table table-lg">
<thead >
    <tr class="filters">

       <th><input type="text" class="form-control" placeholder="Project" disabled></th>
        <th><input type="text" class="form-control" placeholder="Employee" disabled></th>
        <th><input type="text" class="form-control" placeholder="Task" disabled></th>
        <th><input type="text" class="form-control" placeholder="Expense" disabled></th>
        <th><input type="text" class="form-control" placeholder="Amount" disabled></th>
        <th><input type="text" class="form-control" placeholder="Paid/Not" disabled></th>
        <th><input type="text" class="form-control" placeholder="Client" disabled></th>
        <th><input type="text" class="form-control" placeholder="Cost" disabled></th>
        <th><input type="text" class="form-control" placeholder="Income " disabled></th>
        <th><input type="text" class="form-control" placeholder="Date" disabled></th>

    </tr>
</thead>
<tbody>
<?php
if(isset($view_data1) && is_array($view_data1) && count($view_data1)): $i=1;
foreach ($view_data1 as $key => $data) { 
?>

<tr <?php if($i%2==0){echo 'class="even"';}else{echo'class="odd"';}?>>

    <td><?php echo $data['projectname']; ?></td> 
    <td><?php echo $data['employee']; ?></td> 
    <td><?php echo $data['task']; ?></td> 
    <td><?php echo $data['ExpenseName']; ?></td> 
    <td><?php echo $data['ExpenseAmount']; ?></td>
    <td><?php echo $data['pn']; ?></td>  
   <td><?php echo $data['cname']; ?></td>
   <td><?php echo $data['taskcost']; ?></td> 
   <td><?php echo $data['amount']; ?></td> 
   <td><?php echo $data['datetimepicker_mask']; ?></td> 
</tr>
<?php
    $i++;
      }
    else:
?>
<tr>
    <td colspan="7" align="center" >No Records Found..</td>
</tr>
<?php
    endif;
?>
</tbody>                
</table>

Answer 1

I didn't test this code, but the idea is to remember the last key (projectname and employee in this case) and then compare it to the current key.

The fields in your sample code don't match your Word doc example table so I just went with your code sample.

    <tbody>

        <?php
        if (isset($view_data1) && is_array($view_data1) && count($view_data1)) {

            $i = 1;
            $last_key = '';

            foreach ($view_data1 as $key => $data) {

                $stripe = 'odd';
                if ($i % 2 == 0) {
                     $stripe = 'even';
                }
                echo "<tr class='$stripe'>";
                $i++;

                $current_key = $data['projectname'] . $data['employee'];

                if ($current_key !== $last_key) {

                    echo "<td>" . $data['projectname'] . "</td>";
                    echo "<td>" . $data['employee'] . "</td>";
                } else {

                    echo "<td colspan='2'></td>";
                }

                $last_key = $current_key;
                ?>

            <td><?php echo $data['task']; ?></td>
            <td><?php echo $data['ExpenseName']; ?></td>
            <td><?php echo $data['ExpenseAmount']; ?></td>
            <td><?php echo $data['pn']; ?></td>
            <td><?php echo $data['cname']; ?></td>
            <td><?php echo $data['taskcost']; ?></td>
            <td><?php echo $data['amount']; ?></td>
            <td><?php echo $data['datetimepicker_mask']; ?></td>
        </tr>
        <?php
    }
} else {
    ?>
    <tr><td colspan="10" align="center" >No Records Found..</td></tr>
    <?php
}
?>

</tbody>
</table>

Answer 2

don't select two table at the time. join them with the condition.

SELECT expenses.*,income.*,expenses.id as p_id FROM expenses
join income ON expenses.task=income.task AND expenses.projectname=income.projectname

change your view foreach section

<table class="table table-lg">
<thead >
    <tr class="filters">

       <th ><input type="text" class="form-control" placeholder="Project" disabled></th>
        <th ><input type="text" class="form-control" placeholder="Employee" disabled></th>
        <th ><input type="text" class="form-control" placeholder="Task" disabled></th>
        <th ><input type="text" class="form-control" placeholder="Expense" disabled></th>
        <th ><input type="text" class="form-control" placeholder="Amount" disabled></th>
        <th ><input type="text" class="form-control" placeholder="Paid/Not" disabled></th>
        <th ><input type="text" class="form-control" placeholder="Client" disabled></th>
        <th ><input type="text" class="form-control" placeholder="Cost" disabled></th>
        <th ><input type="text" class="form-control" placeholder="Income " disabled></th>
        <th ><input type="text" class="form-control" placeholder="Date" disabled></th>
    </tr>
</thead>
<tbody>
<?php
if(isset($view_data1) && is_array($view_data1) && count($view_data1)): $i=1;
$is_exists=array();
foreach ($view_data1 as $key => $data) { 
?>

<tr <?php if($i%2==0){echo 'class="even"';}else{echo'class="odd"';}?>>
<?php
    if(!in_array($data['p_id'], $is_exists)){
        $is_exists[]=$data['p_id'];
    ?>
        <td><?php echo $data['projectname']; ?></td> 
        <td><?php echo $data['employee']; ?></td> 
        <td><?php echo $data['task']; ?></td> 
        <td><?php echo $data['ExpenseName']; ?></td> 
    <?php
    }else{
     echo "<td rowspan='4'></td>";
    }
    ?>

   <td><?php echo $data['ExpenseAmount']; ?></td>
   <td><?php echo $data['pn']; ?></td>  
   <td><?php echo $data['cname']; ?></td>
   <td><?php echo $data['taskcost']; ?></td> 
   <td><?php echo $data['amount']; ?></td> 
   <td><?php echo $data['datetimepicker_mask']; ?></td> 
</tr>
<?php
    $i++;
      }
    else:
?>
<tr>
    <td colspan="7" align="center" >No Records Found..</td>
</tr>
<?php
    endif;
?>

</tbody>                
</table>

Answer 3

Don't merge tables like that. Use join query inside your models and call it in views through controllers

public function getExpenses(){
     $this->db->select("addexpense.exp_date, addexpense.exp_amount, addexpense.exp_note, addexpense.exp_created, addcategory.category_name");
     $this->db->from('addexpense');
     $this->db->join('addcategory', 'addcategory.category_id = addexpense.category_id');
     $query = $this->db->get();
     return $query->result();
}