How to query the database for multiple ids and display grouped results in the view of a CodeIgniter application?

Question

I am try to run a query in CodeIgniter model. It's working, but when I echo model function query is like below.

SELECT * FROM `table1` WHERE `id` = '17'
SELECT * FROM `table1` WHERE `id` = '20'
SELECT * FROM `table1` WHERE `id` = '21'
SELECT * FROM `table1` WHERE `id` = '22'
SELECT * FROM `table1` WHERE `id` = '23' 

My model function is given below

function get_quick_navi_menu($q_code)
{
    $this->db->select("*");
    $this->db->where('q_id',$q_code);
    $this->db->from("table0");
    $q = $this->db->get();
    //echo $this->db->last_query();
    $final = array();
    if ($q->num_rows() > 0) 
    {
    foreach ($q->result() as $row) {
        $this->db->select("*");
        $this->db->from("table1");
        $this->db->where("id",$row->id);
        $q = $this->db->get();
        echo $this->db->last_query();
        if ($q->num_rows() > 0) {
            $row->children = $q->result();
        }
        array_push($final, $row);
    }
}

I want to run query like below

SELECT * FROM `table1` WHERE `id` = '17,18,19..'

Table Structure

Table0
id   q_id  value1
1      2       4
2      2       5
3      2       6

Table1
t1_id   id  value1   value2
 1      1      2         2
 2      2      5         6
 3      3      8         12

View

<ul class="dropdown-menu" aria-labelledby="dropdownMenu1" style="margin-left: 1px; opacity: .9;">
    <?php foreach ($menus as $menu) { ?>    
        <li class="dropdown-submenu"><a href="#" class="pan-btn" data-look="<?php echo $menu->sceneid;?>"><?php echo $menu->title;?></a>  
            <ul class="dropdown-menu">
                <?php
                if (isset($menu->children)) {
                    foreach ($menu->children as $child) { ?>
                
                        <li><a href="#" class="pan-btn" data-look="<?php echo $child->menu_scene;?>"><?php echo $child->menu_item;?></a></li>
                    <?php }
                }
                ?>
            </ul>
        </li>
    <?php } ?>
</ul>

Controller

$menus = $this->Home_model->get_quick_navi_menu($q_code);
$data = array('menus' => $menus);

Required Output:

Selecting value1 and value2 from table1 according to id from table0.

Answer 1

Get all ids in an array name here ids and use where_in outside the loop

public function get_quick_navi_menu($q_code)
{
    $this->db->select("*");
    $this->db->where('q_id',$q_code);
    $this->db->from("table0");
    $q = $this->db->get();
    $final = array();
    if ($q->num_rows() > 0) 
    {
        foreach ($q->result() as $key => $row) 
        {
            $ids[$key] = $row->id;
            $data[$key] = $row;
        }

        $this->db->select("*");
        $this->db->from("table1");
        $this->db->where_in("id",$ids);
        $q = $this->db->get();
        //echo $this->db->last_query();
        if ($q->num_rows() > 0) 
        {
            if ( ! empty($data))
            {
                foreach ($data as $key => $item) 
                {
                    $item->children = $q->result();
                    $final[] = $item;
                }
            }
            
        }
    }
    return $final;
    /*print_r($final);*/
}

In your controller class:

make sure you have loaded your model and database in controller or in autoload.php

 $q_code = 'q_code_value';
 $data['menus'] =$this->Home_model->get_quick_navi_menu($q_code);
 /* pass the $data in the view like this*/
 $this->load->view('your_view_file_path',$data);

In your view :

<div><?php print_r($records);?></div>

For more : https://www.codeigniter.com/user_guide/database/query_builder.html#looking-for-specific-data

Answer 2

I recommend executing only one query on the database total and using PHP to build a multidimensional array. Use where_in() to pass an array of ids to your WHERE clause. Using a LEFT JOIN will ensure that even "child-less" scenes are represented in your array. A regular JOIN (INNER JOIN) will exclude scenes which have no children in the second table.

public function sceneGrouper(array $qCodes): array
{
    $result = $this->db
        ->select("t0.id AS sceneid, t0.value1 AS title, t1.id AS child_id, t1.value1 AS menu_scene, t1.value2 AS menu_item")
        ->from("table0 t0")
        ->join("table1 t1", "t1.id = t0.id", "left")
        ->where_in("t0.q_id", $qCodes)
        ->get()
        ->result();

    $grouped = [];
    foreach ($result as $row) {
        $sceneId = $row->sceneId;
        $grouped[$sceneId] ??= (object) [
            'sceneId' => $sceneId,
            'title' => $row->title,
            'children' => []
        ];

        if ($row->child_id) {  // don't bother adding empty children
            $grouped[$sceneId]->children[] = (object) [
                'id' => $row->child_id,
                'menuScene' => $row->menu_scene,
                'menuItem' => $row->menu_item
            ];
        }
    }
    return array_values($grouped);
}

Your controller should pass the payload from the model to the view.

public function somePage()
{
    $yourCodes = ['17', '20', '21', '22', '23'];
    $data['scenes'] = $this->Home_model->get_quick_navi_menu($yourCodes);
    $this->load->view('scenes_menu', $data);
}

In your view, loop over the grouped data. Move all of your inline styling to external stylesheets so that your view is more readable and easier to manage.

<ul class="dropdown-menu" aria-labelledby="dropdownMenu1">
    <?php foreach ($scenes as $scene) { ?>  
        <li class="dropdown-submenu">
            <a href="#" class="pan-btn" data-look="<?php echo $scene->sceneid; ?>">
                <?php echo $scene->title; ?>
            </a>  
            <ul class="dropdown-menu">
                <?php foreach ($menu->children ?? [] as $child) { ?>
                    <li>
                        <a href="#" class="pan-btn" data-look="<?php echo $child->menuScene; ?>">
                            <?php echo $child->menuItem; ?>
                        </a>
                    </li>
                <?php } ?>
            </ul>
        </li>
    <?php } ?>
</ul>

Answer 3

Use this function in controller

public function getTableData()
    {
        $this->db->select('GROUP_CONCAT(id) as id');
        $tbl0 = $this->db->get('table0')->row_array();
        if($tbl0) {
                $ids = explode(',', $tbl0['id']);
                $this->db->where_in('id', $ids);
                $tbl1 = $this->db->get('table1')->result_array();
                echo "<pre>"; print_r($tbl1);

        }
    }