Modify the structure of data returned by $wpdb

by

You can do this one of two ways, as far as I can see:

  1. Use PHP to combine the current results to the one you want. Basically:

    $records = array();

    foreach( $results as $result )
{
    $record_id = $result['item_id'];
    if (empty($records[$record_id])) {
        $records[$record_id]['item_id'] = $result['item_id'];
        $records[$record_id]['item_name'] = $result['item_name'];
        $records[$record_id]['sub_items'] = array();
    }
    $records[$record_id]['sub_items'][] = array( 
       'sub_item_id' => $result['sub_item_id'],
       'sub_item_name' => $result['sub_item_name']  
    );
}

  2. Other way is doing 2 queries. first one finds the main items. One you have the item ids of the main items, you do a secondary query where you get all the sub-items whose item_id is in the ones you queries before. Then, you use PHP to put it together in nested arrays, similar to the first option.

As far as I can tell, you can’t really do nested results in mySQL like you want, so you’ll have to do the legwork in PHP.