Web Design Forum: Paginating MSSQL 2000 - Web Design Forum

If you have to work with MsSQL 2000, I pity you. I've had to do it and I've honestly lost weeks to one particular problem: pagination. Paginating more modern versions of MsSQL is apparently very easy, but in 2000 it's a real pain. There are a lot of articles out there suggesting various techniques, but I've had difficulties implementing most of them. Luckily, after much searching, I found one that works! Jeff Smith's technique, unlike most of the other ones, doesn't require nested statements or complicated procedures. It essentially runs the query, analyses it and then runs a slight modification of that query - this makes it only a little more processor-heavy than a page refresh. It will also only return the remaining entries on the last page, not the last full page. But the thing that I really like about it is the simplicity. Since there are only slight differences between both instances of the statement, it's possible to do a bit of string analysis and manipulation to convert a who query into a paginated one.

To start with, I have a db class that handles all my database interactions. Originally I was thinking of expanding this class with the paginate class I'm about to write, but I found it too tricky, so it's a separate class. I do still use the error function. As you may have guessed, the query and rows functions are mainly simple wrappers for mssql_query() and mssql_num_rows() respectivly. If you want to use this class, the following snippit is a good approximation of my error function:

class db {
    function error($main_note, $further_details = NULL, $query = NULL) {
        $die_string  = '<h1>Error</h1>';
        $die_string .= '<p>' . $main_note . '</p>';
        if (!is_null($further_details)) {
            $die_string .= '<p>' . $further_details . '</p>';
        }
        if (!is_null($query)) {
            $die_string .= '<p><pre>' . $query . '</pre></p>';
        }
        die($die_string);
    }
}

Let's start our paginate class:

class paginate {
 
    private $original_query; // The original query to manipulate.
    private $results_per_page = 20; // The number of results per page.
    private $page = 1; // The current page we're viewing.
    private $get_page; // The $_GET key that contains our page number.
    private $number_of_pages = 1; // The number of pages created by this class.
    private $db; // A reference to the db class for database interactions.
    private $paginated_query; // The final paginated query.
 
    function __construct($query, $results_per_page = 20, $get_page = 'page') {
  
        global $db;
        $this->db = $db;
  
        // Work out which page we're on.
        if (isset($_GET[$get_page]) && is_numeric($_GET[$get_page])) {
            $this->page = (int)$_GET[$get_page];
        }

        // Store the number of results per page.
        if (is_numeric($results_per_page)) {
            $this->results_per_page = $results_per_page;
        }
  
        // Store the original query.
        $this->original_query = trim($query);
  
        // Work out how many pages are needed.
        $this->set_number_of_pages();
  
        // Convert the query.
        $this->build_query();
  
    }
}

To work out how many pages we need, the function is very simple:

    private function set_number_of_pages() {
  
        $results = $this->db->query($this->original_query);
        $pages = $this->db->rows($results);
  
        if (is_numeric($pages) && $pages > 1) {
            $this->number_of_pages = ceil($pages / $this->results_per_page);
        }
  
    }

Now the tricky part. We have to manipulate the original query to create a paginated one, described by Jeff Smith. To do that, we need to set the query, save all the columns as variables (including the ORDER BY columns, if they're not already selected) and then add some of the variables to a repeat of the query.

Starting the query is straight-forward enough, since we can work out @a and @b. The IF ... ELSE statement is there to get around the "first page issue" that is mentioned in Jeff Smith's example.

    private function build_query() {
  
        // The paginated query.
        $paginated_query = 'DECLARE @a INT;' . "\n" . 
            'DECLARE @b INT;' . "\n" . 
            'SET @a = ' . (($this->page - 1) * $this->results_per_page) . "\n" . 
            'SET @b = ' . ($this->page * $this->results_per_page) . "\n" . 
            'IF @a < 1 SET ROWCOUNT @b' . "\n" . 
            'ELSE SET ROWCOUNT @a' . "\n";

# The rest of this function follows...
    }

We need to know where the columns are. Thanks to SQL being so structured, we can assume that the columns are between SELECT [DISTINCT] and FROM. We just need to identify these two parts to create a substring:

# Inside build_query()
        // Identify the start of the columns.
        $start = 0;
        if (strtoupper(substr($this->original_query, 0, 6)) !== "SELECT") {
            $this->db->error('Pagination only works on "SELECT" statements', NULL, $this->original_query);
        } else {
   
            // The start of a "SELECT ... FROM" statement.
            $start = 7;

            // The start of a "SELECT DISTINCT ... FROM" statement.
            if (strtoupper(substr($this->original_query, $start, 8)) === "DISTINCT") {
                $start += 9;
            }
   
        }
  
        // Identify the "FROM" location.
        $from_location = stripos($this->original_query, 'FROM');
        if ($from_location === false) {
            $this->db->error('Unable to find "FROM" in query', NULL, $this->original_query);
        }
  
        // Work out the columns.
        $column_string = substr($this->original_query, $start, $from_location - $start);
        $columns = explode(',', $column_string);

Having identified the columns, we can get them ready to be converted into MsSQL variables. It's worth noting that we need variables, so SELECT * will not work. We have to clean and MsSQL-variable-ify the columns a couple of times, so we create a new function to handle this.

    // Clean up the columns we found so we can create the MSSQL variables.
    // "I.ImageId" becomes "@IImageId" etc.
    // "MAX(Rating) AS Star" becomes "@Star".
    private function clean_attribute($attribute) {
        $attr = trim($attribute);
        if (strpos($attr, 'AS') !== false) {
            $attr = trim(array_pop(explode('AS', $attr)));
        }
        $attr = str_replace('.', '', $attr);
        return '@' . $attr;
    }


# Inside build_query()

        $mssql_variables = array();
  
        // Identify all the columns.
        foreach ($columns as $column) { 
  
            // Because we need to create variables out of the column names, we can not process "SELECT [DISTINCT] *".
            if (trim($column) === '*') {
                $this->db->error('Pagination cannot work with SELECTing "*". Please SELECT the columns you want.', NULL, $this->original_query);
            }
  
            $mssql_variables[trim($column)] = $this->clean_attribute($column);
        }
        unset($column);

The ORDER BY columns need a little more love. We need to check to see if they're in the list of variables we have (since we'll need them) and we need to keep a track of whether they're ascending or descending.

