dwiyanto dwiyanto - 3 months ago 7
PHP Question

Group by tables with different fields based on the year on the date on CodeIgniter

I have one table with 3 data with the same id, id each have a value with type a different date.

id | name | date | status |
------ | ------- | ----------- | --------- |
1 | steve | 2014-10-12 | ok |
2 | steve | 2015-09-09 | bad |
3 | steve | 2016-01-10 | ok |


I want to display the data in tabular form based on the year, .

id | name | 2014 | 2015 | 2016
------ | ------ | ----------- | ------------ | -----------
1 | steve | 2014-10-12 | 2015-09-09 | 2016-01-10


but after I run the appropriate script I compose the result is not as I expected.

id | name | 2014 | status | 2015 | status | 2016 | status
------ | ------ | ----------- | ------ | ------------ | ------ | ---------- | -
1 | steve | 2014-10-12 | ok | - | - | - | -
2 | steve | - | - | 2015-09-09 | bad | - | -
3 | steve | - | - | - | - | 2016-01-10 | ok


is there who can help me to solve this problem? I use CodeIgniter framework.
This my script:

Controllers

public function index()
{
$data['deptlist'] = $this->mcrud->viewkal();
$data=array('deptlist' => $this->mcrud->viewkal(),
'isi' =>'kalibrasi/v_kalibrasi');
$this->load->view('layout/wrapper', $data);

}


Models

function viewkal() {
$this->db->order_by('name','ASC');
$this->db->group_by('name');
$query = $this->db->get('mytabel');
return $query->result();
}


Views

<table>
<thead>
<tr>
<th>Name</th>
<th><?php echo date('Y', strtotime('-2 year')); ?></th>
<th><?php echo date('Y', strtotime('-1 year')); ?></th>
<th><?php echo date('Y', strtotime('0 year')); ?></th>
</tr>
</thead>
<tbody>
<?php
foreach ($deptlist as $row ) {
?>
<tr>
<td><?php echo $row->id_alat; ?></td>
<td><?php echo $row->nama_alat; ?></td>
<?php
$result = $row->tanggal;
$data = strtotime($row->tanggal);
$tahun = date('Y',$data);

if ( $tahun == date('Y', strtotime('-3 year'))) {
echo '<td>'.$result.'</td>';
} else {
echo '<td> - </td>';
}

if ( $tahun == date('Y', strtotime('-2 year'))) {
echo '<td>'.$result.'</td>';
} else {
echo '<td> - </td>';
}

if ( $tahun == date('Y', strtotime('-1 year'))) {
echo '<td>'.$result.'</td>';
} else {
echo '<td> - </td>';
}

if ( $tahun == date('Y', strtotime('0 year'))) {
echo '<td>'.$result.'</td>';
} else {
echo '<td> - </td>';
}

if ( $tahun == date('Y', strtotime('-1 year'))) {
echo '<td>'.$result.'</td>';
} else {
echo '<td> - </td>';
}
?>
<?php } ?>
</tr>
</tbody>



Answer

So, your modal should be:

function viewkal()  {
    $query = $this->db
            ->select('`id`, `name`, GROUP_CONCAT(`date`) as `date_string`')
            ->group_by('name')
            ->order_by('name','ASC')
            ->get('mytabel');
    return $query->result();
}

Your controller:

public function index()
{
    $this->load->model('mcrud');
    $data=array('deptlist'  => $this->mcrud->viewkal(),
            'isi'       =>'kalibrasi/v_kalibrasi');
    $this->load->view('layout/wrapper', $data);
}

And your view:

<table>
    <thead>
    <tr>
        <th>Name</th>
        <th><?php echo date('Y', strtotime('-2 year')); ?></th>
        <th><?php echo date('Y', strtotime('-1 year')); ?></th>
        <th><?php echo date('Y', strtotime('0 year')); ?></th>
    </tr>
    </thead>
    <tbody>
    <?php
    foreach ($deptlist as $row) {
        ?>
        <tr>
            <td><?php echo $row->name; ?></td>
            <?php
            for ($year = date('Y', strtotime('-2 year')); $year <= date('Y'); $year++) {
                $yearFind = false;
                foreach (explode(',', $row->date_string) as $yearRow) {
                    if (strpos($yearRow, (string)$year) === 0) {
                        echo '<td>' . $yearRow . '</td>';
                        continue 2;
                    }
                }
                if (!$yearFind) {
                    echo '<td> - </td>';
                }
            }
            ?>
        </tr>
    <?php } ?>

    </tbody>
</table>
Comments