Return both query result and num rows

Question

I'm working in MVC CodeIgniter with MySQL and PHP. Common case - I get data from database and I want count of them. I wonder how can I do it in the most optimial way. Is it sensible to do it in one function?

I will share with You my solution and please tell me is it a good approach. Let's say we have a Model function:

public function get_results()
{
   $query = $this->db->get('data');
   $result = $query->result();
   $count = $query->num_rows();
   return [$result, $count];
}

Question isn't that silly to return both values that way?

I want to avoid creating new function num_result() executing another SQL for only count rows since I have already ran one. Is there any other effective way to take count from one query execution?

proper way is to have assoc array returned with your data `return ['result'=>$result, 'count'=>$count];` — venimus, Dec 03 '15 at 11:23
PostMans, that looks smart, but don't I call function again (and execute script one more time)? EDIT: Got it :-) Just assign return to some variable and count that variable. — Twardy, Dec 03 '15 at 11:43

score 2 · Accepted Answer · answered Dec 03 '15 at 11:42

2

also you can use your function:

public function get_results()
{
   $query = $this->db->get('data');
   $result = $query->result();
   return $result
}

then when call the function:

$result= $this->your_model->get_results();
$count = count($result);

answered Dec 03 '15 at 11:42

Gouda Elalfy

6,888
1
26
38

It's not a good idea to count it on php side. If you will have more records it can be very slow solution. Script first will get all records and after that count it in slow php function. Better use 2 SQL queries. – Kamil P Dec 04 '15 at 12:58
Very incorrect @Kamil. php's `count()` is O(1) it -- it doesn't take any more effort to count a huge array than it does to count a single-element array. Please remove your comment so that researchers are not misled. https://stackoverflow.com/q/4566314/2943403 , https://stackoverflow.com/q/5835241/2943403 – mickmackusa Nov 20 '20 at 04:10

score 0 · Answer 2 · answered Dec 03 '15 at 11:18

return the query object from your model //Model

public function get_results()
{
   return $this->db->get('data');

}

//Controller

public function index(){
    $obj = $this->mymodel->get_results();
    $obj->num_rows(); //Count result
    $obj->result();   // rows object
    $obj->result_aray(); //rows array

}

score 0 · Answer 3 · answered Dec 03 '15 at 11:20

0

You can do it by these way :-

public function get_results()
{
   $query = $this->db->get('data');
   $data['result'] = $query->result();
   $data['count'] = $query->num_rows();
   return $data;
}

It may help you.

answered Dec 03 '15 at 11:20

Harsh Sanghani

1,666
1
14
32

score 0 · Answer 4 · answered Nov 20 '20 at 04:21

Since PHP's count() is O(1), I don't see any benefit in storing the count or passing it between layers of the MVC design.

If you need the result set data and the number of rows in the result set in the controller or in the view, just call count() in that respective layer.

If you ONLY need the count, then your model method (query), should be using something that starts with SELECT COUNT(1) or SELECT COUNT(*).

If you only need the result set and not the count, then excellent, your model method hasn't been over-engineered to return values that will go un-used.

Effectively, your model can be this simple:

public function get_results(): array
{
   return $this->db->get('data')->result();
}

Codeigniter's result() method will unconditionally return an array-type value. It will either be an empty array or an array of objects. Consistent. Lean. Clean. Re-usable.

Return both query result and num rows

4 Answers4

Linked