# Inside build_query()

        // If the "ORDER BY" column(s) are not in the variables, add them.
        $order_location = stripos($this->original_query, 'ORDER BY');
        if ($order_location === false) {
            $this->db->error('Paginated query must be ORDER\'d BY something.', NULL, $this->original_query);
        }
  
        $order_column_string = substr($this->original_query, $order_location + 9);
        $order_column = explode(',', $order_column_string);
        $order_info = array();
        foreach ($order_column as $column) {
   
            // Identify the parts of the ORDER columns.
            $column_parts = explode(' ', trim($column));
            $col = $column_parts[0];
            $asc = true;
            if (isset($column_parts[1]) && trim(strtoupper($column_parts[1])) === 'DESC') {
                $asc = false;
            }
            $attr = $this->clean_attribute($col);
   
            // Add to the mssql_variables, if necessary.
            if (!array_key_exists($col, $mssql_variables)) {
                $mssql_variables[$col] = $attr;
            }
   
            // Add information into the order_info array.
            array_push($order_info, array(
                'column' => $col,
                'variable' => $attr,
                'asc' => $asc
            ));
   
        }
        unset($column);

Now that we have a list of variables, we need to add them to the paginated query. As MsSQL is strongly typed, we need to type-set these variables as we create them. If you chose to use this class this is the part you will probably end up modifying heavily.

# Inside build_query()

        // Add the variables to the paginated query.
        foreach ($mssql_variables as $column => $attr) {
   
            $paginated_query .= 'DECLARE ' . $attr . ' ';
   
            // Attempt to work out the type of variable we should use.
            $lower_attr = strtolower($attr);
            $data_type = 'VARCHAR(500)';
            if (substr($lower_attr, -2) === 'id' || (strpos($lower_attr, 'count') !== false && strpos($lower_attr, 'country') === false)) {
                $data_type = 'INT';
            } else if (strpos($lower_attr, 'created') !== false || strpos($lower_attr, 'date') !== false) {
                $data_type = 'DATETIME';
            }
   
            $paginated_query .= $data_type . ';' . "\n";
   
        }
        unset($column, $attr);

Our paginated query now has our variables declared, we need to start putting in the first iteration of the original query. All the columns have to be assigned to the variables we're creating (replacing the SELECTs) but the rest of the query is the same. Since sometimes columns are aliased, we create a function to remove the alias.

    // Some columns are aliased. This function removes the aliasing for the variables.
    private function clean_column($column) {

        $column_parts = explode('AS', $column);
        if (count($column_parts) > 1) {
            array_pop($column_parts);
        }
        return implode('AS', $column_parts);
  
    }


# Inside build_query()

        // Add the "SELECT [DISTINCT]" to the query.
        $paginated_query .= substr($this->original_query, 0, $start) . "\n";
  
        // Add each of the columns as variables.
        $new_columns = array();
        foreach ($mssql_variables as $column => $attr) {
            array_push($new_columns, $attr . ' = ' . $this->clean_column($column));
        }
        $paginated_query .= implode(', ', $new_columns) . "\n";
  
        // Add the rest of the query.
        $paginated_query .= substr($this->original_query, $from_location) . "\n";

