phpcodeigniteractiverecordcounting

How to count results per group in CodeIgniter?


I know about codeigniter's $this->db->count_all('table_name') and also that I can use a where condition and fetch filtered result. But in my case, I don't know how to implement this in my code:

I want to count all resellers with key="some-value".

This is my view to show all resellers:

<?php $this->load->view('admin/components/page_head') ?>
    <section class="container-fluid">
        <ul class="nav nav-pills">  <li class="active"> <h2><i class="glyphicon glyphicon-user"></i>&nbsp Resellers&nbsp<span class="badge"><?php echo $this->db->count_all('reseller');?></span></h2></li></ul>

        <h3><?php echo anchor('admin/reseller/add_reseller', '<i class="glyphicon glyphicon-plus"></i> Add a Reseller'); ?></h3>
            
        <table class="table table-primary">
            <thead>
                <tr>
                    <th>SIP Userid</th>
                    <th>SIP Password</th>
                    <th>Unique ID</th>
                    <th>Allocation Block</th>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Phone</th>
                    <th>Intial User Required</th>
                    <th>Location</th>
                    <th>Balance</th>
                    <th>Country</th>
                    <th>Country Code</th>   
                    <th>Status</th> 
                    <th>Edit</th>
                    <th>Delete</th>
                </tr>
            </thead>
            <tbody>
                <?php if(count($resellers)): foreach($resellers as $reseller): ?>   
                    <tr>
                        <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'sip_username','<?php echo $reseller->id; ?>')" ><?php echo $reseller->sip_username; ?></td>
                        <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'sip_password','<?php echo $reseller->id; ?>')" ><?php echo $reseller->sip_password; ?></td>
                        <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'key','<?php echo $reseller->id; ?>')" ><?php echo $reseller->key; ?></td>
                        <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'allocation_block','<?php echo $reseller->id; ?>')" ><?php echo $reseller->allocation_block; ?></td>
                        <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'name','<?php echo $reseller->id; ?>')" ><?php echo $reseller->name; ?></td>
                        <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'email','<?php echo $reseller->id; ?>')" ><?php echo $reseller->email; ?></td>
                        <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'phone','<?php echo $reseller->id; ?>')" ><?php echo $reseller->phone; ?></td>
                        <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'user_num','<?php echo $reseller->id; ?>')" ><?php echo $reseller->user_num; ?></td>
                        <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'address','<?php echo $reseller->id; ?>')" > <?php echo $reseller->address; ?></td>
                        <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'balance','<?php echo $reseller->id; ?>')" ><?php echo $reseller->balance; ?></td> 
                        <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'country','<?php echo $reseller->id; ?>')" ><?php echo $reseller->country; ?></td> 
                        <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'country_code','<?php echo $reseller->id; ?>')" ><?php echo $reseller->country_code; ?></td>   
                        <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'status','<?php echo $reseller->id; ?>')" ><?php echo $reseller->status; ?></td>   
                
                        <td><?php echo btn_edit('admin/reseller/edit/' . $reseller->id); ?></td>
                        <td><?php echo btn_delete('admin/reseller/delete/' . $reseller->id); ?></td>
                    </tr>
                <?php endforeach; ?>
            <?php else: ?>
                <tr>
                    <td colspan="3">We could not find any users.</td>
                </tr>
            <?php endif; ?> 
        </tbody>
    </table>
</section>
<?php $this->load->view('admin/components/page_tail') ?>

Now I have another table called users and in that I have a field called key in which the key of reseller is stored so I want to count all users having key="some-key".

This is needed as in the reseller's list I want to show which reseller has how many users.

I did this:

$key = "Rajan-92-1-100-1";
$this->load->model('reseller_m');
$this->load->view('admin/reseller/count');
$this->db->where('key', $key);
$this->db->from('users');
$count = $this->db->count_all_results();
echo $count;

but here I am providing the key statically, I want to count this dynamically for each reseller.

Update: Controller

public function index ()
{
    $usertype = $this->session->userdata('usertype');
    if ($usertype ==="admin") {
        // Fetch all users
        $this->data['resellers'] = $this->reseller_m->get();
        $data['resellers'] = array();
        if ($results) {
            foreach($results as $result) {
                $data['resellers'][] = array(
                    'sip_username' => $result['sip_username'],
                    'sip_password' => $result['sip_password'],
                    'key' =>  $result['key'],
                    'total' => $this->reseller_m->count($result['key']);
                    // Add your other data here.
                );
            }
        }

        // Load view
        $this->data['subview'] = 'admin/reseller/index';
        $this->load->view('admin/_layout_main', $this->data);
    } else {
        $this->load->view('permission');
    }   
}

