Welcome Guest, Not a member yet? Register   Sign In
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




Theme © iAndrew 2016 - Forum software by © MyBB