CodeIgniter Forums
{Solved} Combine 2 results into one. - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: {Solved} Combine 2 results into one. (/showthread.php?tid=66259)

{Solved} Combine 2 results into one. - wolfgang1983 - 09-28-2016

I have these  two model functions.

The get_posts function effects the function total_posts_by_user


PHP Code:
public function get_posts() {
    $this->db->select('p.post_id, p.reply_id, p.user_id, u.username');
    $this->db->from('post as p');
    $this->db->join('user as u', 'u.user_id = p.user_id');
    $this->db->where('p.post_id', $this->input->get('post_id'));
    $this->db->or_where('p.reply_id', $this->input->get('post_id'));
    //$this->db->group_by('p.user_id');
    $query = $this->db->get();
    return $query->result_array();
} 



PHP Code:
public function total_posts_by_user($user_id, $reply_id, $post_id) {
    $this->db->from('post');
    $this->db->where('post_id', $post_id);
    $this->db->where('reply_id', $reply_id);
    $this->db->where('user_id', $user_id);
    $query = $this->db->get();

    if ($query->num_rows() > 0) {
        return $query->num_rows();
    }
} 

On my controller function I have used $this->db->last_query() for the total_posts_by_user($user_id, $reply_id, $post_id)

And out puts like code below but MY Question: On the get_posts In stead of making the 2 admin show how could I combine the 2 in to one using something like group_by and then changing the total_post to 2.

I have tried group by not to sure what missing. $this->db->group_by('p.reply_id'); and $this->db->group_by('p.user_id');


Code:
SELECT * FROM `post` WHERE `post_id` = '1' AND `reply_id` = '0' AND `user_id` = '2' // Main post
SELECT * FROM `post` WHERE `post_id` = '3' AND `reply_id` = '1' AND `user_id` = '1' // Reply Post
SELECT * FROM `post` WHERE `post_id` = '5' AND `reply_id` = '1' AND `user_id` = '1' // Reply Post


Image


[Image: 2wiJsd9LMd55.png]


Controller

PHP Code:
<?php

class Who_replied extends MX_Controller {

public function index() {
    $data['posts'] = array();

    $results = $this->get_posts();

    foreach ($results as $result) {
        $data['posts'][] = array(
            'username' => $result['username'],
            'total' => $this->total_posts_by_user($result['user_id'], $result['reply_id'], $result['post_id'])
        );

        $this->total_posts_by_user($result['user_id'], $result['reply_id'], $result['post_id']);
        echo $this->db->last_query() . '</br>';
    }

    $data['total_posts'] = $this->total_posts();

    return $this->load->view('default/template/forum/categories/who_replied_view', $data);

}

} 


RE: Combine 2 results into one. - salain - 09-29-2016

Hi,

You need to count the user_id with your group by
PHP Code:
public function get_posts() {
    $this->db->select('p.post_id, p.reply_id, p.user_id, u.username COUNT(p.user_id) AS total');
    $this->db->from('post as p');
    $this->db->join('user as u', 'u.user_id = p.user_id');
    $this->db->where('p.post_id', $this->input->get('post_id'));
    $this->db->or_where('p.reply_id', $this->input->get('post_id'));
    $this->db->group_by('p.user_id');
    $query = $this->db->get();
    return $query->result_array();
} 


RE: Combine 2 results into one. - wolfgang1983 - 09-29-2016

(09-29-2016, 12:35 AM)salain Wrote: Hi,

You need to count the user_id with your group by
PHP Code:
public function get_posts() {
    $this->db->select('p.post_id, p.reply_id, p.user_id, u.username COUNT(p.user_id) AS total');
    $this->db->from('post as p');
    $this->db->join('user as u', 'u.user_id = p.user_id');
    $this->db->where('p.post_id', $this->input->get('post_id'));
    $this->db->or_where('p.reply_id', $this->input->get('post_id'));
    $this->db->group_by('p.user_id');
    $query = $this->db->get();
    return $query->result_array();
} 

I think that did the trick but will test it some more.