Retrieving Photos and Comments from Database to JSON

[eluser]TheFuzzy0ne[/eluser]
This was the simplest way I could come up with. It's untested, but if you cross your fingers and think happy thoughts, it just might work without any problems. It's probably not the most efficient way of doing it, but I'm sure you'll agree, it's pretty easy to read:

Code:

public function readAlbumPhotosAndComments($album_id) {
    $this->db->from('photo');
    $this->db->join('comments', 'photo.photo_id = comments.photo', 'left');
    $this->db->where('photo_album', $album_id);
    $this->db->where('photo_private', 0);
    
    $results = $this->db->get()->result_array();

    $ret  = array();
    
    foreach ($results as $res)
    {
        $cur_photo_id = $res['photo_id'];
        
        // Add this photo to the return array if it doesn't already exist, using
        // photo_id as the index.
        if ( ! isset($ret[$cur_photo_id]))
        {
            $ret[$cur_photo_id] = array(
                'photo_id' => $cur_photo_id,
                'photo_resized' => 'photo.jpg',
                'photo_thumbnail' => $res['photo_thumbnail'],
                'photo_album' =>  $res['photo_album'],
                'comments' => array(),
            );
        }
        
        // Add the comment if we have one.
        if ($res['comment'])
        {
            $ret[$cur_photo_id]['comments'][] = array(
                'id' => $res['id'],
                'name' => $res['name'],
                'comment' => $res['comment'],
                'comment_photo' => $res['comment_photo'],
            );
        }
        
    }

    // Re-index the array so that our photos are encoded within a JSON
    // array instead of a JSON object.
    return array_values($ret);
}

I think it speaks for itself, but if anything is unclear about what the code does, let me know.

Don't forget to json_encode the output. I've assumed that you're going to do that in your controller.

Hope this helps.