[eluser]Unknown[/eluser]
Hello all, i'm a ci newb but i'm loving it so far. it makes things so much easier.
I'm tying to create a share like feature on my site. I have these tables
USERS
user_id
fullname
username etc.
POSTS
post_id
user_id
post
orig_post_id
date
USER_FOLLOWERS
follow_id
user_id
follower_id
date
I have this query in my model to select posts from the users the current user is following.
Code:
public function getPosts(){
//user_id from session data
$user_id = $this->session->userdata('user_id');
$sql = "SELECT p.*,u.fullname,u.username
FROM (
SELECT user_id
FROM user_followers
WHERE follower_id = $user_id
UNION ALL
SELECT $user_id
) uf
JOIN posts p
ON p.user_id = uf.user_id
JOIN users u
ON u.user_id = p.user_id
ORDER BY p.post_date DESC";
$query = $this->db->query($sql);
if ($query) {
foreach ($query->result() as $row) {
$branch_id = $row->orig_post_id;
$post_array[] = array(
'post_id' => $row->post_id,
'user_id' => $row->user_id,
'post' => $row->post,
'is_branch_of_id' => $branch_id,
'post_date' => $row->post_date,
'fullname' => $row->fullname,
'username' => $row->username
);
#i would explain what i'm tying to do here below
if ($branch_id != 0) {
$branch_array = array();
#this contains the orignal posts user id
$user_branch_id = $this->postid_return_user_id($branch_id);
$branch_data = $this->branch_query($user_branch_id, $branch_id);
$branch_array[] = array(
'branch_uname' => $branch_data->username,
'branch_fname' => $branch_data->fullname,
'orig_post' => $branch_data->post
);
$post_obj = (object)array_merge($branch_array, $post_array);
} else {
$post_obj = (object)$post_array;
}
}
return $post_obj;
}
Then the branch query
Code:
public function branch_query($orig_post_user_id, $orig_post_id)
{
$sql = "SELECT users.username,users.fullname,posts.post,posts.post_id
FROM users u
JOIN posts p
ON p.user_id = u.user_id
WHERE u.user_id = $orig_post_user_id
AND p.post_id = $orig_post_id";
$q = $this->db->query($sql);
return ($q)?$q->result():array();
}
1st, getting the data i need in the post_array.
if the field orig_post_id is not 0, that is the post was shared by a user from another post. i create another array called branch_array, branch array is meant to contain the original post's users username,fullname and the orignal post itself. This is where branch query comes in. With branch query i pass the orignal users user id and the original post id then it returns the original post users username,fullname and the post itself. I then get this as in the branch data variable and put it into the branch array.
Now i try to merge branch data to post_array and convert the merged array into an object. MY desired ouput would look something like these;
scenario 1, when orig_post_id is not 0
Code:
$post_obj = new stdClass([post_id] => 4,
[user_id] => 2,
[post] => ok ginny,
[orid_post_id] => 3,
[post_date] => some timestamp,
[fullname] => Harry Potter,
[username] => avadakedevra,
[branch_uname] => ginny,
[branch_fname] => Ginny Potter
[orig_post] => stop it harry
)
As you can see, the branched data has been merged. scenario 2, when orig_post_id = 0
Code:
$post_obj = new stdClass([post_id] => 3,
[user_id] => 1,
[post] => stop it harry,
[orid_post_id] => 0,
[post_date] => some timestamp,
[fullname] => Ginny Potter,
[username] => ginny
)
Right now it only get's only one branch data and places it outside the object. Any help would be deeply appreciated. Sorry for the length. As you can tell from the harry potter posts, i'm literally going cray lol Thanks again.
