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.


Messages In This Thread
Retrieving Photos and Comments from Database to JSON - by El Forum - 05-23-2013, 08:22 AM
Retrieving Photos and Comments from Database to JSON - by El Forum - 05-23-2013, 09:08 AM
Retrieving Photos and Comments from Database to JSON - by El Forum - 05-23-2013, 09:11 AM
Retrieving Photos and Comments from Database to JSON - by El Forum - 05-23-2013, 09:20 AM
Retrieving Photos and Comments from Database to JSON - by El Forum - 05-23-2013, 09:29 AM
Retrieving Photos and Comments from Database to JSON - by El Forum - 05-23-2013, 10:27 AM
Retrieving Photos and Comments from Database to JSON - by El Forum - 05-23-2013, 11:08 AM
Retrieving Photos and Comments from Database to JSON - by El Forum - 05-23-2013, 11:31 AM
Retrieving Photos and Comments from Database to JSON - by El Forum - 05-23-2013, 11:40 AM
Retrieving Photos and Comments from Database to JSON - by El Forum - 05-23-2013, 05:09 PM



Theme © iAndrew 2016 - Forum software by © MyBB