Paginating db data using query only in a Model

[eluser]Ngulo[/eluser]
hello to all guys, I'm having a lot of problems to do
Pagination of data from the db.
I would only use my Model to make query db.

so i put this function into my Model:

Code:

function populateByCategory($category)
  {
      $query = $this->db->get_where('photo', array('category' => $category));

      return $query;
  }

this function Model is great and it works fine,returning me the data query array() .

but when i go to the Controller i can't retrieve them so:

Code:

$category = $this->uri->segment(3);
            $modelPhoto = $this->load->model('photo');
            

            $this->load->library('pagination');
            $offset = $this->uri->segment(4);
            
           $config['per_page'] = '1';
           $config['base_url'] = base_url().'photos/category/'.$category.'/';
           $config['total_rows'] = $this->photo->populateByCategory($category)->num_rows();
           $config['uri_segment'] = 5;
          
           $this->pagination->initialize($config);
           $view['paginator'] = $this->pagination->create_links();

any suggest guys?

how can i retrieve data from db now and paginate them?


thanks i hope you'll understand my bad enlglish :/

[eluser]Federico Baña[/eluser]
I would return $query->result() in the model first.

Code:

function populate()
{
   $query = $this->db->get_where( 'table', array( 'field' => 'value' ) );
   return $query->result();
}

Then in the controller you have to split the result array yourself.

Code:

$photos = $this->photos->populate(); // This returning the $query->result(); instead of $query
$offset = $this->uri->segment(4); // offset
$per_page = 1; // photos per page

$config['per_page'] = $per_page;
$config['base_url'] = base_url().'photos/category/'.$category.'/';
$config['total_rows'] = count($photos);
$config['uri_segment'] = 4;

// Then to the view I'd send a sliced array like this
$data = array( 'photos' => array_slice( $photos, $offset, $per_page ) );
$this->load->view( 'whatever', $data );

http://php.net/manual/en/function.array-slice.php

[eluser]Ngulo[/eluser]
Hi Federico, really thanks for time spent with my problem :/

i know i'm a beginner,but i can't retrieve again my db datas

i made this:

Model:

Code:

function populateByCategory($category)
  {
      $query = $this->db->get_where('khc_photo', array('category' => $category));
      
      return $query->result();

  }

COntroller:

Code:

$category = $this->uri->segment(3);
            $this->load->model('photo');
            $this->load->library('pagination');
            $offset = $this->uri->segment(5);
            $populatePhotoList = $this->photo->populateByCategory($category);
            
            $per_page = 1;

            $config['per_page'] = $per_page;
            $config['base_url'] = site_url().'/photos/category/'.$category.'/';
            $config['total_rows'] = count($populatePhotoList);
            $config['uri_segment'] = 5;
          
            
            $this->pagination->initialize($config);
            
            $view['paginator'] = array('PhotoList'=>array_slice($populatePhotoList,$offset, $per_page)  );
            
          

            
            $this->load->view('photos_category_view',$view);

View:

Code:

<table class="category-photo">
                &lt;?php foreach($paginator['PhotoList'] as $row){?&gt;
                <tr>

                    <td><img src="&lt;?php echo base_url()?&gt;uploads/img/&lt;?php echo $row['category'].'/'.$row['photo'] ?&gt;"/></td>


                </tr>
                &lt;?php }
                ?&gt;
            &lt;?php echo $this->pagination->create_links();?&gt;
            </table>

it still doesn't work.... :-S

i made a var_dump($paginator);

and result seems to be right:

Code:

