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