Return to Answer

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
    $tObj->photos[] = $post->photo_id;

    $lastPostId = $post->id;
}

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

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

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
    $tObj->photos[] = $post->photo_id;

    $lastPostId = $post->id;
}

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

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
    $tObj->photos[] = $post->photo_id;

    $lastPostId = $post->id;
}

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

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

// 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
    $tObj->photos[] = $post->photo_id;

    $lastPostId = $post->id;
}

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

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
    $tObj->photos[] = $post->photo_id;

    $lastPostId = $post->id;
}

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