<!-- Collection of code snippets by Arne Vajhøj -->
<!-- posted to eksperten.dk, usenet and other places (2002-now) -->
<?php
/**
 * DataFetcher interface.
 *
 * Note: errors should throw exceptions.
 */
interface DataFetcher {
    /**
     * Execute query.
     * @param integer $offset row offset (0 based)
     * @param integer $numrows number of rows
     */
    public function query($offset, $numrows);
    /**
     * Get row.
     * @return array row as associative array with field name as key and field value as value (null if no row available)
     */
    public function getRow();
    /**
     * Close connection.
     */
    public function close();
}

/**
 * DataFetcher class for mysqli extension.
 * 
 * Note: parameters is not supported due to API problems.
 */
class MysqliDataFetcher implements DataFetcher {
    private $con;
    private $stmt;
    private $rs;
    private $sql;
    /**
     * Constructor.
     * @param string $host server
     * @param string $un username
     * @param string $pw password
     * @param string $db database
     * @param string $sql base SQL statement (will be modified before execution)
     * @throws Exception
     */
    public function __construct($host, $un, $pw, $db, $sql) {
        $this->con = new mysqli($host, $un, $pw, $db);  
        if(mysqli_connect_errno()) throw new Exception(mysqli_connect_error());
        $stmt = null;
        $rs = null;
        $this->sql = $sql;
    }
    public function query($offset, $numrows) {
        if($this->rs != null) $this->rs->free();    
        if($this->stmt != null) $this->stmt->close();   
        if($this->stmt = $this->con->prepare("$this->sql LIMIT $offset,$numrows")) {
            $this->stmt->execute();
            $this->rs = $this->stmt->get_result();
        } else {
            throw new Exception($this->con->error);
        }
    }
    public function getRow() {
        return $this->rs->fetch_array(MYSQLI_ASSOC);    
    }
    public function close() {
        if($this->rs != null) $this->rs->free();    
        if($this->stmt != null) $this->stmt->close();   
        $this->con->close();    
    }
}

/**
 * DataFetcher class for PDO.
 */
abstract class PDODataFetcher implements DataFetcher {
    private $con;
    private $stmt;
    private $sql;
    private $params;
    /**
     * Constructor.
     * @param string $constr connection string
     * @param string $un username
     * @param string $pw password
     * @param string $sql base SQL statement (will be modified before execution)
     * @param array $params parameters for SQL statement
     * @throws Exception
     */
    public function __construct($constr, $un, $pw, $sql, $params) {
        $this->con = new PDO($constr, $un, $pw);
        $this->con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt = null;
        $rs = null;
        $this->sql = $sql;
        $this->params = $params;
    }
    public function query($offset, $numrows) {
        if($this->stmt != null) $this->stmt->closeCursor();
        $this->stmt = $this->con->prepare($this->convertSQL($this->sql, $offset, $numrows));
        $this->stmt->execute($this->params);
    }
    public function getRow() {
        return $this->stmt->fetch(PDO::FETCH_ASSOC);
    }
    public function close() {
        if($this->stmt != null) $this->stmt->closeCursor();
    }
    /**
     * Convert SQL for pagination.
     * @param string $sql base SQL statement 
     * @param integer $offset row offset (0 based)
     * @param integer $numrows number of rows
     * @return string modified SQL statement
     */
    protected abstract function convertSQL($sql, $offset, $numrows);
}

/**
 * DataFetcher class for PDO with MySQL.
 */
class PDOmysqlDataFetcher extends PDODataFetcher {
    /**
     * Constructor.
     * @param string $host server
     * @param string $un username
     * @param string $pw password
     * @param string $db database
     * @param string $sql base SQL statement (will be modified before execution)
     * @param array $params parameters for SQL statement
     * @throws Exception
     */
    public function __construct($host, $un, $pw, $db, $sql, $params) {
        parent::__construct("mysql:host=$host;dbname=$db", $un, $pw, $sql, $params);
    }
    protected function convertSQL($sql, $offset, $numrows) {
        return "$sql LIMIT $offset,$numrows";
    }
}

