php mysql select, array result inside array result

Question

I have 3 tables,

 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

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!

rodneyrehm · Accepted Answer · 2012-02-08 11:20:37Z

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:

<?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);

davidethell · Accepted Answer · 2012-02-08 11:02:05Z

1

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.

answered Feb 8, 2012 at 11:02

davidethell

12.1k6 gold badges47 silver badges67 bronze badges

Comments

Garry Welding · Accepted Answer · 2012-02-08 11:19:23Z

-1

$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.

From the PHP.net link above:

// 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);

edited Feb 8, 2012 at 11:19

answered Feb 8, 2012 at 11:12

Garry Welding

3,6101 gold badge31 silver badges46 bronze badges

Collectives™ on Stack Overflow

php mysql select, array result inside array result

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related