CodeIgniter Forums
Returning number of rows - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Returning number of rows (/thread-55194.html)



Returning number of rows - El Forum - 10-14-2012

[eluser]alvaroeesti[/eluser]

Hello

Well, I need two pieces of information. One the block of rows with their content, and the other how many rows they are, for pagination purposes.


UPDATED:


I solved it by some weird way, (if anyone has a better idea, I ll be interested in hearing about it). I solved this way. I realized that I had two objects, which were being returned as an array of objects. So in order to return all that at once I created an array of arrays of objects. Once at the controller, I unshifted them of the multidimensional array and I used one for returning the content and the other one to get the number of rows by nothing else than counting (using count) the number of items the array had.

=====

continuing with old post:

I do my first query which goes like this:

Code:
$this->db->select('id_atributo, thumb_a,etc');
  
  $this->db->from('localidades x');
  $this->db->join('regiones y', 'y.id_region = x.id_region');
                etc

$this->db->limit(2, 0);
  
  $query = $this->db->get();

And then I sort of repeat the same query except for the Limit stuff, and add this to get the number of rows:

Code:
$question = $this->db->get();
  
  
$filas = $question->num_rows();


So, now that I have these two queries within one Function in the model, I need to return all that:

Code:
return $query->result();

But as you see, I only know how to return the first query. I don't know how to add the additional piece of info, which is the total number of rows that I do in the second query.

You might ask yourself why I repeat the queries. The thing is that if I do num_rows when I have used the Limit, it does not bring me the total number of rows but just the ones chunked by the Limit clause. That is why I repeat the query again without the Limit.

In the controller I have this:


Code:
$data = $this->toModelPage->toModelFunction($etc);

So, that is the issue, how to pass the number of Rows. I have got it fine when it is just about passing the array of objects, but the problem is that additional num_rows() stuff.


thank you

regards

A


Returning number of rows - El Forum - 10-14-2012

[eluser]skunkbad[/eluser]
Maybe try something like this (untested):

Code:
// MODEL ------------------------------------------
private $query_params = array();

public function localidades_data( $params = FALSE, $count = FALSE )
{
// Set params if this is the first call
if( $params !== FALSE )
{
  $this->query_params = $params;

  // Compute / set the offset for the query
  $this->query_params['offset'] = ( $params['page'] * $params['limit'] ) - $params['limit'];
}

// If this is the actual data query, we want to add a SELECT to our query
if( ! $count )
{
  $this->db->select('x.*, y.*');
}

$this->db->from('localidades x');
$this->db->join('regiones y', 'y.id_region = x.id_region');

// If this is the row count
if( $count )
{
  return $this->db->count_all_results();
}

// If this is the data query
else
{
  // Set the limit / offset
  $this->db->limit( $this->query_params['limit'], $this->query_params['offset'] );

  $query = $this->db->get();

  // Return data if there is any
  if( $query->num_rows() > 0 )
  {
   return $query->result();
  }
}

return FALSE;
}

// CONTROLLER -------------------------------------
// The pagination class doesn't allow for multiple config files, so we load configuration the old fashion way
$this->config->load( 'pagination/localidades' );
$pagination_config = config_item('localidades_pagination_settings');

// Set the query params for both count and data queries
$query_params = array(
'limit'      => $pagination_config['per_page'],
'page'       => (int) $page
);

// Get the total rows that match the requested set of localidades
$pagination_config['total_rows'] = $this->localidades_data_model->localidades_data( $query_params, TRUE );

// Initialize pagination and create links
$this->pagination->initialize( $pagination_config );
$view_data['pagination_links'] = $this->pagination->create_links();

// Get the actual localidades data that matches the requested set of localidades
$view_data['localidades_data'] = $this->localidades_data_model->localidades_data();

This is the way I do it in Community Auth


Returning number of rows - El Forum - 10-14-2012

[eluser]alvaroeesti[/eluser]


Thank you. Isn't that too long?

What I did is this:

Code:
return array($question->result(), $query->result() );

So, as you can see, I returned an array which is an array of arrays of objects.

Then at the controller I just took them out of the multidimensional array and worked with them. It works


Returning number of rows - El Forum - 10-14-2012

[eluser]skunkbad[/eluser]
Before I posted the code I stripped out parts that were necessary for search. My code just shows that the query for the count and the query for the data can be done in the same method. You may find that when your queries become more complex you end up going with something like this. Whatever works works!