/**
 * DataFetcher class for PDO with SQLServer.
 */
class PDOsqlsrvDataFetcher extends PDODataFetcher {
    /**
     * Constructor.
     * @param string $host server
     * @param string $un username
     * @param string $pw password
     * @param string $db database
     * @param string $sql base SQL statement (will be modified before execution)
     * @param array $params parameters for SQL statement
     * @throws Exception
     */
    public function __construct($host, $un, $pw, $db, $sql, $params) {
        parent::__construct("sqlsrv:server=$host;database=$db", $un, $pw, $sql, $params);
    }
    protected function convertSQL($sql, $offset, $numrows) {
        return "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,x.* FROM ($sql) x) y WHERE rownum BETWEEN ($offset+1) AND ($offset+$numrows)";
    }
}

/**
 * Data paginator class.
 */
class DataPaginator {
    private $df;
    private $pgsiz;
    private $first;
    private $last;
    private $data;
    private $fnd1more;
    private function update() {
        $this->df->query($this->first - 1, $this->last - $this->first + 2);
        $this->data = array();
        while((count($this->data) < $this->pgsiz) && ($row = $this->df->getRow())) {
            $this->data[] = $row;
        }
        $extrarow = $this->df->getRow();
        $this->fnd1more = is_array($extrarow);
    }
    /**
     * Constructor.
     * @param object $df data fetcher
     * @param integer $pgsiz page size
     * @param integer $start start row number (1 based)
     */
    public function __construct($df, $pgsiz, $start = 1) {
        $this->df = $df;
        $this->pgsiz = $pgsiz;
        $this->first = $start;
        $this->last = $this->first + $this->pgsiz - 1;
        $this->update();
    }
    /**
     * Get data.
     * @return array indexed array where each element is a row and row is an associative array where each element is a field with field name as index and field value as value
     */
    public function getData() {
        return $this->data;
    }
    /**
     * Get first row of current page.
     * @return integer row number (1 based)
     */
    public function getFirst() {
        return $this->first;
    }
    /**
     * Get last row of current page.
     * @return integer row number (1 based)
     */
    public function getLast() {
        return min($this->last, $this->first + count($this->data) - 1);
    }
    /**
     * Get page size.
     * @return integer page size
     */
    public function getPageSize() {
        return $this->pgsiz;
    }
    /**
     * Does previous page exist.
     * @return boolean
     */
    public function hasPrev() {
        return $this->first > 1;    
    }
    /**
     * Does next page exist.
     * @return boolean
     */
    public function hasNext() {
        return $this->fnd1more; 
    }
    /**
     * Go to next page.
     */
    public function next() {
        $this->first += $this->pgsiz;   
        $this->last = $this->first + $this->pgsiz - 1;
        $this->update();    
    }
    /**
     * Go to previous page.
     */
    public function prev() {
        $this->first -= $this->pgsiz;
        $this->last = $this->first + $this->pgsiz - 1;
        $this->update();
    }
}

/**
 * Visual paginator class.
 */
abstract class VisualPaginator {
    private $dp;
    private $url;
    /**
     * Constructor.
     * @param object $dp data paginator
     * @param string $url URL to use for previous/next links (usually same URL as current page)
     */
    public function __construct($dp, $url) {
        $this->dp = $dp;
        $this->url = $url;
    }
    /**
     * Display paginated data.
     */
    public function display() {
        $this->displayHeader($this->dp->getFirst(), $this->dp->getLast());
        foreach($this->dp->getData() as $row) {
            $this->displayRow($row);
        }
        $this->displayFooter($this->url, $this->dp->hasPrev(), $this->dp->getFirst(), $this->dp->hasNext(), $this->dp->getLast(), $this->dp->getPageSize());
    }
    /**
     * Display header.
     * @param integer $first first row number on page (1 based)
     * @param integer $last last row number on page (1 based)
     */
    protected abstract function displayHeader($first, $last);
    /**
     * Display single row.
     * @param array $row associative array where each element is a field with field name as index and field value as value
     */
    protected abstract function displayRow($row);
    /**
     * Display footer.
     * @param string $url URL to use for previous/next links (usually same URL as current page)
     * @param boolean $hasprev does previous page exist
     * @param integer $first first row number on page (1 based)
     * @param boolean $hasnext does next page exist
     * @param integer $last last row number on page (1 based)
     * @param integer $pgsiz page size
     */
    protected abstract function displayFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz);
}

