Pagination not working

[eluser]bhakti.thakkar[/eluser]
i have a page in which i display all the projects of a particular client. e.g. there are 24 records for a particular client. but i get all the 24 records at a stretch in my view. i have specified 5 records per page but it doesnt work. there are proper ‹ First < 1 2 3 4 5 > on my view page but all 24 records are shown on all the links. what is wrong ? please help me

Controller : projects.php:

Code:

function index()
    {
        $start = (int) $this->uri->segment(3);
        ($start) ? $lower = $start : $lower = 0;

        $this->load->library('pagination');
        $config['base_url'] = base_url().'index.php/projects/index/';
         $config['uri_segment'] = 3;
        $config['total_rows'] = $this->projects_model->ProjectCount(); // returns 24 rows
        $offset = (int) $this->uri->segment(3, 0);
        $config['per_page'] = '5';

//        $config['num_links'] = 5;
        $config['full_tag_open'] = '<p class="pagination">';
        $config['full_tag_close'] = '</p>';
        $this->pagination->initialize($config);  
        $data['pagination'] = $this->pagination->create_links();

        $data['query'] = $this->projects_model->getProjects($lower, $config['per_page']);
        $data['total_rows'] = ($data['query']) ? $data['query']->num_rows() : 0;

    
        $data['message'] = ($this->session->flashdata('message') != '') ? '<p class="error">'.$this->session->flashdata('message').'</p>' : '';
        $data['page_title'] = 'Projects';
        $this->load->view('projects/index', $data);
    }

my view :

Code:

<?php
if (isset($pagination)) {
    echo $pagination;
}
?>
<table class="stripe" id="invoiceListings">
  <tbody id="invoiceRows">
    <tr>
      <th class="invNum" width="8%">Sr. no.</th>
      <th class="invNum">Project ref.</th>
      <th class="dateIssued">Name</th>
    </tr>
&lt;?php
if (isset($total_rows) && $total_rows == 0):
?&gt;
    <tr>
      <td colspan="5">No records found
      </td>
    </tr>
&lt;?php
else:

    $i=0;
    foreach($query->result() as $row):
    $i++;
?&gt;

    <tr>
      <td>&lt;?=$i?&gt;</td>
      <td>&lt;?php echo anchor('invoices/view/'.$row->Project_ID,$row->Project_ID);?&gt;</td>
     <td>&lt;?php echo anchor('invoices/view/'.$row->Project_VC, htmlentities($row->Project_VC));?&gt;</td>

    </tr>
    &lt;?php
    endforeach;
    endif;
    ?&gt;
  </tbody>
</table>


Model :
[code]
    function getProjects()
    {
        return $this->_getProjects();
    }

    // --------------------------------------------------------------------


    function _getProjects($offset=0, $limit=100)
    {
        $relation_id = $this->session->userdata('srelation_id');
        // check for any invoices first
        if ($this->db->count_all_results('Project_T') < 1)
        {
            return FALSE;
        }


        $this->db->select(' * ');
        $this->db->where(" Relation_ID = '$relation_id' and Deleted_DT is null");
        
        $this->db->offset($offset);
        $this->db->limit($limit);
        return $this->db->get('Project_T');
    }

Thanks in anticipation


[/code]

[eluser]julgus[/eluser]
Hi,
must be something wrong with the limit offset,scope in your sql in the method

Code:

$this->projects_model->getProjects($lower, $config['per_page']);

I use the pagination without any problems in my project.
Regards

[eluser]bhakti.thakkar[/eluser]
yes i was not passing the offset and limit properly to my model which i changed as below:

Code:

function getProjects($offset , $limit)
    {
        return $this->_getProjects($offset , $limit);
    }

    // --------------------------------------------------------------------


    function _getProjects($offset, $limit)
    {

        $relation_id = $this->session->userdata('srelation_id');
        // check for any invoices first
        if ($this->db->count_all_results('Project_T') < 1)
        {
            return FALSE;
        }
        $this->db->select(' * ');
        $this->db->where(" Relation_ID = '$relation_id' and Deleted_DT is null");
        
        $this->db->offset($offset);
        $this->db->limit($limit);
        return $this->db->get('Project_T');
    }

