CODEWITHSUNDEEP
phpmysqlcodeigniter
Sarotobi
Sarotobi

Reputation: 831

Codeigniter query count() total comments joining three tables

I am attempting to create a query to get a single blog post from my blog table and at the same time get the information from that user on my users' table which was ok using Join but now I want to count the total comments of that blog post as total so that would be three tables to query blog, users and comments

But below code display 3 blog entry with the same content, and where to place the COUNT(*) as total for comment table, any suggestion would be great!

public function get_entry(){

        $id = $this->input->post('ID', true);


        $this->db->select('*, u.ID');
        $this->db->where('u.ID', $id)
         ->from('gb_blod as u')
         ->join('gb_users as a', 'u.user_email = a.email', 'LEFT')
         ->join('gb_comments as b', 'u.ID = b.journal_id', 'LEFT');
         $result = $this->db->get();


            if($result->num_rows() > 0){

            return $result->result_array();


            }else{

                return false;
            }

    }

Upvotes: 0

Views: 702

Answers (3)

Kathak Dabhi
Kathak Dabhi

Reputation: 399

Count the comment id and group the query by blog id.

public function get_entry(){

        $id = $this->input->post('ID', true);


        $this->db->select('u.*, a.*, count(b.ID) as total');
        $this->db->where('u.ID', $id)
         ->from('gb_blod as u')
         ->join('gb_users as a', 'u.user_email = a.email', 'LEFT')
         ->join('gb_comments as b', 'u.ID = b.journal_id', 'LEFT')
         ->group_by('u.ID');
         $result = $this->db->get();


            if($result->num_rows() > 0){

            return $result->result_array();


            }else{

                return false;
            }

    }

Upvotes: 2

Rahul shukla
Rahul shukla

Reputation: 378

Try using this, you can change the query according to your requirement:-

  $usr_flds = "count(u.ID) as count_rows";
  $this->db->select('usr_flds');
  $this->db->where('u.ID', $id)
 ->from('gb_blod as u')
 ->join('gb_users as a', 'u.user_email = a.email', 'LEFT')
 ->join('gb_comments as b', 'u.ID = b.journal_id', 'LEFT');
   $result = $this->db->get();
   return $res->num_rows();

Upvotes: 2

PHP Geek
PHP Geek

Reputation: 4033

try this.i hope it will work for you.

$result = $this->db->get();
 $count = $result->get()->num_rows();
        return [$result, $count];

Upvotes: 0

Related Questions