/**
 * Table visual paginator class (display data in HTML table).
 */
abstract class TableVisualPaginator extends VisualPaginator {
    private $desc;
    private $colhdr;
    private function spacer($attr) {
        return (strlen($attr) > 0) ? (' ' . $attr) : '';
    }
    /**
     * Constructor.
     * @param object $dp data paginator
     * @param string $url URL to use for previous/next links (usually same URL as current page)
     * @param string $desc descriptive text used in header
     * @param array $colhdr array with column header names
     */
    public function __construct($dp, $url, $desc, $colhdr) {
        parent::__construct($dp, $url);
        $this->desc = $desc;
        $this->colhdr = $colhdr;
    }
    protected function displayHeader($first, $last) {
        echo $this->getHeader($this->desc, $first, $last);
        echo "<table" . $this->spacer($this->getTableAttr()) . ">\r\n";
        echo "<tr" . $this->spacer($this->getTrAttr()) . ">\r\n";
        foreach($this->colhdr as $ch) {
            echo "<th" . $this->spacer($this->getThAttr()) . ">$ch</th>\r\n";
        }
        echo "</tr>\r\n";
    }
    protected function displayRow($row) {
        echo "<tr" . $this->spacer($this->getTrAttr()) . ">\r\n";
        foreach($row as $f => $v) {
            echo "<td" . $this->spacer($this->getTdAttr()) . ">$v</td>\r\n";
        }
        foreach($this->getExtraCols() as $cb) {
            echo "<td" . $this->spacer($this->getTdAttr()) . ">" . call_user_func($cb, $row) . "</td>\r\n";
        }
        echo "</tr>\r\n";
    }
    protected function displayFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz) {
        echo "</table>\r\n";
        echo $this->getFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz);
    }
    /**
     * Get header string.
     * @param string $desc descriptive text
     * @param integer $first first row number on page (1 based)
     * @param integer $last last row number on page (1 based)
     * @return string header
     */
    protected abstract function getHeader($desc, $first, $last);
    /**
     * Get footer string.
     * @param string $url URL to use for previous/next links (usually same URL as current page)
     * @param boolean $hasprev does previous page exist
     * @param integer $first first row number on page (1 based)
     * @param boolean $hasnext does next page exist
     * @param integer $last last row number on page (1 based)
     * @param integer $pgsiz page size
     * @return footer
     */
    protected abstract function getFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz);
    /**
     * Get attributes on table tag.
     * @return string attributes
     */
    protected abstract function getTableAttr();
    /**
     * Get attributes on tr tag.
     * @return string attributes
     */
    protected abstract function getTrAttr();
    /**
     * Get attributes on th tag.
     * @return string attributes
     */
    protected abstract function getThAttr();
    /**
     * Get attributes on td tag.
     * @return string attributes
     */
    protected abstract function getTdAttr();
    /**
     * Get extra columns to add.
     * @return array array with column values
     */
    protected abstract function getExtraCols();
}

/**
 * List visual paginator class (display data in HTML list).
 */