array(1) { ["PhotoList"]=> array(1) { [0]=> object(stdClass)#16 (7) { ["id"]=> string(3) "123" ["title"]=> string(0) "" ["description"]=> string(0) "" ["photo"]=> string(20) "fasd.jpg" ["category"]=> string(4) "2001" ["date"]=> string(10) "28-07-2010" ["datetime"]=> string(8) "16:27:09" } } }

also do you know if my base url is so:

Code:

http://localhost/asd/index.php/photo/category/2001/

must i retrieve my pagination $offset with $this->uri->segment(4)? right so?

thanks again !!!!

[eluser]Ngulo[/eluser]
any others suggestions guys?

[eluser]ELRafael[/eluser]
Ngulo,

Try to use $this->db->last_query() to check with the query is ok.

Look a piece of my code
Model (model_name)

Code:

function search($start, $end)
{
    //Your code
    $this->db->limit($end, $start);
    return $this->db->get('table_name');
}

Controller:

Code:

$per_page = 5;
$start = $this->uri->segment(3, 0);
$this->load->library('pagination');
$config['base_url']     = base_url().'main/procura_casa/';
$config['total_rows']     = $num_rows;
$config['per_page']     = $per_page;
$config['num_links']    = 3;
$config['uri_segment']  = 3;
$config['first_link']     = '≤';
$config['last_link']    = '≥';
$this->pagination->initialize($config);
$this->data['links'] = $this->pagination->create_links();
$casas = $this->model_name->search($start, $per_page);
$this->data['casas']     = $casa;

View:

Code:

<?php foreach ( $casas->result() as $c ) : ?>
    &lt;?php echo $c->id; ?&gt;<br />
&lt;?php endforeach; ?&gt;
&lt;?php if ( $links ) : ?&gt;
    <div class="paginacao">Páginas: &lt;?php echo $links; ?&gt;</div>
&lt;?php endif; ?&gt;

[eluser]Ngulo[/eluser]
Hello ELRafael,

maybe I explained evil, because of my bad English.

I have an array_slice() that contains all data taken from db.

Then if I try to var_dump () (what I posted above) ,i can see this same array data, then the query is right ...

Since the data are correct (I've checked)

The problem is really that I can not get them out and print them in the view! I suppose

[eluser]ELRafael[/eluser]
Hum....

Inside your view loop, if you try to write something...

Code:

<?php foreach ( $paginator['PhotoList'] as $row ): ?>
   Ok, I'm inside this loop.
<?php endforeach; ?>

I guess you're doing this wrong

Code:

<img src="&lt;?php echo base_url()?&gt;uploads/img/&lt;?php echo $row['category'].'/'.$row['photo'] ?&gt;"/>

When you use return $this->db->get('table')->result(), the result will be an object, not an array.
Try to use something like that:

Code:

<img >category; ?&gt;" />

Don't worry about your english!! My english is bad too

[eluser]Ngulo[/eluser]
hi guys,i just fixed my problem....thanks to all of you

the problem was in my view i use this to make work my controller and model:

Code:

<?php foreach ($paginator['PhotoList'] as $row){?>
                <tr>

                    <td><img >category.'/'.$row->photo ?&gt;"/></td>


                </tr>
                
                &lt;?php ;}?&gt;
              
            &lt;?php echo $this->pagination->create_links();?&gt;

really thanks for suggestions

[eluser]Ngulo[/eluser]
[quote author="Ngulo" date="1281118112"]hi guys,i just fixed my problem....thanks to all of you

the problem was in my view i use this to make work my controller and model:

Code:

<?php foreach ($paginator['PhotoList'] as $row){?>
                <tr>

                    <td><img >category.'/'.$row->photo ?&gt;"/></td>


                </tr>
                
                &lt;?php ;}?&gt;
              
            &lt;?php echo $this->pagination->create_links();?&gt;

really thanks for suggestions [/quote]


i can't right the code inside this forum texteditor: anyway this is---

<?php foreach ($paginator['PhotoList'] as $row){?>
<tr>

<td><img >category.'/'.$row->photo ?&gt;"/></td>


</tr>

&lt;?php ;}?&gt;

&lt;?php echo $this->pagination->create_links();?&gt;

[eluser]Jondolar[/eluser]
I don't think you really want to return the entire data set every time. You only want to return the records to be displayed, right? Your model could have two functions, one that returns just the count of your query based on your "where" clause. You call that to get the total records. Then, another query that returns the results of the query based on your "where" clause AND your "limit" clause. Although this takes two trips to the database, it doesnt' return 10,000 records just to display 25 and it doesn't require the array_split() function.

Alternatively, you could keep the full count of records per category in the category record as a field. Then, if you already queried the category prior to retrieving the photo records, you'd already have the count. Any time an update or delete is done, just update the category count.

Good luck with your project.