issue limiting my sql query with pagination class

Question

<?php
/****************************************************************************
* paging.class.php v 1.0
*
* version 1.0
*
* This script allow to generate dynamically navigation of pages on your web site
* It is easy to configure and use this paging class
*
* ----------------------------------------------------------------
*
* You can find examples in the index.php script (with this package)
*

Copyright (C) 2010 Deyan Spasov <[email protected]>

*******************************************************************************/

class paging {

    public $showPagesNumber = true;     //Show page number     Example: Page 1 of 20
    public $showPagesForm = true;           //If you want to use pages form. The system show form with input field and go button
    public $showFirstAndLast = true;    //If you have paging buttons first page and last page
    public $showPrevAndNext = true;     //If you have paging button previous page and next page
    public $numberOfPages = 7;              //Set the number of shown pages

    public $pagingClass = 'paging';             //Set css class of your paging
    public $pagingFirstText = '&laquo;';    //Set text of first button
    public $pagingLastText = '&raquo;';     //Set text of last button
    public $pagingPrevText = '&lsaquo;';    //Set text of previous button
    public $pagingNextText = '&rsaquo;';    //Set text of next button
    public $pagingPageText = 'Page';            //Set page text
    public $pagingPageOfText = 'of';            //Set of text
    public $pagingFormButtonText = 'Go';    //Set text of form button


    /*
        Generate standart navigation
        example: generate('?page=', '&category=1', 5, 10, 100)      
        result:  « ‹ 1 2 3 4 5 6 7 8 9 10 › »

        @access public

        @parameters
            $frontUrl - Set the url before page number
            $backUrl  - Set the url after page number
            $currentPage - Set the current page number
            $rowsPerPage - Set how many rows you show on page
            $allRows  - Set the number of all rows that you have

        @return string with paging HTML source code
    */
    public function generate($frontUrl, $backUrl, $currentPage, $rowsPerPage, $allRows) {

        if($allRows <= $rowsPerPage) {
            return '';
        }

        $pages = ceil ( $allRows / $rowsPerPage );

        settype($currentPage, "int");

        if($currentPage < 1 || $currentPage > $pages) {
            $currentPage = 1;
        }

        $paging = '<div class="'.$this->pagingClass.'">';

        if ($currentPage > 2 && $this->showFirstAndLast) {
            $paging .= '<a href="' . $frontUrl . '1' . $backUrl . '" title="First page" class="arrows">'.$this->pagingFirstText.'</a>';
        }

        if ($currentPage > 1 && $this->showPrevAndNext) {
            $paging .= '<a href="' . $frontUrl . '' . ($currentPage - 1) . '' . $backUrl . '" title="Previous page" class="arrows">'.$this->pagingPrevText.'</a>';
        }

        $halfPages = $this->numberOfPages / 2;
        settype($halfPages, 'int');

        if($pages > $this->numberOfPages) {
            if($currentPage == 1) {
                for($i = 1; $i <= $this->numberOfPages; $i ++)
                    $paging .= '<a href="' . $frontUrl . '' . $i . '' . $backUrl . '" ' . (($i == $currentPage) ? ' class="selected"' : ' class="normal"') . '>' . $i . '</a>';
            } elseif ($currentPage == $pages) {
                for($i = $pages - $this->numberOfPages + 1; $i <= $pages; $i ++)
                    $paging .= '<a href="' . $frontUrl . '' . $i . '' . $backUrl . '" ' . (($i == $currentPage) ? ' class="selected"' : ' class="normal"') . '>' . $i . '</a>';
            } else {
                $start = $currentPage - $halfPages;

                if($start == 0) {
                    $start = 1;
                }

                if($start < 1) {
                    $start_at = 1;
                    $end = (- 1) * $start + $halfPages + $currentPage;
                } else {
                    $start_at = $start;
                    $end = $start_at + $this->numberOfPages - 1;
                    if ($end > $pages) {
                        $start_at = $start_at - ($end - $pages);
                        $end = $pages;
                    }
                }

                for($i = $start_at; $i <= $end; $i ++)
                    $paging .= '<a href="' . $frontUrl . '' . $i . '' . $backUrl . '" ' . (($i == $currentPage) ? ' class="selected"' : ' class="normal"') . '>' . $i . '</a>';
            }
        } else {
            for($i = 1; $i <= $pages; $i ++)
                $paging .= '<a href="' . $frontUrl . '' . $i . '' . $backUrl . '" ' . (($i == $currentPage) ? ' class="selected"' : ' class="normal"') . '>' . $i . '</a>';
        }

        if($currentPage < $pages && $this->showPrevAndNext) {
            $paging .= '<a href="' . $frontUrl . '' . ($currentPage + 1) . '' . $backUrl . '"  title="Next page" class="arrows">'.$this->pagingNextText.'</a>';
        }

        if($currentPage + 1 < $pages && $this->showFirstAndLast) {
            $paging .= '<a href="' . $frontUrl . '' . $pages . '' . $backUrl . '" title="Last page" class="arrows">'.$this->pagingLastText.'</a>';
        }

        if($this->showPagesForm) {
            $paging .= '<form method="post" action="" class="page_form">'.$this->pagingPageText.' <input type="text" name="page" value="" /> <button type="submit" name="go_page">'.$this->pagingFormButtonText.'</button></form>';
        }

        if($this->showPagesNumber) {
            $paging .= '<div class="page_numbers">'.$this->pagingPageText.' '.$currentPage.' '.$this->pagingPageOfText.' '.$pages.'</div>';
        }

        $paging .= '<div style="clear: both"></div>';

        return $paging . "</div>";
    }

}

