MS SQL Server and pagination

[eluser]ELRafael[/eluser]
Hello again.

I have a problem with pagination + MS SQL Server (2000 version)

If i just use this code

Code:

$this->db->limit(5, 10);
$query = $this->db->get('table');

Don't work. I wanna to get the 5 first regs. In MySQL is easy as a piece of cake, but in MSSQL Server doesn't work. M$ SQL does not support offset!!

If i put an echo, $this->db->last_query(), shows a message with TOP (SELECT TOP 5), but this is not working (merda!)

I readed many topics but still haven't found what i looking for (oh, i know what i'm looking)

Someone light on?

[eluser]ELRafael[/eluser]
nothing yet?

[eluser]Unknown[/eluser]
Rafael,

I use something like this:

$this->db->select('TOP (limit) ColumnID, Column, Column');
$this->db->where('ColumnID NOT IN(SELECT TOP (offset) ColumnID FROM TableName)');

where (limit) and (offset) are your respective limits and offsets.

The problem is that MSSQL doesn't offer LIMIT functionality. I think somewhere else in the forums someone had written a patch to the active record class to allow the limit syntax to be used.

[eluser]llbbl[/eluser]
I'm sorry, but MS SQL ... seriously?? wtf

get a real database my friend.

[eluser]ELRafael[/eluser]
oh, i wish i can get other db... but sometimes you can't choose the structure!!! real life!!!

but thanks. i resolved another way. MS SQL 2000 sucks!!! I'm luck that i have Angus MacGyver as a teacher

[eluser]Silencez[/eluser]
Can you please post your solution?

Thank you

[eluser]ELRafael[/eluser]

Code:

SELECT
    fields
FROM
    TABLE st
INNER JOIN
    OTHER_TABLE obs ON obs.REF = st.REF
WHERE
    st.REF IN
            (
                SELECT
                    TOP %per_page% st2.REF
                FROM
                    TABLE st2
                WHERE
                    st2.REF NOT IN
                        (SELECT TOP %page_start% st3.REF FROM ST st3 ORDER BY st3.DESIGN)
                ORDER BY
                    st2.DESIGN
            )

%per_page% is how many registers you'll have in each page (like 10, 50, 100)
%page_start% is the start of pagination (1, 2, 3, 4).

Sure, in my query there is many others things, like WHERE field_name LIKE %something%. You'll need to make tests to see in which place of query you'll put the "wheres".

This case, I didn't use the CodeIgniter Database Library. Neither put this in Active Record. But You can try.

[eluser]whobutsb[/eluser]
I've had some poor results using CI's Active Record LIMIT method and Join method. Whenever I use the two together in a script the results are just appended on to the previous results. I'm not sure if anyone else has run in to this issue as well when working Active Record.

[eluser]mihaip007[/eluser]
Okay I am trying my hand here and the first thing is to play with MSSQL! because like many we don't have options sometimes.

I am very new to CodeIgniter. So here goes with my question:

If I could get an example I need something to help out with the following in simple terms: I want to read the table "customers" (all the field) from a database called "database" but with limits. If I have to use the example above how can i use it in CI?

[eluser]whobutsb[/eluser]
Hi There,
I highly recommend the script in this forum to help with the limit method in MSSQL. My next suggestion is if you don't have to join any other tables of information then doing a limit is very straight forward just write your code with the CI Active Record methods:

Code:

$this->db->select('*');
$this->db->limit($offset, $limit);
$query = $this->db->get('customers');
return $query->results;