Paginator for raw query

[iivanov2]

I'd like to share a class which could add to yours and which will allow raw queries to be paginated. This feature is missing currently but it is necessarily. You could add it as new method to MysqliDb or as separated class. You could polish it if you think that it's not perfect. This is my class with simple example:

class rawPaginator
{
    private $preparedParams=array();
    private $rawQuery;
    private $DB;
    private $paginatorParams=array(
    'page'=>1, //which page to show
    'perpage'=>12, //max number of items on each page 
    'delta'=>5 //how much prev and next links around current page
    );
    public $links=array();
    public $totalRows;
    public $totalPages;
    public $page;

    public function __construct($aDB, $aRawQuery, $aPaginatorParams=array(), $aPreparedParams=array())
    {
        $this->rawQuery=$aRawQuery;
        $this->DB=$aDB;
        $this->preparedParams=$aPreparedParams;
        foreach($aPaginatorParams as $key=>$value)
        {
            $this->paginatorParams[$key]=$value;
        }
        if($this->paginatorParams['page']<1 or !is_numeric($this->paginatorParams['page']))
        {
            $this->paginatorParams['page']=1;
        }
        if($this->paginatorParams['perpage']<1 or !is_numeric($this->paginatorParams['perpage']))
        {
            $this->paginatorParams['perpage']=1;
        }       
        if(!is_numeric($this->paginatorParams['delta']))
        {
            $this->paginatorParams['delta']=5;
        }       
        $this->totalRows();
        $this->Page();
    }   

    public function Page()
    {
        $curPage=$this->paginatorParams['page'];
        $perpage=$this->paginatorParams['perpage'];
        $totalPages=$this->totalPages=ceil($this->totalRows/$perpage);
        $offset=($curPage-1)*$perpage;
        $query=$this->rawQuery." LIMIT $offset, $perpage ";
        $result=$this->DB->rawQuery($query,$this->preparedParams);
        $delta=$this->paginatorParams['delta'];

        //generate links
        if($curPage-$delta>1)
        {
            array_push($this->links, array('number'=>'1','type'=>'gotofirst')); //generate link to first page
        }

        $prevLinksArr=array();
        for($i=1;$i<=$delta;$i++) //generate few back links
        {
            $prevNum=$curPage-$i;
            if($prevNum<1)
            {
                break;
            }
            array_push($prevLinksArr, array('number'=>$prevNum,'type'=>'delta'));
        }
        $this->links=array_merge($this->links,array_reverse($prevLinksArr));

        array_push($this->links, array('type'=>'curpage','number'=>$curPage)); //generate link for current page

        for($i=1;$i<=$delta;$i++) //generate few prev links
        {
            $nextNum=$curPage+$i;
            if($nextNum>$this->totalPages)
            {
                break;
            }
            array_push($this->links, array('number'=>$nextNum,'type'=>'delta'));

        }       

        if($curPage+$delta<$totalPages)
        {
            array_push($this->links,  array('number'=>$totalPages,'type'=>'gotolast')); //generate link to last page
        }       
        //END generate links

        $this->page=$result;
    }

    private function totalRows()
    {
        $this->totalRows=0;
        $mod1 = preg_replace('/SELECT.+(FROM )/si', 'SELECT COUNT(*) FROM ', $this->rawQuery); 
        $result=$this->DB->rawQuery($mod1,$this->preparedParams);
        if(count($result)>0)
        {
            $this->totalRows=count($result);
        }
        return $this->totalRows;
    }

    public function getFormatedLinks() //bootstrap 3 suitable
    {
        $full_URL=$_SERVER['REQUEST_URI'];
        $parsedUrl = parse_url($full_URL);
        if(!array_key_exists("path",$parsedUrl))
        {
            $parsedUrl["path"]="";
        }       
        if(!array_key_exists("query",$parsedUrl))
        {
            $parsedUrl["query"]="";
        }       
        $parsedUrl["query"]=preg_replace('/page=[0-9]*/i', '', $parsedUrl["query"]); //remove page=[0-9]* if exists

        $HTML='<ul class="pagination">' . PHP_EOL;
        foreach($this->links as $value)
        {   
            if($value['type']!="curpage")
            {
                $HTML.='<li>' . PHP_EOL;
            }
            else
            {
                $HTML.='<li class="active">' . PHP_EOL;
            }
            $url=$parsedUrl["path"].'?page='.$value['number'];
            if($parsedUrl["query"]!='')
            {
                $url.='&'.$parsedUrl["query"];
            }
            $url=preg_replace('/&+/', '&', $url);
            $url=preg_replace('/&$/', '', $url);
            if(isset($_COOKIE['anchor']))
            {
                $url.='#'.$_COOKIE['anchor']; //attach anchor if there is one
            }   
            if($value['type']!="gotofirst" && $value['type']!="gotolast")
            {
                $HTML.='<a href="'.$url.'">'.$value['number'].'</a>' . PHP_EOL;
            }
            elseif($value['type']=="gotofirst")
            {
                $HTML.='<a href="'.$url.'">|&lt;</a>' . PHP_EOL;
            }
            else //case $value['type']=="gotolast"
            {
                $HTML.='<a href="'.$url.'">&gt;|</a>' . PHP_EOL;
            }           
            $HTML.='</li>' . PHP_EOL;
        }
        $HTML.='</ul>' . PHP_EOL;
        return $HTML;
    }
}

$page=2;
$perpage=25;

$Paginator = new rawPaginator($db,$rawQuery,array('page'=>$page, 'perpage'=>$perpage, 'delta'=>3),$queryBindParams);

$searchResults=$Paginator->page;
$totalItems=$Paginator->totalRows;
$totalPages=$Paginator->totalPages;
$links=$Paginator->getFormatedLinks();
echo $twig->render('search.twig', array('searchResults'=>$searchResults, 'totalItems'=>$totalItems, 'totalPages'=>$totalPages, 'links'=>$links));

[Faraz543]

Can I skip the "$queryBindParams" in the code and make that query run without it?