?>

I have this php pagination class and I cannot figure out a way to limit my SQL query. This generates the buttons for pagination just fine, but have no idea how to limit my query output. Also, how would I go about using this class with other variables in my url. because this one doesnt support it I dont think. like.

mypage.php?status=pending&page=4

here is what is in my "working" page.

<?php 

require_once('classes/class.paging.php');

// PAGINATION STUFF
$ordercount1 = mysql_query("SELECT * from orders WHERE technumber='$myuserid'");
$ordercount2 = mysql_num_rows($ordercount1);

// echo $ordercount2;

    $pagingClass = new paging();

    $limit = 10;        //Number of rows that we show on page
    $allRows = $ordercount2;    //Number of all rows that we have

    if(isset($_POST['page'])) {
        $_GET['page'] = $_POST['page'];
    }

    if(!isset($_GET['page']) || !is_numeric($_GET['page'])) {
        $_GET['page'] = 1;
    }

    //We off the page number
    $pagingClass->showPagesNumber = false;
    $pagingClass->showPagesForm = false;
    $pagingClass->numberOfPages = 10;
    echo $pagingClass->generate('?page=', '', $_GET['page'], $limit, $allRows);

// END PAGINATION STUFF

?>

limit your query ? what do you mean by that ? and what's the problem with the url variables ? and most important DO NOT use mysql_* - not only it is deprecated but also it invites sql_injection. — Nir Alfasi
Limit my queries so I can only show the amount of records, and make it move accordingly on each pagination page. $getmyorders = mysql_query("SELECT orderid, status, date FROM orders WHERE technumber ='$myuserid' ORDER BY date DESC LIMIT $LIMITER_HERE"); — Amber Stillwell
The $LIMITER_HERE is what I need, as the coder of this class failed to implement this I presume. :( — Amber Stillwell
For example: In this script net.tutsplus.com/tutorials/php/how-to-paginate-data-with-php the limiter for the SQL query is $pages->limit — Amber Stillwell
any by URL variables say I go to mypage.php?status=pending but with this pagination class if i click for instance page 4 it goes to mypage.php?page=4 instead of mypage.php?status=pending&page=4 — Amber Stillwell

Nir Alfasi Nir Alfasi · Accepted Answer · 2012-08-07T03:30:05

The link you provided show the usage:

$pages = new Paginator;
$pages->items_total = $num_rows[0];
$pages->mid_range = 9;
$pages->paginate();
echo $pages->display_pages();

In this example 9 is the total number of results from your select, use select count(1) from... in order to find this number (total number of results) before you use the Paginator.

As for the POST/GET parameters: before you call the paginator loop over the parameters and save them into a string:

foreach($_GET as $key=> $val){ $str .= "$key=$val&";}

Then override the class everytime it creates an HREF for example, change:

"href=\"{$_SERVER[PHP_SELF]}?page=$i&ipp=$this->items_per_page\">$i</a> ";

to:

"href=\"{$_SERVER[PHP_SELF]}?" . $str . "page=$i&ipp=$this->items_per_page\">$i</a> ";

issue limiting my sql query with pagination class

1 Answers