PHP mySQL一对多获取结果作为数组

Question

我有 3 个表，其中一个用于存储项目的一般属性。每个项目可以获得一个或多个图像和一个或多个地址，我将它们存储在单独的表中。

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

现在我想像这样得到一个数组或对象:

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"
              }
          }
       }
   }

}

所以，我的问题是:我可以通过一个 sql 请求来完成吗？我知道 mysql 不返回数组，所以我必须用 php 处理 mysql 结果才能得到这个。请帮我找出最好的方法。

一种方法可能是通过一个查询获取项目并通过它们进行 foreach，然后使用单独的 sql 请求获取每一行的地址和 imgs 并将它们推送到项目数组中。我认为这不是一个好主意，因为这需要太多查询而且速度很慢。

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']);
        }
    ?>