How to use group by in codeigniter to count some value?

Question

How can i use group by for following case:

I have a table called Sitekey in here i have list of License keys.

The Site_Key field contains the license keys for my product. The Org_id contains the id of the organization to which that key has been assigned.

Now I have another table called activation. Its Structure is:

Now in this table Site_key contains the license(key). and the device information.

Now I want to show licenses available in table to whom they are assigned.

I do this :

public function get_key()
{

    $this->db->select('*');  
    $this->db->from('sitekey');
    $this->db->join('company', 'sitekey.org_id = company.id');
    $this->db->order_by('sitekey.id','desc');
    $query = $this->db->get();
    $result   = $query->result();              
    return $result;

}

Now I want get the count of keys that are used in activation table! > in sitekey table the licenses field contains the number of licenses so the company can use that key for that number of times. Now i want to get the key i gave them and the keys they have used from it. So i guess i need to use group by So how can i write such a query ?

Note

I want to count the keys in activation table. For eg there is a key :4CAC-9CA7 Then i want to know how many times it has been used ? So i want to count all this for all keys

The Company Table:

@AjayMakwana i saw that in user_guide. but i dont know how to use it here! what should be my query here — Rajan
– Rajan, Commented Feb 4, 2016 at 10:08

Aleksey Solomakha · Accepted Answer · 2016-02-04 10:40:08Z

1

public function get_key() 
{ 

$this->db->select('company.org_name, sitekey.site_key, (SELECT COUNT(*) FROM activation WHERE activation.site_key= sitekey.site_key) as key_count'); 
$this->db->from('sitekey'); 
$this->db->join('company', 'sitekey.org_id = company.id'); 
$query = $this->db->get(); 
$result = $query->result(); 

var_dump($result); 
return $result; 

}

edited Feb 4, 2016 at 10:40

answered Feb 4, 2016 at 10:11

Aleksey Solomakha

1131 gold badge1 silver badge9 bronze badges

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

8 Comments

Rajan Over a year ago

no man, i dont need to use expiry data, i just want to show how many keys i have given to organization and how many have they used from it

Aleksey Solomakha Over a year ago

Show structure "company"

Rajan Over a year ago

added the company table , but i want to get the count of each key used in activation table,

Aleksey Solomakha Over a year ago

So why you can't just GROUP BY site_key ?

Aleksey Solomakha Over a year ago

SELECT company.org_name, sitekey.site_key, (SELECT COUNT(*) FROM activation WHERE user_id = company.id) FROM sitekey JOIN company ON sitekey.org_id = company.id It must return COMAPNY NAME, 4CAC-9CA7, 2 times ANOTHER COMPANY, 4CAC-9CA7, 12 times

|

Ravi Prakash Awasthi · Accepted Answer · 2016-02-04 10:14:27Z

0

Try this may be this will help you

public function get_key() {
               $result=$this->db->select('*')
                      ->from('activation')
                      ->group_by('yourfield')
                      ->get();
             $key_count= $query->num_rows();   
             return $result;
}

answered Feb 4, 2016 at 10:14

Ravi Prakash Awasthi

1431 silver badge5 bronze badges

Comments

Gwendal · Accepted Answer · 2016-02-04 10:27:11Z

0

Sorry, I can't comment yet so I've to post an answer.

If you have a row per key in your activation table, you can just count everything

$this->db->from('activation')
->where('expiry_date <', date('U'))
->count_all_results();

will return an int

If you want to group by a column, it's easy, juste add a group_by

$this->db->from('activation')
->group_by('site_key')
->where('expiry_date <', date('U'))
->count_all_results();

EDIT:

If you want to know of many licenses their is left, you can try

$this->db->from('activation a')
->select('COUNT(a.id) as used, s.licenses as max, max - used as left')
->group_by('a.site_key')
->join('sitekey s', 'a.sitekey = s.site_key')
->get()->results();

edited Feb 4, 2016 at 10:27

answered Feb 4, 2016 at 10:15

Gwendal

1,2737 silver badges17 bronze badges

3 Comments

Rajan Over a year ago

i dont need expiry data here. i just want to count the licenses i gave them and the licenses they have used from it

Rajan Over a year ago

in sitekey table the licenses field defines how many times they can use this key

Gwendal Over a year ago

@Rajan I edited my answer, can you tell me if the last part works for you ?

Collectives™ on Stack Overflow

How to use group by in codeigniter to count some value?

3 Answers 3

8 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related