Now we add some of the statement's processing. I never understood why Jeff used set @b = 1 + @b - @a, I thought the 1 + was giving me the wrong results, so I've left it out.

# Inside build_query()
        // Add the pagination processing to the query.
        $paginated_query .= 'SET @b = @b - @a' . "\n";
        $paginated_query .= 'SET ROWCOUNT @b' . "\n";

It's time to add the second iteration of the original query. This is identical to the original query, except an extra caveat that we need to add to all the pages (except the first one). This needs to be added just before the ORDER BY.

# Inside build_query()

        // Add the original query again, up til the WHERE (if there is one).
        $where_location = stripos($this->original_query, 'WHERE');
        $paginated_query .= substr($this->original_query, 0, $order_location) . "\n";
  
        // Add (or add to) the WHERE statement, but not on the first page.
        if ($this->page > 1) {
            $paginated_query .= ($where_location === false ? 'WHERE ' : 'AND ');
            $paginated_orders = array();
            foreach ($order_info as $order) {
                array_push($paginated_orders, $order['column'] . ($order['asc'] ? ' > ' : ' < ') . $order['variable']);
            }
            unset($order);
            $paginated_query .= implode(' AND ', $paginated_orders) . "\n";
        }
  
        // Add the ORDER BY.
        $paginated_query .= substr($this->original_query, $order_location) . "\n";

Jeff Smith finished his paginated query with a little house keeping. In honour of his hard work, I'll add his final line to this paginated query:

# Inside build_query()

        // Add a ROWCOUNT cleaning line.
        $paginated_query .= 'SET ROWCOUNT 0';
  
        // Store the final paginated query.
        $this->paginated_query = $paginated_query;

# End of build_query()

