Albus One Albus One - 2 months ago 6
PHP Question

codeigniter group by does not work

I am trying to make a timeline of events which are stored in database:

db table

I have used this code in my model to fetch data

$query = $this->db->select('*');
$query = $this->db->group_by('data');
$query = $this->db->order_by('data', 'desc');
$query = $this->db->get('istorija');
return $query->result();


And then output code i view

<?php
foreach($istorija as $info) {
?>
<li class="time-label">
<span class="bg-red"><?php echo ucfirst(strftime("%B %e, %Y", $info->data)); ?></span>
</li>

<li>
<i class="fa fa-<?php echo $info->ikona; ?> bg-<?php echo $info->fonas; ?>"></i>
<div class="timeline-item">
<span class="time"><i class="fa fa-clock-o"></i> <?php echo strftime("%H:%M", $info->data); ?></span>
<h3 class="timeline-header"><a href="#"><?php echo $info->pavadinimas; ?></a></h3>
<div class="timeline-body">
<?php echo nl2br($info->tekstas); ?>
</div>
</div>
</li>
<?php
}
?>


I expected it to render view by date and events within date i.e

[date 1]
- event 1
- event 2
[date 2]
- event 1


However it displays like this

event list

How can i make it display correctly?

Answer

Group By works perfectly, as you have just demonstrated. If you group by a column that has no repeating data, there is nothing to group. All of your data records are different.

Given that you are not using a date field in your database, but storing the php timestamp for some reason, which does seem a bit strange, I would be surprised if you ever had two timestamps that were identical enough for group by to have any affect whatsoever.

Given the data you have shown, you do not need the group by, indeed it will not work they way you have intended, even with a date field.

Collect the data and sort by ascending timestamp. Then in your display simply set a temp_date variable for the current date you are displaying, ie, September, 6, 2016. Then on the next iteration through the loop, check to see if the date has changed, if it has, display the new date, set the temp variable and continue looping. Something like:

<?php
$temp_date = '';
foreach($istorija as $info) 
{ 
    $this_date = ucfirst(strftime("%B %e, %Y", $info->data);
    if ($this_date != $temp_date) 
    {
        $temp_date = $this_date;

        // output your date header
        ....
    }

    // output your date listing
    ....
}
?>

Hope that helps,

Paul.

Comments