Combine 4 queries into one-CodePudding

I've 4 queries in my codeigniter-3 project and it's taking forever to load. I want to combine it to one query.

Is this possible to combine 4 queries into one? Thanks in advance

This is my controller code.

// Query 1
$getMenus = $this->db->query("SELECT * FROM table_name WHERE status = '1' AND type = 'starter' ORDER BY id DESC");
$allMenus = $getMenus->result_array();
$data['all_starter_menu'] = $allMenus;

// Query 2
$getMenus = $this->db->query("SELECT * FROM table_name WHERE status = '1' AND type = 'main_course' ORDER BY id DESC");
$allMenus = $getMenus->result_array();
$data['all_maincourse_menu'] = $allMenus;

// Query 3
$getMenus = $this->db->query("SELECT * FROM table_name WHERE status = '1' AND type = 'deserts' ORDER BY id DESC");
$allMenus = $getMenus->result_array();
$data['all_deserts_menu'] = $allMenus;

// Query 4
$getMenus = $this->db->query("SELECT * FROM table_name WHERE status = '1' AND type = 'drinks' ORDER BY id DESC");
$allMenus = $getMenus->result_array();
$data['all_drinks_menu'] = $allMenus;

My View Code

/ Query 1 foreach loop
<?php foreach ($all_starter_menu as $menu) { ?>
    <div >
        <h3><?= $menu['name'] ?> </h3>
        <strong><?= $menu['price'] ?></strong>
    </div>
<? } ?>

// Query 2 foreach loop
<?php foreach ($all_maincourse_menu as $menu) { ?>
    <div >
        <h3><?= $menu['name'] ?> </h3>
        <strong><?= $menu['price'] ?></strong>
    </div>
<? } ?>

// Query 3 foreach loop

...

// Query 4 foreach loop

...

CodePudding user response：

Something like this should work (not tested though):

$getMenus = $this->db->query("SELECT * FROM x_menu WHERE menu_status = '1' AND type IN ('starter', 'main_course', 'deserts', 'drinks') ORDER BY id DESC");
$allMenus = $getMenus->result_array();

$data = [];

foreach ($allMenus as $menu) {
    $data['all_' . str_replace('_', '', $menu['type']) . '_menu'][] = $menu;
}

This gets all of the rows for the 4 different types (starter, main course, deserts, drinks), then loops over the result and sorts each row into the correct subarray of $data based on its type value.

You can leave off the AND type IN ('starter', 'main_course', 'deserts', 'drinks') part of the query if these 4 types are the only types in the table.