如何在另一个表列中插入一个表数据

Question

I have table server_menuresponse with columns id menu_order menu_name menu_link data_icon sub_menu and another table sub_menu with columns id mainmenu_id submenu_order submenu_name submenu_link submenu_icon

I want to get all the columns of sub_menu table inside sub_menu column of server_menuresponse

I want php result should be like:

<?php
stdClass::__set_state(array(

   'server_menuresponse' => 
  array (
    0 => 
    stdClass::__set_state(array(
       'id' => '1',
       'menu_order' => '1',
       'menu_name' => 'Home',
       'menu_link' => '',
       'data_icon' => '',
       'sub_menu' => 
      array (
      ),
    )),
    1 => 
    stdClass::__set_state(array(
       'id' => '2',
       'menu_order' => '2',
       'menu_name' => 'New',
       'menu_link' => '',
       'data_icon' => '',
       'sub_menu' => 
      array (
        0 => 
        stdClass::__set_state(array(
           'id' => '5',
           'mainmenu_id' => '2',
           'submenu_order' => '3',
           'submenu_name' => 'Auto',
           'submenu_link' => '',
           'submenu_icon' => 'menu_icon/auto.png',
        )),
        1 => 
        stdClass::__set_state(array(
           'id' => '4',
           'mainmenu_id' => '2',
           'submenu_order' => '2',
           'submenu_name' => 'Bike',
           'submenu_link' => '',
           'submenu_icon' => 'menu_icon/bike.png',
        )),
        2 => 
        stdClass::__set_state(array(
           'id' => '6',
           'mainmenu_id' => '2',
           'submenu_order' => '4',
           'submenu_name' => 'Bus',
           'submenu_link' => '',
           'submenu_icon' => 'menu_icon/bus.png',
        )),
        3 => 
        stdClass::__set_state(array(
           'id' => '3',
           'mainmenu_id' => '2',
           'submenu_order' => '1',
           'submenu_name' => 'Car',
           'submenu_link' => '',
           'submenu_icon' => 'menu_icon/car.png',
        )),
        4 => 
        stdClass::__set_state(array(
           'id' => '1',
           'mainmenu_id' => '2',
           'submenu_order' => '6',
           'submenu_name' => 'Find All New Vehicle',
           'submenu_link' => '',
           'submenu_icon' => 'menu_icon/new_vehicle.png',
        )),
        5 => 
        stdClass::__set_state(array(
           'id' => '7',
           'mainmenu_id' => '2',
           'submenu_order' => '5',
           'submenu_name' => 'Lorry',
           'submenu_link' => '',
           'submenu_icon' => 'menu_icon/lorry.png',
        )),
        6 => 
        stdClass::__set_state(array(
           'id' => '2',
           'mainmenu_id' => '2',
           'submenu_order' => '7',
           'submenu_name' => 'New Vehicle Offer',
           'submenu_link' => '',
           'submenu_icon' => 'menu_icon/new_offer.png',
        )),
      ),
    )),
    2 => 
    stdClass::__set_state(array(
       'id' => '3',
       'menu_order' => '3',
       'menu_name' => 'Used',
       'menu_link' => '',
       'data_icon' => '',
       'sub_menu' => 
      array (
        0 => 
        stdClass::__set_state(array(
           'id' => '11',
           'mainmenu_id' => '3',
           'submenu_order' => '3',
           'submenu_name' => 'Auto',
           'submenu_link' => '',
           'submenu_icon' => 'menu_icon/auto.png',
        )),
        1 => 
        stdClass::__set_state(array(
           'id' => '10',
           'mainmenu_id' => '3',
           'submenu_order' => '2',
           'submenu_name' => 'Bike',
           'submenu_link' => '',
           'submenu_icon' => 'menu_icon/bike.png',
        )),
        2 => 
        stdClass::__set_state(array(
           'id' => '12',
           'mainmenu_id' => '3',
           'submenu_order' => '4',
           'submenu_name' => 'Bus',
           'submenu_link' => '',
           'submenu_icon' => 'menu_icon/bus.png',
        )),
        3 => 
        stdClass::__set_state(array(
           'id' => '9',
           'mainmenu_id' => '3',
           'submenu_order' => '1',
           'submenu_name' => 'Car',
           'submenu_link' => '',
           'submenu_icon' => 'menu_icon/car.png',
        )),
        4 => 
        stdClass::__set_state(array(
           'id' => '8',
           'mainmenu_id' => '3',
           'submenu_order' => '5',
           'submenu_name' => 'Find All Used Vehicle',
           'submenu_link' => '',
           'submenu_icon' => 'menu_icon/new_vehicle.png',
        )),
      ),
    )),
    3 => 
    stdClass::__set_state(array(
       'id' => '4',
       'menu_order' => '4',
       'menu_name' => 'News & Reviews',
       'menu_link' => '',
       'data_icon' => '',
       'sub_menu' => 
      array (
        0 => 
        stdClass::__set_state(array(
           'id' => '15',
           'mainmenu_id' => '4',
           'submenu_order' => '3',
           'submenu_name' => 'Car Videos',
           'submenu_link' => '',
           'submenu_icon' => '',
        )),
        1 => 
        stdClass::__set_state(array(
           'id' => '17',
           'mainmenu_id' => '4',
           'submenu_order' => '5',
           'submenu_name' => 'Feature Stories',
           'submenu_link' => '',
           'submenu_icon' => '',
        )),
        2 => 
        stdClass::__set_state(array(
           'id' => '14',
           'mainmenu_id' => '4',
           'submenu_order' => '2',
           'submenu_name' => 'Road Test',
           'submenu_link' => '',
           'submenu_icon' => '',
        )),
        3 => 
        stdClass::__set_state(array(
           'id' => '13',
           'mainmenu_id' => '4',
           'submenu_order' => '1',
           'submenu_name' => 'User Reviews',
           'submenu_link' => '',
           'submenu_icon' => '',
        )),
        4 => 
        stdClass::__set_state(array(
           'id' => '16',
           'mainmenu_id' => '4',
           'submenu_order' => '4',
           'submenu_name' => 'Write Review',
           'submenu_link' => '',
           'submenu_icon' => '',
        )),
      ),
    )),
  ),
 ),
));
?>

Answer 1

I have totally changed my answer!

Based on the answer to this question: How to group array data returned by left join query in php?

I think you do two queries. Like this:

$data = array();

// get all the menu items 
$rows = $db->query("SELECT * from server_menuresponse order by id");
while ($row = mysqli_fetch_assoc($rows)) {
    $row['sub_menu'] = []; // blank array for sub menu
    $data[$row['id']] = $row;
}

// get sub menu items, add them to the correct menu item
$rows = $db->query("SELECT * from sub_menu order by mainmenu_id, id");
while ($row = mysqli_fetch_assoc($rows)) {
    $data[$row['mainmenu_id']]['sub_menu'][] = $row;
}

print_r($data);