{Solved} Combine 2 results into one.

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





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);

}

} 


There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!

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();
} 


A good decision is based on knowledge and not on numbers. - Plato

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.

There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!