[eluser]Iksander[/eluser]
Now, I have found this SQL statement to mimic OFFSETS in MSSQL (for pagination):
Code:
"SELECT * FROM (
SELECT TOP {$this->db->escape($per_page)} * FROM (
SELECT TOP {$this->db->escape($per_page+$offset)} *
FROM nfr_vendors
$where
ORDER BY $order ASC) AS t1
ORDER BY $order DESC) AS t2
ORDER BY $order ASC"
As you can see slightly modified for my own uses... It works just fine, but, has a somewhat confusing side-effect...
When I paginate results that are not in EXACT multiples of whatever my item limit is - I get 'spill-over'; say I have 14 rows in my DB and I have an item per-page limit of 10. Normally the first page would display the first 10 rows (which this does too), then the second page would only have the REMAINING 4 rows. What actually happens is, the first page predictably displays the first 10 rows; BUT the second page displays 10 rows with the first four being the cut-off and the last four on the tail end of the results display.
Code:
First page
----------
entry1
entry2
entry3
entry4
entry5
entry6
entry7
entry8
entry9
entry10
Second page
-----------
entry5
entry6
entry7
entry8
entry9
entry10
entry11 <--- Here are the straggling results...
entry12
entry13
entry14
While it technically does work, it isn't working exactly as I want it to. I would rather have the offset leave only those remaining rows in the results.
I am not an SQL guru but I will continue to tinker - if someone has run into this or has a moment of inspiration, the advise would be appreciated!
