Codeigniter Select and Count MySQL Records

Question

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?

You can display count as count(array) in view. No need to do any particular query for this. — Pragna
– Pragna, Commented May 10, 2018 at 13:22

Pradeep · Accepted Answer · 2018-05-10 13:14:52Z

1

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';?>

edited May 10, 2018 at 13:14

answered May 10, 2018 at 13:09

Pradeep

9,70713 gold badges29 silver badges34 bronze badges

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

1 Comment

mickmackusa Mar 25 at 23:24

I do not endorse this model method design -- it is only conditionally returning a payload. If there are no rows in the result set, there is no return. This will mean that the $count and $all_records will not exist in the view and will emit complaints from PHP.

Alessandro Minoccheri · Accepted Answer · 2018-05-10 13:06:24Z

1

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);

answered May 10, 2018 at 13:06

Alessandro Minoccheri

36.1k22 gold badges128 silver badges176 bronze badges

1 Comment

mickmackusa Mar 25 at 23:19

select('*') never needs to be added to an active record chain. get() can receive the tablename, therefore the from() method call can also be removed. Simply write: public function select(): array { return $this->db->get('records')->result_array(); } Then the call from the controller would be $records = $this->Records_model->select();

Ash-b · Accepted Answer · 2018-05-10 13:07:32Z

1

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

answered May 10, 2018 at 13:07

Ash-b

7137 silver badges11 bronze badges

1 Comment

mickmackusa Mar 25 at 23:22

select('*') never needs to be added a CodeIgniter active record -- it is the default. get()'s first parameter is the tablename, so the from() call can be omitted too. Use type hinting everywhere possible.

public function selectRecords(): array { $resultObject = $this->db->get('records'); return ['count' => $resultObject->num_rows(), 'records' => $resultObject->result_array()]; }

Vijay Sharma · Accepted Answer · 2018-05-10 13:10:38Z

1

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

answered May 10, 2018 at 13:10

Vijay Sharma

8318 silver badges16 bronze badges

1 Comment

mickmackusa Mar 25 at 23:16

This question never mentioned pagination. select('*') never needs to be written in CodeIgniter. If $params might hold limit and offset data then that can be null coalesced directly into the get() call after the tablename is passed in. I'd say that a selectRecords() method should never return the num rows count -- that would be a poor naming choice. A method to return an array or an integer is going to be messy. I would write only this: select(?int $limit = null, ?int $offset = null): array { return $this->db->get('records', $limit, $offset)->result(); }. See? No mess.

mickmackusa · Accepted Answer · 2025-03-25 20:52:45Z

0

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 (but I've never found myself in this situation).

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

edited Mar 25 at 20:52

answered Apr 13, 2024 at 11:29

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

Collectives™ on Stack Overflow

Codeigniter Select and Count MySQL Records

5 Answers 5

1 Comment

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related