php mysql select，数组结果里面的数组结果

I have 3 tables,

我有3张桌子,

 tbl_photo               tbl_photo_comments      tbl_photo_likers
 ___________             ____________             _____________
| photo_id  |           | comment_id |           | like_id     |
| photo_url |           | photo_id FK|           | user_id FK  |
| user_id FK|           | user_id FK |           | photo_id FK |
                        | comment    | 

My objective is to get the photos data from tbl_photo together with their respective comments data and likers data. The structure of the array I want is as below where in I have ONE result array that has 2 more arrays as elements on its data

我的目标是从tbl_photo获取照片数据以及他们各自的评论数据和喜欢的数据。我想要的数组的结构如下所示,我有一个结果数组,其中有2个数组作为其数据的元素

oneResultArray =
{
  photo_url = "www.url.com/photo.png";
  photoID = 1;
  user_id =  2
  commentData = (
     {
         comment = "comment 1";
         userid = 1
     },
     {
         comment = "comment 2";
         userid = 2
     },
     {
         comment = "comment 3";
         userid = 3});

     likersData = (
     {
         userid = 2;
         username = liker1;
     },
     {
         userid = 3;
         username = liker2;
     });

   },
{
  photo_url = "www.url.com/photo.png";
  photoID = 1;
  user_id =  2
  commentData = (
     {
         comment = "comment 1";
         userid = 1
     },
     {
         comment = "comment 2";
         userid = 2
     },
     {
         comment = "comment 3";
         userid = 3});

     likersData = (
     {
         userid = 2;
         username = liker1;
     },
     {
         userid = 3;
         username = liker2;
     });
   }

my question is, Is it possible to accomplish this using one query on mysql? if not, is there any other way of doing this? thank you guys!

我的问题是,是否有可能使用mysql上的一个查询来实现这一点?如果没有,还有其他方法吗?感谢你们!

3 个解决方案

#1

As davidethell pointed out you don't want to join those tables. Thus it is not possible to select your data in a single query. Garry Welding's approach may be interpreted to run subsequent queries for each photo record you've got. This is NOT what you want to do either. 3 photos would lead to 7 queries being executed. That's 4 more than necessary. 10 photos would lead to 21 queries. You're getting the picture. Try something along the lines of:

正如davidethell指出的那样,你不想加入这些表格。因此,无法在单个查询中选择您的数据。 Garry Welding的方法可能会被解释为对您获得的每张照片记录进行后续查询。这不是你想要做的。 3张照片将导致7个查询被执行。这超过了必要的4。 10张照片将导致21次查询。你正在拍照。尝试以下方面的内容:

<?php

// build hierarchical result
$result = array();

// getting the photos
$query = $pdo->query('SELECT photo_id, photo_url, user_id FROM tbl_photo WHERE user_id = 5');
foreach ($query as $row) {
    $result[$row['photo_id']] = $row;
    $result[$row['photo_id']]['comments'] = array();
    $result[$row['photo_id']]['likes'] = array();
}

if ($result) {
    // comments and likes only for the photos we've selected
    $photos = join(',', array_keys($result));

    // getting the comments
    $query = $pdo->query('SELECT comment_id, photo_id, user_id, comment FROM tbl_photo_comments WHERE photo_id IN (' . $photos . ')');
    foreach ($query as $row) {
        $result[$row['photo_id']]['comments'][$row['comment_id']] = $row;
    }

    // getting the likes
    $query = $pdo->query('SELECT like_id, user_id, photo_id FROM tbl_photo_likers WHERE photo_id IN (' . $photos . ')');
    foreach ($query as $row) {
        $result[$row['photo_id']]['likes'][$row['like_id']] = $row;
    }
}

var_dump($result);

#2

You can't do this in one query without lots of duplicate data in each row. It will be more efficient and easier to wade through the results to do this in three queries. You will query the photos table, loop through it and in each iteration of the loop do two queries, one for comments and one for likes.

如果每行中没有大量重复数据,则无法在一个查询中执行此操作。在三个查询中执行此操作将更有效,更容易。您将查询照片表,循环浏览它,并在循环的每次迭代中执行两个查询,一个用于评论,一个用于喜欢。

#3

$photos = {code to get the array of photos};

foreach($photos as &$photo) {
    $photo['comments'] = {code to get photo comments using the photo id};
    $photo['likes'] = {code to get photo likes using the photo id}
}

return $photos;

This would be the only way I know of to do this. The & before $photo is to pass the array by reference. Passing arrays by reference is only available in PHP5+, http://php.net/manual/en/language.types.array.php.

这是我所知道的唯一方法。和$ photo之前是通过引用传递数组。通过引用传递数组仅适用于PHP5 +,http://php.net/manual/en/language.types.array.php。

From the PHP.net link above:

从上面的PHP.net链接:

// PHP 5
foreach ($colors as &$color) {
    $color = strtoupper($color);
}
unset($color); /* ensure that following writes to
$color will not modify the last array element */

// Workaround for older versions
foreach ($colors as $key => $color) {
    $colors[$key] = strtoupper($color);
}

print_r($colors);