abstract class ListVisualPaginator extends VisualPaginator {
    private $desc;
    private function spacer($attr) {
        return (strlen($attr) > 0) ? (' ' . $attr) : '';
    }
    /**
     * Constructor.
     * @param object $dp data paginator
     * @param string $url URL to use for previous/next links (usually same URL as current page)
     * @param string $desc descriptive text used in header
     */
    public function __construct($dp, $url, $desc) {
        parent::__construct($dp, $url);
        $this->desc = $desc;
    }
    protected function displayHeader($first, $last) {
        echo $this->getHeader($this->desc, $first, $last);
        echo "<ul" . $this->spacer($this->getUlAttr()) . ">\r\n";
    }
    protected function displayRow($row) {
        echo "<li" . $this->spacer($this->getLiAttr()) . ">\r\n";
        echo $this->getBody($row);
        echo "</li>\r\n";
    }
    protected function displayFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz) {
        echo "</ul>\r\n";
        echo $this->getFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz);
    }
    /**
     * Get header string.
     * @param string $desc descriptive text
     * @param integer $first first row number on page (1 based)
     * @param integer $last last row number on page (1 based)
     * @return string header
     */
    protected abstract function getHeader($desc, $first, $last);
    /**
     * Get footer string.
     * @param string $url URL to use for previous/next links (usually same URL as current page)
     * @param boolean $hasprev does previous page exist
     * @param integer $first first row number on page (1 based)
     * @param boolean $hasnext does next page exist
     * @param integer $last last row number on page (1 based)
     * @param integer $pgsiz page size
     * @return footer
     */
    protected abstract function getFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz);
    /**
     * Get attributes on ul tag.
     * @return string attributes
     */
    protected abstract function getUlAttr();
    /**
     * Get attributes on li tag.
     * @return string attributes
     */
    protected abstract function getLiAttr();
    /**
     * Get representation of one row.
     * @param array $row associative array where each element is a field with field name as index and field value as value
     */
    protected abstract function getBody($row);
}

/**
 * Standard header and footer trait.
 */
trait StandardHeaderFooter {
    /**
     * Get header string.
     * @param string $desc descriptive text
     * @param integer $first first row number on page (1 based)
     * @param integer $last last row number on page (1 based)
     * @return string header
     */
    public function getHeader($desc, $first, $last) {
        return "$desc $first - $last:\r\n";
    }   
    private function getUrl($url, $start) {
        return "$url?start=$start";
    }
    private function getLink($text, $url, $start) {
        return "<a href='" . $this->getUrl($url, $start) . "'>$text</a>";
    }
    /**
     * Get footer string.
     * @param string $url URL to use for previous/next links (usually same URL as current page)
     * @param boolean $hasprev does previous page exist
     * @param integer $first first row number on page (1 based)
     * @param boolean $hasnext does next page exist
     * @param integer $last last row number on page (1 based)
     * @param integer $pgsiz page size
     * @return footer
     */
    public function getFooter($url, $hasprev, $first, $hasnext, $last, $pgsiz) {
        return  ($hasprev ? $this->getLink('Previous', $url, $first - $pgsiz) : '') . ' ' . ($hasnext ? $this->getLink('Next', $url, $last + 1) : '') . "\r\n";
    }
}

/**
 * Simple table visual paginator class (just a plain table with no extras). 
 */
class SimpleTableVisualPaginator extends TableVisualPaginator {
    use StandardHeaderFooter;
    /**
     * Constructor.
     * @param object $dp data paginator
     * @param string $url URL to use for previous/next links (usually same URL as current page)
     * @param string $desc descriptive text used in header
     * @param array $colhdr array with column header names
     */
    public function __construct($dp, $url, $desc, $colhdr) {
        parent::__construct($dp, $url, $desc, $colhdr);
    }
    protected function getTableAttr() {
        return "border='1'";    
    }
    protected function getTrAttr() {
        return '';
    }
    protected function getThAttr() {
        return '';
    }
    protected function getTdAttr() {
        return '';
    }
    protected function getExtraCols() {
        return array();
    }
}

/**
 * Simple list visual paginator class (just a plain list with no extras). 
 */
class SimpleListVisualPaginator extends ListVisualPaginator {
    use StandardHeaderFooter;
    public function __construct($dp, $url, $desc) {
        parent::__construct($dp, $url, $desc);
    }
    protected function getUlAttr() {
        return '';
    }
    protected function getLiAttr() {
        return '';
    }
    protected function getBody($row) {
        $res = '';
        foreach($row as $f => $v) {
            $res .= "$v\r\n";
        }
        return $res;
    }
}

