• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQL SERVER PAGINATION

#1
[eluser]Unknown[/eluser]
I'm not a fans of sql server,but my company already use it for previous project.

Instead of go to the next page, CI pagination class would coupled with the next page.

I'm trying to fix it, and this code works for me .

function _limit($sql, $limit, $offset,$pkey='')
{
/*original code from CI
* $i = $limit + $offset;
* return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);
*/

$sql = strtolower($sql);
if($pkey == '') {
$pkey = substr($sql,7,strpos($sql,',') - 7);
}
//separate where clause
if(!strstr($sql,'where')) {
$left_where = $sql;
} else {
$left_where = substr($sql,0,strpos($sql,'where'));
}

if(strstr($sql,'order by') && !strstr($sql,'group by')) {
$right_where = substr($sql,strlen($left_where),strpos($sql,'order by') - strlen($left_where));
$order_by = substr($sql,strpos($sql,'order by'),strlen($sql) - strpos($sql,'order by'));
} else if(strstr($sql,'group by')){
//echo strpos($sql,'group by');
$right_where = substr($sql,strlen($left_where),strpos($sql,'group by') - strlen($left_where));
$order_by = substr($sql,strpos($sql,'group by'),strlen($sql) - strpos($sql,'group by'));
} else {
$right_where = substr($sql,strlen($left_where),strlen($sql)-strlen($left_where));
$order_by = '';
}

//separate * clause to insert TOP statement
$strSelect = substr($left_where,0,7);
$strRightFields = substr($left_where,6,strlen($left_where)-7);
$strLimit = $strSelect.' TOP '.$limit.' '.$strRightFields.' '.$right_where;

//generate offset sql
if(!$offset) $offset = 0;
$strOffset = $strSelect.' TOP '.$offset.' '.$pkey.' '.$right_where;
if(strstr($sql,'where')) {
$sql = $strLimit. ' AND ' .$pkey. ' NOT IN ( '.$strOffset. ' ' . $order_by .' ) '.$order_by;
} else {
//echo $strLimit;exit;
$strLimit = substr($strLimit,0,strpos($strLimit,"order by"));
$from = 'from '. substr($strRightFields,strpos($strRightFields,'from')+4,strlen($strRightFields)-strpos($strRightFields,'from')+4);
$sql = $strLimit. ' WHERE ' .$pkey. ' NOT IN ( '.$strOffset. ' ' . $from .' ) '.$order_by;
}
return $sql;
}

Some limitation :
1. Cannot use select '*', u have to place uniq field in the first place after select.
2. Uniq field is mandatory
3. I intend to provide uniq field inputs in fourth argument,but dont have idea?


Hope this will help, thanks


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.