Combining 2 sql queries in codeigniter [duplicate]

Question

I have two tables. voting_ip and country. I want to retrieve results from country table where open_id_fk (foreign key) of voting_ip table is equal to open_id(Primary Key) of country table. How to write sql query to combine these queries and return the result. I am using the below code in my codeigniter model to retrieve number of occurances of open_id_fk in voting_ip table.

public function mostvotedans()
{
    $this->db->select('open_id_fk, COUNT(*) as total');
    $this->db->group_by('open_id_fk'); 
    $this->db->order_by('total', 'desc'); 
    $query = $this->db->get('voting_ip', 5);
    return $query;

    $query = $this->db->query("SELECT * FROM country WHERE open_id_fk=open_id;");  
    return $query;         
}

use joins link

Gopal Sharma
– Gopal Sharma

2015-08-04 08:27:18 +00:00
Commented Aug 4, 2015 at 8:27 — Gopal Sharma
– Gopal Sharma, Commented Aug 4, 2015 at 8:27

shankar kumar · Accepted Answer · 2015-08-04 07:42:06Z

1

change it as following.

  public function mostvotedans()
{
  $this->db->select('c.open_id,COUNT(ip.open_id_fk) as total')->from('voting_ip ip');
  $this->db->join('country c','c.open_id=ip.open_id_fk');
  $this->db->group_by('ip.open_id_fk'); 
  $this->db->order_by('total', 'desc'); 
  $this->db->limit(5);
 $query = $this->db->get();
 return $query;


}

answered Aug 4, 2015 at 7:42

shankar kumar

6485 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shank Over a year ago

thank you.. this worked :) awesome.. i just modified it to get all the columns as.. $this->db->select('c.*,COUNT(ip.open_id_fk) as total')->from('voting_ip ip');

Vincent Decaux · Accepted Answer · 2015-08-04 07:36:07Z

1

Use a join statement :

$query = $this->db->select('v.open_id_fk, COUNT(v.*) AS total, c.country_name')
            ->join('country AS c', 'c.open_id = v.open_id_fk')
            ->from('voting_ip AS v')
            ->group_by('v.open_id_fk')
            ->order_by('total', 'DESC')
            ->limit(5);

Should work, I put 'country_name' because I don't know your tables.

answered Aug 4, 2015 at 7:36

Vincent Decaux

10.9k7 gold badges67 silver badges103 bronze badges

Collectives™ on Stack Overflow

Combining 2 sql queries in codeigniter [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related