define('NOFLD','');
define('NOFUNC', null);

/**
 * Advanced table visual paginator class (table with classes on tags and extra columns with edit and delete links).
 */
class AdvancedTableVisualPaginator extends TableVisualPaginator {
    use StandardHeaderFooter;
    private $clz;
    private $idfld;
    private $editurl;
    private $delurl;
    private function getClassAttr($elm) {
        if(array_key_exists($elm, $this->clz)) {
            return "class='" . $this->clz[$elm] . "'";  
        } else {
            return '';
        }
    }
    /**
     * Constructor.
     * @param object $dp data paginator
     * @param string $url URL to use for previous/next links (usually same URL as current page)
     * @param string $desc descriptive text used in header
     * @param array $colhdr array with column header names
     * @param array $clz associative array where each element is a class attribute with tag name as index and class name as value
     * @param string $idfld name of field to use as is for edit and delete
     * @param string $editurl URL to use for edit links
     * @param string $editurl URL to use for delete links
     */
    public function __construct($dp, $url, $desc, $colhdr, $clz, $idfld = NOFLD, $editurl = NOFUNC, $delurl = NOFUNC) {
        parent::__construct($dp, $url, $desc, $colhdr);
        $this->clz = $clz;
        $this->idfld = $idfld;
        $this->editurl = $editurl;
        $this->delurl = $delurl;
    }
    protected function getTableAttr() {
        $clzattr = $this->getClassAttr('table');
        if(strlen($clzattr) > 0) {
            return "border='1' $clzattr";
        } else {
            return "border='1'";
        }
    }
    protected function getTrAttr() {
        return $this->getClassAttr('tr');
    }
    protected function getThAttr() {
        return $this->getClassAttr('th');
    }
    protected function getTdAttr() {
        return $this->getClassAttr('td');
    }
    protected function getExtraCols() {
        $res = array();
        if(($this->idfld != NOFLD) && ($this->editurl != NOFUNC)) {
            $res[] = array($this, 'getEditLink');
        }
        if(($this->idfld != NOFLD) && ($this->delurl != NOFUNC)) {
            $res[] = array($this, 'getDelLink');
        }
        return $res;
    }
    protected function getEditLink($row) {
        return "<a href='$this->editurl?$this->idfld=" . $row[$this->idfld] . "'>Edit</a>";
    }
    protected function getDelLink($row) {
        return "<a href='$this->delurl?$this->idfld=" . $row[$this->idfld] . "'>Delete</a>";
    }
}

/**
 * Advanced list visual paginator class (list with classes on tags and data formatted via template).
 */
class AdvancedListVisualPaginator extends ListVisualPaginator {
    use StandardHeaderFooter;
    private $clz;
    private $tmpl;
    private function getClassAttr($elm) {
        if(array_key_exists($elm, $this->clz)) {
            return "class='" . $this->clz[$elm] . "'";  
        } else {
            return '';
        }
    }
    /**
     * Constructor.
     * @param object $dp data paginator
     * @param string $url URL to use for previous/next links (usually same URL as current page)
     * @param string $desc descriptive text used in header
     * @param array $colhdr array with column header names
     * @param array $clz associative array where each element is a class attribute with tag name as index and class name as value
     * @param strint $tmpl template for displaying a row (colon plus field name get replaced with field value)
     */
    public function __construct($dp, $url, $desc, $clz, $tmpl) {
        parent::__construct($dp, $url, $desc);
        $this->clz = $clz;
        $this->tmpl = $tmpl;
    }
    protected function getUlAttr() {
        return $this->getClassAttr('ul');
    }
    protected function getLiAttr() {
        return $this->getClassAttr('li');
    }
    protected function getBody($row) {
        $res = $this->tmpl;
        foreach($row as $f => $v) {
            $res = str_replace(':' . $f, $v, $res);
        }
        return $res;
    }
}
?>