CODEWITHSUNDEEP
phpcodeigniter
irwan dwiyanto
irwan dwiyanto

Reputation: 690

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

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

  id   |   name  |    date     |   status  |
------ | ------- | ----------- | --------- |
  1    |  steve  |  2015-09-12 | scheduled |
  2    |  steve  |  2015-09-09 |   actual  |
  3    |  steve  |  2016-01-10 | scheduled |
  4    |  steve  |  2016-01-21 |   actual  |

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

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

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>

Upvotes: 1

Views: 131

Answers (2)

Lakremon
Lakremon

Reputation: 797

To add status check it code: model:

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

controller:

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

view:

<table>
    <thead>
    <tr>
        <th rowspan="2">Name</th>
        <th colspan="2"><?php echo date('Y', strtotime('-2 year')); ?></th>
        <th colspan="2"><?php echo date('Y', strtotime('-1 year')); ?></th>
        <th colspan="2"><?php echo date('Y', strtotime('0 year')); ?></th>
    </tr>
    <tr>
        <th>scheduled</th>
        <th>actual</th>
        <th>scheduled</th>
        <th>actual</th>
        <th>scheduled</th>
        <th>actual</th>
    </tr>
    </thead>
    <tbody>
    <?php
    foreach ($deptlist as $row) {
        ?>
        <tr>
            <td><?php echo $row->name; ?></td>
            <?php
            $status_array = explode(',', $row->status_string);
            $date_array = explode(',', $row->date_string);
            for ($year = date('Y', strtotime('-2 year')); $year <= date('Y'); $year++) {
                $scheduledValue = false;
                $actualValue = false;
                foreach ($date_array as $dateKey => $yearRow) {
                    if (strpos($yearRow, (string)$year) === 0) {
                        if ($status_array[$dateKey] == 'scheduled') {
                            $scheduledValue = $yearRow;
                        } elseif ($status_array[$dateKey] == 'actual') {
                            $actualValue = $yearRow;
                        }
                    }
                }
                if ($scheduledValue) {
                    echo "<td>{$scheduledValue}</td>";
                } else {
                    echo '<td> - </td>';

                }
                if ($actualValue) {
                    echo "<td>{$actualValue}</td>";
                } else {
                    echo '<td> - </td>';

                }
            }
            ?>
        </tr>
    <?php } ?>

    </tbody>
</table>

Upvotes: 1

Lakremon
Lakremon

Reputation: 797

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>

Upvotes: 1

Related Questions