CODEWITHSUNDEEP
phpcodeignitercodeigniter-3
TheOrdinaryGeek
TheOrdinaryGeek

Reputation: 2323

Codeigniter Select and Count MySQL Records

Using Codeigniter 3, I would like to display all the records from a table in a MySQL database. I'd also like to include the number of records selected.

For example;

Showing x number of records;

record 1
record 2
record 3
etc

Currently I have the following (which works);

// select all records
public function selectRecords() {
    $this->db->select('*');
    $this->db->from('records');
    $query = $this->db->get();
    return $query->result_array();
}

// count all records 
public function countRecords() {
    $this->db->select('count(*) as count');
    $this->db->from('records');
    $query = $this->db->get();
    return $query->row();
}

My question is do I need two separate queries in order to achieve this (select and count)?

Is there a more efficient way of achieving what I want?

Upvotes: 1

Views: 2173

Answers (5)

mickmackusa
mickmackusa

Reputation: 48071

Your model method only needs to return the 2d payload to the controller (it doesn't need to be tasked with also counting the rows).

public function getAll(): array
{
    return $this->db->get('records')->result_array();
}

In your controller, you can just call count() if you need it at that level. If the count is only needed by the view, then only call count() in the view.

If there are times when you only want the count of all rows and don't need the actual row data (in a single server request), then yes, it is sensible to dedicate a method to this singular responsibility.

public function countAll(): int
{
    return $this->db->count_all_results('records');
}

Upvotes: 0

Pradeep
Pradeep

Reputation: 9707

You can do something like this :

public function selectRecords() 
{
    $query = $this->db->get('records');
    if ($query->num_rows() > 0 )
    {
       $records = $query->result_array();
       $data['count'] = count($records);
       $data['all_records'] = $records;
       return $data;
    }  
}

Pass it to the view from your controller :

 $data = $this->model_name->selectRecords();
 /*print_r($data) to see the output*/
 $this->load->view('your_view',$data);

In view :

<?php echo $count .' number of records';?>

Upvotes: 1

Vijay Sharma
Vijay Sharma

Reputation: 831

try this it will help you to provide pagination for records

public function selectRecords($params = array(), $count = false) {

    $offset = isset($params['offset']) ? $params['offset'] : '';
    $limit = isset($params['limit']) ? $params['limit'] : '';
    $this->db->select('*');
    $this->db->from('records');

    $query = $this->db->get();
    if ($count) {
           return $this->db->get()->num_rows();
      }

      if (empty($offset) && !empty($limit)) {
           $this->db->limit($limit);
      }
      if (!empty($offset) && !empty($limit)) {
           $this->db->limit($limit, $offset);
      }

      $result = $this->db->get()->result();
      return $result;
}

Upvotes: 1

Ash-b
Ash-b

Reputation: 713

In The first function itself you can get the count using $query->num_rows() function

public function selectRecords() {
   $return = array();
   $this->db->select('*');
   $this->db->from('records');
   $query = $this->db->get();
   $return['count']   =  $query->num_rows(); 
   $return['records'] =  $query->result_array();
   return $return;
}

Upvotes: 1

Alessandro Minoccheri
Alessandro Minoccheri

Reputation: 35973

you can do only:

public function selectRecords() {
    $this->db->select('*');
    $this->db->from('records');
    $query = $this->db->get();
    return $query->result_array();
}

and 

$records = $this->selectRecords();
$count = count($records);

Upvotes: 1

Related Questions