[eluser]redcore[/eluser]
I did it with a query found at <a href="http://blogs.tech-recipes.com/johnny/2006/08/23/how-to-mimic-mysqls-limit-feature-in-ms-t-sql/#comment-20991">tech-recipes</a> that uses BETWEEN instead of OFFSET.
Since I use MSSQL (my employer uses MSSQL, not necessarily my choice) I have to figure out ways to do some of CI's MySQL-only stuff, like how OFFSET is normally used easily with the pagination (using the active records class) library. I've been learning CI with Jeffrey Way's <a href="http://net.tutsplus.com/articles/news/codeigniter-from-scratch-day-7-pagination/">CI From Scratch on NetTuts</a> - pagination has been a bit more involved figuring out how to use it with MSSQL. Here's what I came up with...
Code:
class Site extends Controller {
function index()
{
$this->load->library('pagination');
$this->load->library('table');
$this->table->set_heading('ID', 'The Title', 'The Content');
$config["base_url"] = 'http://localhost:8888/ci/index.php/site/index';
$config["total_rows"] = $this->db->get('data')->num_rows();
$config["per_page"] = 10;
$config["num_links"] = 20;
$config["full_tag_open"] = '<div id="pagination">';
$config["full_tag_close"] = '</div>';
// start MSSQL-specific code
$table = 'data';
$fields = 'id, title, contents';
$this->pagination->initialize($config);
if($this->uri->segment(3))
{
$totalTo = $this->uri->segment(3)+10;
} else {
$totalTo = $config["per_page"];
}
$totalFrom = $totalTo - $config["per_page"] + 1; // add one so we don't overlap the number from the last result set
$data["records"] = $this->db->query("WITH temp AS
(SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rownum
from $table)
SELECT $fields FROM temp WHERE rownum BETWEEN $totalFrom AND $totalTo");
$this->load->view('site_view', $data);
}
}
I'm not sure if it works with MSSQL 2000, but it definitely works with 2005+. If you're using 2000, you should really look at upgrading anyways
Hope that helps folks. I seen a few other alternative SQL queries for paginating MSSQL results and they were either sort of heavy SQL-wise or they could slow down performance with large data sets. This SHOULD perform well enough, especially with the correct indexes. Always interested in being better, though, so if someone finds a flaw (with the custom MSSQL parts, not the tutorial parts...) let me know
