Looping DATETIME and keeping the DATES with the DATETIME inside

Question

I have a table that looks like this:

|    last_update    | res_id | group_id |
|2015-07-24 08:51:38| 1131   |  64654   | 
|2015-07-24 08:55:38| 1131   |  64654   | 
|2015-07-25 08:51:38| 1131   |  64654   | 
|2015-07-25 08:40:38| 1131   |  64654   | 

I am trying to loop only the dates, because my application needs to show activity for each day seperatly and inside the day all the activity of that day! how would i do that? 2 loops? I tried:

Model:

    function get_user_logs ()
{

    $query = $this->db->select('*')->from('user_logs')->order_by('last_update', 'DESC')->get();
    $results = $query->result_array();

    foreach ($results as $key => $result) {
        $new[$key] = $result;
    }
    return $new;
}

function get_user_logs_bydates () {

    //DISTINCT to select only unique dates CAST to get them as dates.
    $query = ('SELECT DISTINCT CAST(last_update as date) FROM user_logs');
    $data = $this->db->query($query);
    $array = $data->result_array();
    return $array;
}

Controller:

public function changelog ()
{
    $data['dates'] = $this->Reservations_model->get_user_logs_bydates();
    $data['logs']  = $this->Reservations_model->get_user_logs();

    $this->load->view('abrupt/reservations/view_changelog', $data);
}

View:

<div class="tm-body">
        <ol class="tm-items">
            <?php foreach($dates as $date) { ?>
            <li>
                <div class="tm-box">
                    <h4><?php print $date['CAST(last_update as date)']; ?></h4>
                    <ul class="list-unstyled">

                        <?php foreach ($logs as $log) { ?>

                        <li><?php print_r($log); ?></li>
                        <?php } ?>
                    </ul>
                </div>
            </li>
            <?php } ?>
        </ol>
    </div>

Output will be:

<h4>2015-07-25</h4>

Array(
[last_update] => 2015-07-24 08:51:38,
[res_id] => 1131,
[group_id] => 64654
);

Array(
[last_update] => 2015-07-24 08:55:38,
[res_id] => 1131,
[group_id] => 64654
);

Array(
[last_update] => 2015-07-25 08:51:38,
[res_id] => 1131,
[group_id] => 64654
);

Array(
[last_update] => 2015-07-25 08:40:38
[res_id] => 1131,
[group_id] => 64654
);


<h4>2015-07-24</h4>

Array(
[last_update] => 2015-07-24 08:51:38,
[res_id] => 1131,
[group_id] => 64654
);

Array(
[last_update] => 2015-07-24 08:55:38,
[res_id] => 1131,
[group_id] => 64654
);

Array(
[last_update] => 2015-07-25 08:51:38,
[res_id] => 1131,
[group_id] => 64654
);

Array(
[last_update] => 2015-07-25 08:40:38
[res_id] => 1131,
[group_id] => 64654
);

I am trying to get:

<h4>2015-07-25</h4>

Array(
[last_update] => 2015-07-25 08:51:38,
[res_id] => 1131,
[group_id] => 64654
);

Array(
[last_update] => 2015-07-25 08:40:38
[res_id] => 1131,
[group_id] => 64654
);


<h4>2015-07-24</h4>

Array(
[last_update] => 2015-07-24 08:51:38,
[res_id] => 1131,
[group_id] => 64654
);

Array(
[last_update] => 2015-07-24 08:55:38,
[res_id] => 1131,
[group_id] => 64654
);

Any help is appreciated! Thanks!

Answer 1

While fetching the results, get the date part from last_update field.

Then, create sub-arrays of records with this key.

Try to print this with print_r($new);, it will work.

function get_user_logs () {
  $query = $this->db->select('*')->from('user_logs')->order_by('last_update', 'DESC')->get();
  $results = $query->result_array();
  $new = array();
  foreach ($results as $key => $result) {
    $dateVal = substr($result['last_update'], 0, 11);
    $new[$dateVal][] = $result;
  }
   return $new;
}

And in view:

<?php
if (! empty($new)) {
  foreach ($new as $dateVal => $record) {
    echo '<h3>'.$dateVal.'</h3>';
    echo '<pre>';print_r($record);echo '</pre>';
  }
}
?>

You are actually creating a three-dimensional array.

You have an array for each record.

We are grouping the records' arrays with their dates.

Now, this group of multi-dimensional arrays is put under main array.

Its simple, we are looping through single dimensional array to get it multi-dimensional.

Answer 2

Try to update your query as

$query = ('SELECT DISTINCT CAST(last_update as date) FROM user_logs ORDER BY last_update DESC');