Welcome Guest, Not a member yet? Register   Sign In
Retrieving Photos and Comments from Database to JSON
#1

[eluser]RMinor[/eluser]
I am trying to grab all photos and their respective comments from my database to display as a JSON object to a light box. My database structure is as follows:

album (album_id, album_title, album_description, album_profile)
photo (photo_id, photo_resized, photo_thumbnail, photo_album)
comments (id, name, comment, timestamp, ip_address, photo)

Controller:
Code:
public function get_album_json($album_id) {
    $data['response'] = FALSE;
    $this->load->model('album_model');
    if ($data['photos'] = $this->album_model->readAlbumPhotosAndComments($album_id)) {
        $data['response'] = TRUE;
    }
    $this->output->set_content_type('application/json')->set_output(json_encode($data));
}

Model:
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);
    $query = $this->db->get();
    //return ($query->num_rows() > 0) ? $query->result_array() : FALSE;
    $old_array = $query->result_array();
    //echo '<pre>'; print_r($old_array); echo '</pre>';
    $new_array = array();
    foreach ($old_array as $row) {
        $photo_id = $row['photo_id'];
        // searching $photo_id in new_array
        $found = FALSE;
            foreach ($new_array as $key => $val) {
                if ($val['photo'] == $photo_id) {
                    $found = $key;
                    break;
                }
            }
            //echo '<pre>'; print_r($new_array); echo '</pre>';
            if ($found === FALSE) {
                // change first comment to array of comments
                $row['comment'] = array(array($row['comment'], $row['timestamp'], $row['ip_address'] ));
                unset($row['timestamp']);
                unset($row['ip_address']);
                // add new photo to new array
                $new_array[] = $row;
            } else {
                // add next comment to existing photo
                $new_array[$found]['comment'][] = array($row['name'], $row['comment'], $row['timestamp'], $row['ip_address'] );
            }
        }
        echo '<pre>'; print_r($new_array); echo '</pre>';
        return $new_array;
    }
}

I want to try to return in JSON an array the has all of the photos and then for each photo a sub-array of it's comments. So far, my code is not giving me what I want. I think I need to do something like grabbing all of the photos and then for each photo grabbing its comments and then somehow merge the two arrays. I think I will need to do some re-writing on my model, but I am not sure exactly how to do this or what is the best way. Any help is appreciated.
#2

[eluser]TheFuzzy0ne[/eluser]
Are you using Lightbox or Lightbox 2?

As far as I'm aware, neither version of Lightbox supports comments, or even JSON. They both support a single caption, and generate their slideshow from your markup.

Are we talking about two different things here? Are you using some kind of plugin? Before helping you to write any code, I just want to be sure that this is going to work, because at the moment, I'm not convinced.
#3

[eluser]RMinor[/eluser]
I will be using a custom light box that another developer is working on. He asked me to return a JSON array with photos and comments so his API can work with them. My goal is to make a photo gallery with the same functionality as Facebook's current light box. I did see iLightBox that looks similar, but I wanted to get this custom one working. Thanks!
#4

[eluser]TheFuzzy0ne[/eluser]
In that case, please can you give us an example of the JSON array you would expect to be output? Then it should be a lot simpler to make this puppy work.
#5

[eluser]RMinor[/eluser]
Sure, I probably should've included that in the original post. Something like this would be great.

{
"photo_id": 1,
"photo_resized": "photo.jpg",
"photo_thumbnail": "photo_thumb.jpg",
"photo_album": 1,
"comments": {
"id": "1", "name": "Commenter", "comment": "This is a comment.", "comment_photo": "1"},
"id": "2", "name": "Commenter 2", "comment": "This is a comment again.", "comment_photo": "1"},
"id": "3", "name": "Commenter 3", "comment": "This is a comment yet again.", "comment_photo": "1"},
"id": "4", "name": "Commenter 4", "comment": "This is a comment one last time.", "comment_photo": "1"},
}
}
#6

[eluser]TheFuzzy0ne[/eluser]
So, just to be sure -- I assume that you will be passing back an array of those JSON objects, rather than just the one?
#7

[eluser]RMinor[/eluser]
Yes, you are correct.
#8

[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.
#9

[eluser]TheFuzzy0ne[/eluser]
Just corrected a typo I noticed that would break the code. D'oh!
#10

[eluser]RMinor[/eluser]
Works perfectly. I just had to change one of the column names, but it works great. Thank you.




Theme © iAndrew 2016 - Forum software by © MyBB