Merging data from two queries output

Question

I'm tying to create a share like feature on my site. I have these tables

USERS

1. user_id
2. fullname
3. username etc.

POSTS

1. post_id
2. user_id
3. post
4. orig_post_id
5. date

USER_FOLLOWERS

1. follow_id
2. user_id
3. follower_id
4. date

I have this query to select posts from the users the current user is following.

//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

   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

$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

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

Answer 1

That's just making things more complicated than it should be. Try this

 foreach ($query->result() as $row) {
                $branch_id = $row->is_branch_of_id;
                $user_branch_id = $this->postid_return_user_id($branch_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,
                    'file_path_thumb' => $row->file_path_thumb,

---

                    'data' => $this->branch_query($user_branch_id, $branch_id)

---

                );
                    $post_obj = $this->array_to_object($post_array);

            }

Since post_array is multidimensional, you will need this function to convert it into an object.

 public function array_to_object($array) {
        $obj = new stdClass;
        foreach($array as $k => $v) {
            if(is_array($v)) {
                $obj->{$k} = $this->array_to_object($v);
            } else {
                $obj->{$k} = $v;
            }
        }
        return $obj;
    }