data result group by date in frontend view html in codeinginter

Question

I have table like this. The table result order by date. I'm using codeigniter framework.

I want to print it out above result like below image.

Today, Yesterday can be display using helper function.

Model

function getData(){
  $this->db->order_by('date','desc');
  return $this->db->get('table')->result();
}

Controller

function index(){
  $this->load->model('data_model');
  $this->data['result'] = $this->data_model->getData();

  $this->load->view('dat_view', $this->data);
}

View

<div>
<?php
if($result){
  $date ='';
  foreach($result as $row){

     if(!$date){
       $date = $row->date; 
     }else{
       if($date != $row->date){
         $date = $row->date;
       }
     }

     echo 'Date : ' . $date;
     echo '<li>'.$row->title.'</li>';

  }
}
?>
</div>

above code display like below

Date : 2017-11-28 1. Some title A

Date : 2017-11-28 1. Some title B

Date : 2017-11-28 1. Some title C

Please help me to group this by any function or array or other.

Add GROUP_CONCAT in SELECT https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — GYaN, Nov 28 '17 at 03:40
The thing is I need to list the records, table description column got article data. — Sampath, Nov 28 '17 at 03:45

M Khalid Junaid · Accepted Answer · 2017-11-28T04:42:56.253

Fetching data ordered by date which is fine in view you just need to add some conditional logic to show date only once for same date records

<div>
    <?php
    if ($result) {
        $date = false;
        foreach ($result as $row) {
            if ($row->date != $date) {
                echo 'Date : ' . $date;
                $date = $row->date;
            }
            echo '<li>' . $row->title . '</li>';
        }
    }
    ?>
</div>

There is no need to use GROUP_CONCAT because at some stage it might fail or you need to adjust some Mysql configuration due to character limitation.

As per docs The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet

Or prepare your data in controller like each date array has collection of its title like

$data = array();
foreach ($result as $row) {
    $data[$row->date][] = $row->title;
}

this will produce an array like

array(
'2017-11-28' => array('some title a','some title b'),
'2017-11-29' => array('some title a','some title b')
)

Pass this $data to your view and loop through your array as

<div>
    <?php
    foreach ($data as $date => $titles) {
        echo 'Date : ' . $date;
        foreach ($titles as $title) {
            echo '<li>' . $title . '</li>';
        }
    }
    ?>
</div>

it works well, I like to use second method, but Witch one is the faster ? — Sampath, Nov 28 '17 at 05:27
@SampathSriAnuradha Performance wise first approach is better than second one because it has only single loop to show the desired data while as compare to second approach it involves 3 loops — M Khalid Junaid, Nov 28 '17 at 05:28

score 0 · Answer 2 · answered Nov 28 '17 at 04:08

0

I'm giving you select query... apply it in your code accordingly...

SELECT GROUP_CONCAT(title),date
FROM table_name GROUP BY date ORDER BY date

answered Nov 28 '17 at 04:08

GYaN

2,327
4
19
39

The How can I fetch data in the view. – Sampath Nov 28 '17 at 04:29

data result group by date in frontend view html in codeinginter

2 Answers2