Add pagination to all pages that show past N request

[lencioni]

It would be nice to have pagination so you are able to browse backward in time. Alternatively, it would be great to be able to specify a date range a la Google Analytics.

[lencioni]

Pagination in MySQL is very straightforward with the LIMIT syntax, but for MSSQL, things get a bit more complicated.

I've written some functions that work to mimic the limit syntax for MSSQL. They are part of a larger class and a bit on the old side, so they might need some tweaking, but here's the quick copy and paste:

/**
 * @param string $sql
 * @param string $orderBy
 * @return string
 */
final protected static function limitOrderBy($sql, $orderBy = 'ASC')
{
    // get the "order by" statement how we need it
    $r      = preg_replace('/.*?ORDER(.*)/ims', 'ORDER$1', $sql);
    $r      = preg_replace('/[\w]+\.([\[\]\w]+)/', '$1', $r);

    $parts  = explode(',', $r);
    foreach($parts as &$orderPart)
    {
        if (preg_match('/[\s](DE|A)SC[\s]*$/i', $orderPart) === 0)
        {
            $orderPart  .= ' ' . $orderBy;
        }
    }
    return implode(',', $parts);
}

/**
 * @param string $sql
 * @return string
 */
final protected static function stripOrderBy($sql)
{
    return preg_replace('/^(.*?)ORDER.*$/ims', '$1', $sql);
}

/**
 * @param string $sql
 * @param integer $start
 * @param integer $stop
 * @param string $orderBy
 * @return string
 */
final public static function limit($sql, $start, $stop, $orderBy = 'ASC')
{
    // Get the "order by" statement how we need it
    $orderBySql = self::limitOrderBy($sql, $orderBy);
    $sql        = self::stripOrderBy($sql);
    $rows       = $stop - $start + 1;

    return "
    SET ROWCOUNT $rows;
    SELECT TOP $rows * FROM (
        SELECT ROW_NUMBER() OVER ($orderBySql) AS rownumber, sqlQuery.*
        FROM ($sql) AS sqlQuery
    ) AS rowedQuery
    WHERE rownumber >= $start
        AND rownumber <= $stop
    ";
}