PHP mySQL one-to-many get result as an array

Question

I have 3 tables, one of which is used to store general properties of items. Each item can get one or more images and one or more addresses, which I store in separate tables.

tbl_items

id    title
1     item1
2     item2

tbl_item_imgs

id  title  filename  item_id
1   img1   kfm.gif   1
2   img2   edff.png  1
3   img3   knkk.jpg  2
4   img4   lkj.png   1

tbl_item_addresses

id  address     attitude   longitude  item_id
1   texas       55         54         1
2   NY          34         34         1
3   texas       55         53.5       1
4   LA          84         85         2

Now I want to get an array or object as a result like this:

array(2){

   [0]=> array(4){
          [id]=>"1"
          [title]=>"item1"
          [imgs] => array(3){
               [0]=> array(4){
                   [id] => "1"
                   [title] => "img1"
                   [filename] => "kfm.gif"
                   [item_id] => "1" 
               }
               [1]=> array(4){
                   [id] => "2"
                   [title] => "img2"
                   [filename] => "edff.png"
                   [item_id] => "1" 
               }
               [2]=> array(4){
                   [id] => "4"
                   [title] => "img"
                   [filename] => "lkj.png"
                   [item_id] => "1" 
               }
          }
          [addresses] = array(3){
              [0]=> array(4){
                   [id] => "1"
                   [address] => "texas"
                   [attitude] => "55"
                   [longitude] => "54" 
               }
               [1]=> array(4){
                   [id] => "2"
                   [address] => "NY"
                   [attitude] => "34"
                   [longitude] => "34" 
               }
               [2]=> array(4){
                   [id] => "3"
                   [address] => "texas"
                   [attitude] => "55"
                   [longitude] => "53.5" 
               }
          }        
   }

   [1]=> array(4){
      array(4){
          [id]=>"1"
          [title]=>"item1"
          [imgs] => array(1){
              [0]=>array(4){
                   [id] => "3"
                   [title] => "img3"
                   [filename] => "knkk.jpg"
                   [item_id] => "2" 
              }    
          }
          [addresses] = array(1){
              [0]=>array(4){
                   [id] => "4"
                   [address] => "LA"
                   [attitude] => "84"
                   [longitude] => "85"
              }
          }
       }
   }

}

So, my question is this: Can I do this via one sql request? I am aware that mysql does not return array so I have to process the mysql result with php to get this. Please help me to find out the best way to do this.

One way might be to get the items with one query and foreach through them, then get the addresses and imgs of each row with separate sql requests and push them into item array. I don't think this is a good idea because that requires too many queries and would be slow.

Answer 1

    <?php
        $current_title = '';
        $data_array = array();
        $query = "SELECT tbl_items.*, imgs.id AS image_id,imgs.title AS image_title,imgs.filename AS image_filename,imgs.item_id AS image_itemid , addresses.item_id AS address_itemid, addresses.id AS address_id
        , addresses.attitude AS address_attitude, addresses.longitude AS address_longitude
            FROM tbl_items
            LEFT JOIN tbl_items_addresses AS addresses ON tbl_items.id = addresses.item_id
            LEFT JOIN tbl_item_imgs AS imgs ON tbl_items.id = imgs.item_id";
        $data_results = mysql_query($query);
        foreach ($data_results as $data){
            if ($data['title'] != $current_title){
                $current_title = $data['title'];
                $data_array[$current_title]['id'] = $data['id'];
                $data_array[$current_title]['title'] = $data['title'];
                $data_array[$current_title]['addresses'] = array();
                $data_array[$current_title]['imgs'] = array();
            }
                $data_array[$current_title]['addresses'][] = ('address_id'=>$data['address_id'],'address_itemid'=>$data['address_itemid'],'address_attitude'=>$data['address_attitude'],'address_longitude'=>$data['address_longitude']);
                $data_array[$current_title]['imgs'][] = ('image_id'=>$data['image_id'],'image_title'=>$data['image_title'],'image_filename'=>$data['image_filename'],'image_itemid'=>$data['image_itemid']);
        }
    ?>