Display grouped lists of values from a database table in CodeIgniter

Question

I want to group items by a value but failed. I have an array and when try to use GROUP BY it only returns first row but I need all rows.

I have to get something like this:

Brazil
- Value 1
- Value 2
- Value 3

Italy

- Value 1
- Value 2
- Value 3
- Value 4

Spain

- Value 1
- Value 2

This is my foreach function which is on model file:

function live() {
    $data = array();
    $this->db->order_by('mins', 'desc');
    $this->db->group_by('country');
    foreach ($this->db->get_where('table_name')->result_array() as $row) {
        $data[] = $row;
    }
    
    return $data;
}

And this is my view file:

<?php foreach($live as $l) { ?>
<div class="country_name"><?php echo $l['country']; ?></div>
<li><?php echo $l['value']; ?></li>
<?php } ?>

Answer 1

group_by is used to fetch aggregate data like the # of rows per country, etc., which is not needed here. You can "group" row values by countries in PHP:

$countries = [];
foreach ($this->db->order_by('mins', 'desc')->get('table_name')->result_array() as $row) {
    $countries[$row['country']][] = $row['value']; //group values by country
}    

You can then iterate the array like this:

<?php 
foreach ($countries as $country_name => $values) {
    echo "<div>$country_name</div>";
    echo '<ul><li>' . implode('</li><li>', $values) . '</li></ul>';
}
?>