php - 在codeigniter中显示多个具有相同组名的记录

我想在codeigniter中用数据库中的select填充optgroup下拉列表，因为我编写了以下内容：

表：

+---+-------------+--------------+
|id | group_name  | location_name|
+---+-------------+--------------+
| 1 | top cities  | Bangalore    |
|   |             |              |
| 2 | top cities  | Chennai      |
|   |             |              |
| 3 | big cities  | Mumbai       |
|   |             |              |
| 4 | small cities| mumbai       |
|   |             |              |
| 5 | small cities| mumbai       |
+---+-------------+--------------+

需求：

           <select id="location" name="location">
                <option value="" selected="selected">Select Location</option>
                <optgroup label="top cities">
                    <option value="Bangalore">Bangalore</option>
                    <option value="Chennai">Chennai</option>
                </optgroup>
                <optgroup label="big cities">
                    <option value="Mumbai">Mumbai</option>
                </optgroup>
                <optgroup label="small cities">
                    <option value="Mumbai">Mumbai</option>
                    <option value="Mumbai">Mumbai</option>
                </optgroup>
           </select>

模型：

 function get_location() {
    $this->db->select('group_name,GROUP_CONCAT(location_name) AS locations');
    $this->db->order_by("group_name");
    $this->db->from('location');
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        foreach ($query->result() as $row) {
            $data[] = $row;
        }
        return $data;
    }
    return false;
}

视图：

            <select id="location" name="location">
                <option value="" selected="selected">Select Location</option>
                <?php
                $CI = & get_instance();
                $CI->load->model('Common', 'Common', TRUE);
                $results = $CI->Common->get_location();
                foreach ($results as $row) {
                    echo '<optgroup label="'.$row->group_name.'">';

                    $location = explode(',', $row->locations);
                    foreach ($location as $loc) {
                        echo '<option value="'.$loc.'">'.$loc.'</option>';
                    }

                    echo "</optgroup>";
                }
                ?>
            </select>

但是结果与我要求的不同...

输出：

        <select id="location" name="location">
            <option value="" selected="selected">Select Location</option>
            <optgroup label="top cities">
                <option value="Bangalore">Bangalore</option>
                <option value="Chennai">Chennai</option>
                <option value="Mumbai">Mumbai</option>
                <option value="Mumbai">Mumbai</option>
                <option value="Mumbai">Mumbai</option>
            </optgroup>
       </select>

请帮助任何人...在此先感谢

最佳答案

这是因为您的查询是错误的。您应该在查询中添加以下行：

    $this->db->group_by('group_name');

另外，您不应该从视图中访问模型。您应该使用控制器从模型中获取数据并将其传递给视图。