This looks like a lot of work, but we now have a class that can take a regular MsSQL statement and paginate it in MsSQL 2000! You're free to use it, but I don't have access to an MsSQL server (other than my client's) so I can't offer any kind of support. I wish you luck with your queries.

Here's the complete paginate class:

class paginate {
 
    private $original_query;
    private $results_per_page = 20;
    private $page = 1;
    private $get_page;
    private $number_of_pages = 1;
    private $db;
    private $paginated_query;
 
    function __construct($query, $results_per_page = 20, $get_page = 'currentpage') {
  
        global $db;
        $this->db = $db;
  
        // Work out which page we're on.
        if (isset($_GET[$get_page]) && is_numeric($_GET[$get_page])) {
            $this->page = (int)$_GET[$get_page];
        }

        // Store the number of results per page.
        if (is_numeric($results_per_page)) {
            $this->results_per_page = $results_per_page;
        }
  
        // Store the original query.
        $this->original_query = trim($query);
  
        // Work out how many pages are needed.
        $this->set_number_of_pages();
  
        // Convert the query.
        $this->build_query();
  
    }
 
    private function set_number_of_pages() {
  
        $results = $this->db->query($this->original_query);
        $pages = $this->db->rows($results);
  
        if (is_numeric($pages) && $pages > 1) {
            $this->number_of_pages = $pages;
        }
  
    }
 
    // Clean up the columns we found so we can create the MSSQL variables.
    // "I.ImageId" becomes "@IImageId" etc.
    // "MAX(Rating) AS Star" becomes "@Star".
    private function clean_attribute($attribute) {
        $attr = trim($attribute);
        if (strpos($attr, 'AS') !== false) {
            $attr = trim(array_pop(explode('AS', $attr)));
        }
        $attr = str_replace('.', '', $attr);
        return '@' . $attr;
    }
 
    // Some columns are aliased. This function removes the aliasing for the variables.
    private function clean_column($column) {

        $column_parts = explode('AS', $column);
        if (count($column_parts) > 1) {
            array_pop($column_parts);
        }
        return implode('AS', $column_parts);
  
    }
 
    private function build_query() {
  
        // The paginated query.
        $paginated_query = 'DECLARE @a INT;' . "\n" . 
            'DECLARE @b INT;' . "\n" . 
            'SET @a = ' . (($this->page - 1) * $this->results_per_page) . "\n" . 
            'SET @b = ' . ($this->page * $this->results_per_page) . "\n" . 
            'IF @a < 1 SET ROWCOUNT @b' . "\n" . 
            'ELSE SET ROWCOUNT @a' . "\n";
  
        // Identify the start of the columns.
        $start = 0;
        if (strtoupper(substr($this->original_query, 0, 6)) !== "SELECT") {
            $this->db->error('Pagination only works on "SELECT" statements', NULL, $this->original_query);
        } else {
   
            // The start of a "SELECT ... FROM" statement.
            $start = 7;

            // The start of a "SELECT DISTINCT ... FROM" statement.
            if (strtoupper(substr($this->original_query, $start, 8)) === "DISTINCT") {
                $start += 9;
            }
   
        }
  
        // Identify the "FROM" location.
        $from_location = stripos($this->original_query, 'FROM');
        if ($from_location === false) {
            $this->db->error('Unable to find "FROM" in query', NULL, $this->original_query);
        }
  
        // Work out the columns.
        $column_string = substr($this->original_query, $start, $from_location - $start);
        $columns = explode(',', $column_string);
  
        $mssql_variables = array();
  
        // Identify all the columns.
        foreach ($columns as $column) { 
  
            // Because we need to create variables out of the column names, we can not process "SELECT [DISTINCT] *".
            if (trim($column) === '*') {
                $this->db->error('Pagination cannot work with SELECTing "*". Please SELECT the columns you want.', NULL, $this->original_query);
            }
  
            $mssql_variables[trim($column)] = $this->clean_attribute($column);
        }
        unset($column);
  
        // If the "ORDER BY" column(s) are not in the variables, add them.
        $order_location = stripos($this->original_query, 'ORDER BY');
        if ($order_location === false) {
            $this->db->error('Paginated query must be ORDER\'d BY something.', NULL, $this->original_query);
        }
  
        $order_column_string = substr($this->original_query, $order_location + 9);
        $order_column = explode(',', $order_column_string);
        $order_info = array();
        foreach ($order_column as $column) {
   
            // Identify the parts of the ORDER columns.
            $column_parts = explode(' ', trim($column));
            $col = $column_parts[0];
            $asc = true;
            if (isset($column_parts[1]) && trim(strtoupper($column_parts[1])) === 'DESC') {
                $asc = false;
            }
            $attr = $this->clean_attribute($col);
   
            // Add to the mssql_variables, if necessary.
            if (!array_key_exists($col, $mssql_variables)) {
                $mssql_variables[$col] = $attr;
            }
   
            // Add information into the order_info array.
            array_push($order_info, array(
                'column' => $col,
                'variable' => $attr,
                'asc' => $asc
            ));
   
        }
        unset($column);
  
  
        // Add the variables to the paginated query.
        foreach ($mssql_variables as $column => $attr) {
   
            $paginated_query .= 'DECLARE ' . $attr . ' ';
   
            // Attempt to work out the type of variable we should use.
            $lower_attr = strtolower($attr);
            $data_type = 'VARCHAR(500)';
            if (substr($lower_attr, -2) === 'id' || (strpos($lower_attr, 'count') !== false && strpos($lower_attr, 'country') === false)) {
                $data_type = 'INT';
            } else if (strpos($lower_attr, 'created') !== false || strpos($lower_attr, 'date') !== false) {
                $data_type = 'DATETIME';
            }
   
            $paginated_query .= $data_type . ';' . "\n";
   
        }
        unset($column, $attr);
  
        // Add the "SELECT [DISTINCT]" to the query.
        $paginated_query .= substr($this->original_query, 0, $start) . "\n";
  
        // Add each of the columns as variables.
        $new_columns = array();
        foreach ($mssql_variables as $column => $attr) {
            array_push($new_columns, $attr . ' = ' . $this->clean_column($column));
        }
        $paginated_query .= implode(', ', $new_columns) . "\n";
  
        // Add the rest of the query.
        $paginated_query .= substr($this->original_query, $from_location) . "\n";
  
        // Add the pagination processing to the query.
        $paginated_query .= 'SET @b = @b - @a' . "\n";
        $paginated_query .= 'SET ROWCOUNT @b' . "\n";
  
        // Add the original query again, up til the WHERE (if there is one).
        $where_location = stripos($this->original_query, 'WHERE');
        $paginated_query .= substr($this->original_query, 0, $order_location) . "\n";
  
        // Add (or add to) the WHERE statement, but not on the first page.
        if ($this->page > 1) {
            $paginated_query .= ($where_location === false ? 'WHERE ' : 'AND ');
            $paginated_orders = array();
            foreach ($order_info as $order) {
                array_push($paginated_orders, $order['column'] . ($order['asc'] ? ' > ' : ' < ') . $order['variable']);
            }
            unset($order);
            $paginated_query .= implode(' AND ', $paginated_orders) . "\n";
        }
  
        // Add the ORDER BY.
        $paginated_query .= substr($this->original_query, $order_location) . "\n";
  
        // Add a ROWCOUNT cleaning line.
        $paginated_query .= 'SET ROWCOUNT 0';
  
        // Store the final paginated query.
        $this->paginated_query = $paginated_query;
  
    }
 
}