The View:

<td><?php echo $reseller['total'];?></td>

The Model:

public function count($key) 
{
    $this->db->where('key', $key);
    $query = $this->db->get($this->db->dbprefix . 'users');
    return $query->num_rows();
}

public function get() 
{
    $query = $this->db->get($this->db->dbprefix . 'users');
    if ($query->num_rows() > 0) {
        return $query->results_array();
    } else {
        return FALSE;
    }
}

Solution

  • I will try and give it a go.

    I use return $query->num_rows(); when I need to count with where.

    Model Function

    class Reseller_m extends CI_Model {
    
      public function count($key) {
        $this->db->where('key', $key);
        $query = $this->db->get($this->db->dbprefix . 'users');
    
        return $query->num_rows();
      }
    
     public function get() {
         // Not sure if you have any thing else here.
        $query = $this->db->get($this->db->dbprefix . 'users');
    
        if ($query->num_rows() > 0) {
              return $query->results_array();
              // return $query->row_array();
        } else {
              return FALSE;
        }
      }
    
    }
    

    Controller

    When you need to count dynamically in table you can call model function like below. In the array, I find having data array like below that for tables is much easier.

    public function index() {
    
        $usertype = $this->session->userdata('usertype');
    
        if ($usertype === "admin") {
    
        $this->load->model('reseller_m');
    
        $results = $this->reseller_m->get();
    
        // Removed $this->data and just have $data see what works. 
    
        $data['resellers'] = array();
    
        if ($results) {
          foreach($results as $result) {
           $data['resellers'][] = array(
              'id' => $result['id'],
              'sip_username' => $result['sip_username'],
              'sip_password' => $result['sip_password'],
              'key' =>  $result['key'],
              'total' => $this->reseller_m->count($result['key']),
              'allocation_block' => $result['allocation_block'],
              'name' => $result['name'],
              'email' => $result['email'],
              'phone' => $result['phone'],
              'user_num' => $result['user_num'],
              'address' => $result['address'],
              'balance' => $result['balance'],
              'country_code' => $result['country_code'],
              'status' => ($result['status'] ? Enabled : Disabled)
           );
          }
        }
    
          $data['subview'] = 'admin/reseller/index';
          $this->load->view('admin/_layout_main', $data);
    
        } else {
    
           $this->load->view('permission');
    
        }
    }
    

    View example

    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'total','<?php echo $reseller['id'];?>')" ><?php echo $reseller['total'];?></td>
    

    Table Example

    <tbody>
    
    <?php if ($resellers) {?>
    
    <?php foreach ($resellers as $reseller) {?>
    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'sip_username','<?php echo $reseller['id'];?>')" ><?php echo $reseller['sip_username'];?></td>
    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'sip_password','<?php echo $reseller['id'];?>')" ><?php echo $reseller['sip_password'];?></td>
    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'key','<?php echo $reseller['id'];?>')" ><?php echo $reseller['key'];?></td>
    
    //  Added here Total Here
    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'total','<?php echo $reseller['id'];?>')" ><?php echo $reseller['total'];?></td>
    
    
    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'allocation_block','<?php echo $reseller['id'];?>')" ><?php echo $reseller['allocation_block'];?></td>
    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'name','<?php echo $reseller['id'];?>')" ><?php echo $reseller['name'];?></td>
    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'email','<?php echo $reseller['id'];?>')" ><?php echo $reseller['email'];?></td>
    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'phone','<?php echo $reseller['id'];?>')" ><?php echo $reseller['phone'];?></td>
    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'user_num','<?php echo $reseller['id'];?>')" ><?php echo $reseller['user_num'];?></td>
    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'address','<?php echo $reseller['id'];?>')" ><?php echo $reseller['address'];?></td>
    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'balance','<?php echo $reseller['id'];?>')" ><?php echo $reseller['balance'];?></td>
    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'country_code','<?php echo $reseller['id'];?>')" ><?php echo $reseller['country_code'];?></td>
    <td  contenteditable="true" onClick="edit_Reseller(this)" onBlur="save_reseller(this,'status','<?php echo $reseller['id'];?>')" ><?php echo $reseller['status'];?></td>
    
    <?php } else { ?>
    
    <p>No Results</p>
    
    <?php }?>
    
    </tbody>