How use class pagination with Join ?

[eluser]Gravedigger[/eluser]
Hi,
i have a question.
How to use class pagination with a sql join ?

Code:

$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = blogs.id');

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

I can't bet in

Code:

get('blogs', $num, $offset)

Can you help me please

[eluser]Atharva[/eluser]

Code:

function index(){
      $this->load->library('pagination');

      $per_page= 5;
      $offset = (int) $this->uri->segment(3, 0);

      $config['base_url'] = base_url().index_page().'controller/index/';
      $this->db->select('*');
      $this->db->from('blogs');
      $this->db->join('comments', 'comments.id = blogs.id');

      $this->db->limit($per_page,$offset);

      $data['records'] = $this->db->get();

      $data['total_rows'] = $this->db->query("SELECT COUNT(id) AS cnt FROM blogs b INNER JOIN  comments c ON  b.id = c.id")->row()->cnt;



      $config['total_rows'] = $data['total_rows'];
      $config['uri_segment'] = 3;
      $config['num_links'] = 3 ;
      $config['per_page'] = $per_page;
      $this->pagination->initialize($config);
      $data['offset']=$offset;

      
      $data['links']=$this->pagination->create_links();
      $this->load->view('your_view',$data);
}

And in your_view , you can echo $links to generate links.

[eluser]Gravedigger[/eluser]
Ok i'll try this.
Thank's

[eluser]Gravedigger[/eluser]
I have test you code but i have an error, i try this:

Code:

function categorie(){
    $this->load->library('pagination');

    $per_page= 5;
    $offset = (int) $this->uri->segment(3, 0);

    $config['base_url'] = base_url().index_page().'/site/categorie/index/';
    $this->db->select('*');
    $this->db->from('videos');
    $this->db->join('lien_categorie', 'lien_categorie.id = videos.id_video');
    $this->db->where('lien_categorie.id_categorie = "'.$this->uri->segment(3).'"');
    $this->db->limit($per_page,$offset);

    $data['records'] = $this->db->get();
    
    echo'<pre>';print_r($data['records']);echo'</pre>';
    $data['total_rows'] = $this->db->query("SELECT COUNT(c.id) AS cnt FROM videos v INNER JOIN  lien_categorie c ON  v.id_video = c.id")->row()->cnt;
    


    $config['total_rows'] = $data['total_rows'];
    $config['uri_segment'] = 3;
    $config['num_links'] = 3 ;
    $config['per_page'] = $per_page;
    $this->pagination->initialize($config);
    $data['offset']=$offset;

    $data['links']=$this->pagination->create_links();
    $this->load->view('categorie',$data);
  }

Message: Trying to get property of non-object

And in my array of the print i have:

Code:

CI_DB_mysql_result Object
(
    [conn_id] => Resource id #32
    [result_id] => Resource id #38
    [result_array] => Array
        (
        )

    [result_object] => Array
        (
        )

    [current_row] => 0
    [num_rows] => 0
    [row_data] =>
)

[eluser]cideveloper[/eluser]
Something I just noticed. How can you have your offset and id_categorie be

Code:

$this->uri->segment(3)

and your total_rows should have the same where clause used to get the data.

According to your code total rows will have everything while you data is only obtained using the specific id

[eluser]Gravedigger[/eluser]
Yes i have change the uri to 3. now the offset take a good row.

But my result doesn't show an array and i can't recover the data in my view.

[eluser]cideveloper[/eluser]
Try this. I also changed the base url because it didn't really make sense to me.

Code:

function categorie(){

        $this->load->library('pagination');

        $cat_id = $this->uri->segment(3,0);
        $per_page= 5;
        $offset = (int) $this->uri->segment(4, 0);

        $config['base_url'] = base_url().'site/categorie/'.$cat_id .'/';
        $this->db->select('*');
        $this->db->from('videos');
        $this->db->join('lien_categorie', 'lien_categorie.id = videos.id_video');
        $this->db->where('lien_categorie.id_categorie = "'.$cat_id.'"');
        $this->db->limit($per_page,$offset);

        $data['records'] = $this->db->get();
        
        echo'<pre>';print_r($data['records']);echo'</pre>';
        $data['total_rows'] = $this->db->query("SELECT COUNT(c.id) AS cnt FROM videos v INNER JOIN  lien_categorie c ON  v.id_video = c.id where l.id_categorie='".$cat_id."'")->row()->cnt;
        


        $config['total_rows'] = $data['total_rows'];
        $config['num_links'] = 3 ;
        $config['per_page'] = $per_page;
        $this->pagination->initialize($config);
        $data['offset']=$offset;

        $data['links']=$this->pagination->create_links();
        $this->load->view('categorie',$data);
    }

[eluser]Gravedigger[/eluser]
Ty Progr@mmer for help me,
i try your function, i think the pagaination was good.
The sql to, i have debug mode On.
the first one

Code:

SELECT *
FROM (`videos`)
JOIN `lien_categorie` ON `lien_categorie`.`id` = `videos`.`id_video`
WHERE `lien_categorie`.`id_categorie` = "1"
LIMIT 5

In phpMyadmin that's return good entry (9 array) and the count is good to (9):

Code:

SELECT COUNT(c.id) AS cnt FROM videos v INNER JOIN  lien_categorie c ON  v.id_video = c.id where c.id_categorie='1'

But i've got:GET DATA

No GET data exists

In my view i make a foreach on:

Code:

$records

That's not return the data with an array of my first sql.

CI_DB_mysql_result Object
(
[conn_id] => Resource id #40
[result_id] => Resource id #46
[result_array] => Array
(
)

[result_object] => Array
(
)

[current_row] => 0
[num_rows] => 5
[row_data] =>
)

[eluser]Cristian Gilè[/eluser]
To generate query results you have to use:

Code:

$data['records'] = $this->db->get()->result(); //return the query result as an array of objects

or

$data['records'] = $this->db->get()->result_array();//return the query result as a pure array

Cristian Gilè

[eluser]cideveloper[/eluser]
Cristian Gilè is correct. You need to run the result() on your $this->db->get();

I would however keep the controller the way it is and then do this in your view

Code:

foreach ($records->result() as $row)
{
   echo $row->field1;
   echo $row->field1;
   echo $row->field1;
}

The only reason I say do it that way is so that you have access to the below in your view

Code:

$num_of_rows = $records->num_rows();
$num_of_fields_selected = $records-->num_fields();

Just my opinion though.

One thing you really should do is move your queries over to a model. It really cleans up your controllers and makes it easier to read and work with. MVC starts with "Model"