MySQL JOIN to JSON array

Question

I'm building a HTTP API and this part should return a JSON list of posts and their attached photos, like this:

[
    {
        "id": "788",
        "content": "Foo bar! Foo foo, bar bar.",
        "photos": [181, 282, 182]
    },
    ...
]

And this is my PHP code handling the request:

// get all the posts and their photos
$st = $db->prepare ('SELECT * FROM `posts` ' .
                    'LEFT JOIN `photos` ON `photo_post` = `post_id`');
$st->execute ();

// init our results array
$posts = array ();

// get the first post (plus it's JOINed photo)
$row = $st->fetch (PDO::FETCH_OBJ);
// if there's more left
while (false !== $row) {
    // init our post array
    $post = array (
        'id' => $row->id,
        'content' => $row->content,
        'photos' => array ()    // to fill with the photos
    );
    do {
        // don't add a `null` if there's no photos
        if (null !== $row->screenshot)
            $post ['photos'] [] = $row->photo_id;
    // get the row, repeat if it's another photo
    } while (false !== ($row = $st->fetch (PDO::FETCH_OBJ)) &&
             $row->id === $post ['id']);

    // add this post to the list
    $posts [] = $post;
}

// and send it back
echo json_encode ($posts);

Pretty simple JOIN parsing job, right? But the code is anything but nice or simple!

How can I parse MySQL's JOIN results elegantly into the JSON heirarchy that I need?

Answer 1

This is not vastly different but you might think its a little easier to read and follow and probably therefore debug.

With this code everything can be done within a single loop rather than the more complex multiple loop while/do that you originally used.

This also makes use of the stdClass provided by PHP to generate an array of objects as per your requirement, where your code was creating an array of arrays.

// get all the posts and their photos
$st = $db->prepare ('SELECT * FROM `posts` ' .
                    'LEFT JOIN `photos` ON `photo_post` = `post_id`');
$st->execute ();

// init our results array
$posts = array ();

$lastPostId = NULL;

// get the first post (plus it's JOINed photo)
while ( $post = $st->fetch (PDO::FETCH_OBJ) ) {

    if ( $lastPostId != $post->id ) {
        if ( $lastPostId !== NULL ) {
            // add this obj to the post array
            $posts[] = $tPost;
        }
        // start a new temp object
        $tPost          = stdClass();
        $tPost->id      = $post->id;
        $tPost->content = $post->content;
        $tPost->photos  = array();
    }

    // Add photo_id to current tPost obj
    $tPost->photos[] = $post->photo_id;

    $lastPostId = $post->id;
}

// and send it back
echo json_encode($posts);

Answer 2

You can make use of MySQL's GROUP_CONCAT function which will return a comma separated list of photoIds. e.g. 1,2,3,4,5

If there are no photos, an empty string is returned. Explode returns an empty array if passed an empty string.

// get all the posts and their photos

$st = $db->prepare ('
    SELECT `posts`.post_id as id, `posts`.content, GROUP_CONCAT(`photos`.id) AS photoIds
    FROM `posts` 
    LEFT JOIN `photos` ON `photo_post` = `post_id`
    GROUP BY `posts`.post_id
');

$st->execute ();

// init our results array
$posts = [];

$rows = $st->fetchAll (\PDO::FETCH_ASSOC);

foreach ($rows as $row) {
    $row['photos'] = explode(',', $row['photoIds']);
    $posts[] = $row;
}

// and send it back
echo json_encode ($posts);