now the links are working but one strange problem is when i open the page 1st time it shows me 5 records. then when i click next, the first five records remain on the same view and next five are appended to it. that means 2nd page has 10 records and this goes on of all the links. the 3rd page has 15 records and i printed the offset and limit and they are geting incremented and passed properly. think there is some problem here :

Code:

$config['base_url'] = base_url().'index.php/projects/index/';
$config['uri_segment'] = 3;

can anyone figure it out what is wrong?

[eluser]BaRzO[/eluser]
You don't have to use

Code:

<?php
if (isset($pagination)) {
    echo $pagination;
}
?>

Notes:

The $config array contains your configuration variables. It is passed to the $this->pagination->initialize function as shown above. Although there are some twenty items you can configure, at minimum you need the three shown. Here is a description of what those items represent:

* base_url This is the full URL to the controller class/function containing your pagination. In the example above, it is pointing to a controller called "Test" and a function called "page". Keep in mind that you can re-route your URI if you need a different structure.
* total_rows This number represents the total rows in the result set you are creating pagination for. Typically this number will be the total rows that your database query returned.
* per_page The number of items you intend to show per page. In the above example, you would be showing 20 items per page.

The create_links() function returns an empty string when there is no pagination to show.

if pagination needed. Links will be there but if not there will be empty string and to see what happening you can use profiling you can easily see what is going wrong

[eluser]Flemming[/eluser]

2nd page has 10 records and this goes on of all the links. the 3rd page has 15 records

it sounds like you simply have your limit and offset the wrong way round!

[eluser]bhakti.thakkar[/eluser]
no the limit and offsets are set correctly but the thing is i am using MSSQL DB which doesnt accept offset and limit. i should have specified it right at the first post itself the DB that i am using. i thought its some other issue. but its mssql issue which doesnt support these clauses.

Hope i get some crack on it. also in the mean while i will scan this forum's previous all post on this issue.

Thanks

[eluser]bhakti.thakkar[/eluser]
still this remains unsolved. can any one please provide any linked on in. i have even scanned all the related posts on this forum and i am not able to get anything work for me.

Thanks in advance

[eluser]bhakti.thakkar[/eluser]
I tried the below code which is working but there is only one problem. if for example there are 17 records and if i am setting 15 records per page then on the first page there will be 15 and on the second page instead of remaining 2, it show again 15 i.e. last 2 records + 13 records of previous page. but this is now what is completely ok. below is what i modified in my model.

Project_model.php: where $num is the limit

function _getProjects($offset, $num)
{
$relation_id = $this->session->userdata('srelation_id');

$offset = (!$offset) ? 0 : $offset;
$next = ($num + $offset);
$orderR = "asc";
$where = '';
$sql =
'select * from (' .
'select top ' . $num . ' * from (' .
'select top ' . ($num+$offset-1) . ' * ' .
'from Project_T where Relation_ID = ' .$relation_id.
'order by Project_ID asc) as t1 ' .
'order by Project_ID desc) as t2 ' .
'order by Project_ID asc';
$query = $this->db->query($sql);

return $query->result();

}

Hope i get the final help on it and it also helps some one.

[eluser]whobutsb[/eluser]
Hey There bhakti.thakkar, I'm in the same situation as you! I'am using a MSSQL DB and trying to use the pagination script and whenever I go to the 2nd page I don't get the next set of results they are just appended to the first set of results. Did you ever figure out why this was happening? Does the CI Pagination method not handle MSSQL DBs?

Thanks, in advance,
Steve

[eluser]bhakti.thakkar[/eluser]
Hi Steve,
The problem is there is no limit and offset clause in MSSQL DB. So even if you specify limit and offset, things are not goin to work. Well, right now , i am using the mechanism i have provided in my previous thread and things are working fine :-) the only problem i am facing is, if suppose there are 19 records and i want 15 records per page, then what happens is on first page 15 records are displayed well but on click of next it shows me again 15 i.e new 4 and previous pages 11 records. it shud be only 4 on the next page logically. but that fine until i find some alternative for it. i have scanned to numerous forums of CI and total scan search in this forum as well, but no solution so far.

If i could help you out in any way just let me know!!! :coolsmile: