MySQL JOIN to JSON array 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-&gt;prepare ('SELECT * FROM `posts` ' .
                    'LEFT JOIN `photos` ON `photo_post` = `post_id`');
$st-&gt;execute ();

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

// get the first post (plus it's JOINed photo)
$row = $st-&gt;fetch (PDO::FETCH_OBJ);
// if there's more left
while (false !== $row) {
    // init our post array
    $post = array (
        'id' =&gt; $row-&gt;id,
        'content' =&gt; $row-&gt;content,
        'photos' =&gt; array ()    // to fill with the photos
    );
    do {
        // don't add a `null` if there's no photos
        if (null !== $row-&gt;screenshot)
            $post ['photos'] [] = $row-&gt;photo_id;
    // get the row, repeat if it's another photo
    } while (false !== ($row = $st-&gt;fetch (PDO::FETCH_OBJ)) &amp;&amp;
             $row-&gt;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?
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-&gt;prepare ('SELECT * FROM `posts` ' .
                    'LEFT JOIN `photos` ON `photo_post` = `post_id`');
$st-&gt;execute ();

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

$lastPostId = NULL;

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

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

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

    $lastPostId = $post-&gt;id;
}

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