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>  Resellers <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;
}
}
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>