• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
{Solved} Combine 2 results into one.

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

}


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

#2
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

Reply

#3
(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.
There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.