Pagination Problem

Hi Matt,
I appreciate your effort at helping others here. Please I need an assistance. I am using a pagination script I found online and making use of the script involves these lines of code to be added to the index for it to work:

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

The script itself, I mean the class is here:

Class Paginator{
    var $items_per_page;
    var $ipp;
    var $items_total;
    var $current_page;
    var $num_pages;
    var $mid_range;
    var $low;
    var $limit;
    var $return;
    var $default_ipp;
    var $querystring;
    var $ipp_array;

    function Paginator()
    {
        if(!isset($_GET['page']))
        {
            $_GET['page'] = 1;
            }
            
        if(!isset($_GET['ipp']))
        {
            $_GET['ipp'] = $this->default_ipp; 
            }
             
        $this->current_page = 1;
        $this->mid_range = 7;
        $this->ipp_array = array(10,25,50,100,'All');
        $this->items_per_page = (!empty($_GET['ipp'])) ? $_GET['ipp']:$this->default_ipp;
        
    }

    function paginate()
    {
        if(!isset($this->default_ipp)) $this->default_ipp=25;
        if($_GET['ipp'] == 'All')
        {
            $this->num_pages = 1;
//            $this->items_per_page = $this->default_ipp;
        }
        else
        {
            if(!is_numeric($this->items_per_page) OR $this->items_per_page <= 0) $this->items_per_page = $this->default_ipp;
            $this->num_pages = ceil($this->items_total/$this->items_per_page);
        }
        $this->current_page = (isset($_GET['page'])) ? (int) $_GET['page'] : 1 ; // must be numeric > 0
        $prev_page = $this->current_page-1;
        $next_page = $this->current_page+1;
        if($_GET)
        {
            $args = explode("&",$_SERVER['QUERY_STRING']);
            foreach($args as $arg)
            {
                $keyval = explode("=",$arg);
                if($keyval[0] != "page" And $keyval[0] != "ipp") $this->querystring .= "&" . $arg;
            }
        }

        if($_POST)
        {
            foreach($_POST as $key=>$val)
            {
                if($key != "page" And $key != "ipp") $this->querystring .= "&$key=$val";
            }
        }
        if($this->num_pages > 10)
        {
            $this->return = ($this->current_page > 1 And $this->items_total >= 10) ? "<a class=\"paginate\" href=\"$_SERVER[PHP_SELF]?page=$prev_page&ipp=$this->items_per_page$this->querystring\">&laquo; Previous</a> ":"<span class=\"inactive\" href=\"#\">&laquo; Previous</span> ";

            $this->start_range = $this->current_page - floor($this->mid_range/2);
            $this->end_range = $this->current_page + floor($this->mid_range/2);

            if($this->start_range <= 0)
            {
                $this->end_range += abs($this->start_range)+1;
                $this->start_range = 1;
            }
            if($this->end_range > $this->num_pages)
            {
                $this->start_range -= $this->end_range-$this->num_pages;
                $this->end_range = $this->num_pages;
            }
            $this->range = range($this->start_range,$this->end_range);

            for($i=1;$i<=$this->num_pages;$i++)
            {
                if($this->range[0] > 2 And $i == $this->range[0]) $this->return .= " ... ";
                // loop through all pages. if first, last, or in range, display
                if($i==1 Or $i==$this->num_pages Or in_array($i,$this->range))
                {
                    $this->return .= ($i == $this->current_page And $_GET['page'] != 'All') ? "<a title=\"Go to page $i of $this->num_pages\" class=\"current\" href=\"#\">$i</a> ":"<a class=\"paginate\" title=\"Go to page $i of $this->num_pages\" href=\"$_SERVER[PHP_SELF]?page=$i&ipp=$this->items_per_page$this->querystring\">$i</a> ";
                }
                if($this->range[$this->mid_range-1] < $this->num_pages-1 And $i == $this->range[$this->mid_range-1]) $this->return .= " ... ";
            }
            $this->return .= (($this->current_page < $this->num_pages And $this->items_total >= 10) And ($_GET['page'] != 'All') And $this->current_page > 0) ? "<a class=\"paginate\" href=\"$_SERVER[PHP_SELF]?page=$next_page&ipp=$this->items_per_page$this->querystring\">Next &raquo;</a>\n":"<span class=\"inactive\" href=\"#\">&raquo; Next</span>\n";
            $this->return .= ($_GET['page'] == 'All') ? "<a class=\"current\" style=\"margin-left:10px\" href=\"#\">All</a> \n":"<a class=\"paginate\" style=\"margin-left:10px\" href=\"$_SERVER[PHP_SELF]?page=1&ipp=All$this->querystring\">All</a> \n";
        }
        else
        {
            for($i=1;$i<=$this->num_pages;$i++)
            {
                $this->return .= ($i == $this->current_page) ? "<a class=\"current\" href=\"#\">$i</a> ":"<a class=\"paginate\" href=\"$_SERVER[PHP_SELF]?page=$i&ipp=$this->items_per_page$this->querystring\">$i</a> ";
            }
            $this->return .= "<a class=\"paginate\" href=\"$_SERVER[PHP_SELF]?page=1&ipp=All$this->querystring\">All</a> \n";
        }
        $this->low = ($this->current_page <= 0) ? 0:($this->current_page-1) * $this->items_per_page;
        if($this->current_page <= 0) $this->items_per_page = 0;
        $this->limit = ($_GET['ipp'] == 'All') ? "":" LIMIT $this->low,$this->items_per_page";
    }
    function display_items_per_page()
    {
        $items = '';
        if(!isset($_GET[ipp])) $this->items_per_page = $this->default_ipp;
        foreach($this->ipp_array as $ipp_opt) $items .= ($ipp_opt == $this->items_per_page) ? "<option selected value=\"$ipp_opt\">$ipp_opt</option>\n":"<option value=\"$ipp_opt\">$ipp_opt</option>\n";
        return "<span class=\"paginate\">Items per page:</span><select class=\"paginate\" onchange=\"window.location='$_SERVER[PHP_SELF]?page=1&ipp='+this[this.selectedIndex].value+'$this->querystring';return false\">$items</select>\n";
    }
    function display_jump_menu()
    {
        $option = '';
        for($i=1;$i<=$this->num_pages;$i++)
        {
            $option .= ($i==$this->current_page) ? "<option value=\"$i\" selected>$i</option>\n":"<option value=\"$i\">$i</option>\n";
        }
        return "<span class=\"paginate\">Page:</span><select class=\"paginate\" onchange=\"window.location='$_SERVER[PHP_SELF]?page='+this[this.selectedIndex].value+'&ipp=$this->items_per_page$this->querystring';return false\">$option</select>\n";
    }
    function display_pages()
    {
        return $this->return;
    }
}  

The creator of the script specified that the query should follow this format:

SELECT title FROM articles WHERE title != '' ORDER BY title ASC $pages->limit

with special reference to $pages->limit; because its needed by the Paginator class to function.

Here is my challenge, the method that gets the list of asset to be paginated uses this code of yours in my asset manager class:

<?php
    public static function getAssetList( $numRows=100000000, $order="productName ASC" ) {
        $conn = DatabaseManager::getConnection();
        $sql = "SELECT SQL_CALC_FOUND_ROWS *, UNIX_TIMESTAMP(PurchaseDate) AS purchaseDate FROM table_asset
        ORDER BY " . mysql_escape_string($order) . " LIMIT :numRows";
    
        $st = $conn->prepare( $sql );
        $st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
        $st->execute();
        $list = array();
    
        while ( $row = $st->fetch() ) {
            $asset = new AssetManager( $row );
            $list[] = $asset;
        }
    
        // Now get the total number of articles that matched the criteria
        $sql = "SELECT FOUND_ROWS() AS totalRows";
        $totalRows = $conn->query( $sql )->fetch();
        $conn = null;
        return ( array ( "results" => $list, "totalRows" => $totalRows[0] ) );
    }
    ?> 

The challenge is how to incorporate $pages->limit into the sql query that get the result I need from the databse. How do i reconcile this? basically replacing $pages->limit with LIMIT :numRows as shown above. I don't know how to get it done because when I used $pages, there was fatal error telling me I was using non object variable.

Hi All programmers in the house especially the moderators, Matt and Chris, I am awaiting your response on this issue. i've tried severally to get the pagination working but its not. I hope to hear from you guys pretty soon. Thanks in advance

[Edited by papadammy on 13-Nov-12 14:17]

concatenate the variables as LIMIT values

LIMIT $P1, $P2 the first parameter is the row to start at, the second is the number of rows in the rowset.

$sql = "SELECT columns FROM `table` LIMIT $page, $limit;"

so ... LIMIT 5,10 will return rows 6 to 16

[Edited by chrishirst on 15-Nov-12 18:24]

--
Chris.
So long, and thanks for all the fish.
http://webmaster-talk.eu/

Also you should avoid using

SELECT * FROM

for a production build of your application.

--
Chris.
So long, and thanks for all the fish.
http://webmaster-talk.eu/

Thanks Chris fir your reply, I tried the solution you offered but it didn't work out for me. The creator of the script I'm using for pagination specifically stated the use of $pages->limit for the desired result to be gotten. Please how do I tag that line along with the code I have above that I want to paginate its result and in case you have a pagination script I can use and you're willing to share, I will appreciate that.Thanks so much.

Explain what you mean by

"Please how do I tag that line along with the code I have above that I want to paginate its result"

--
Chris.
So long, and thanks for all the fish.
http://webmaster-talk.eu/

Thanks Chris,
Have a look at my code that will draw the data to be paginated from the database:

public static function getAssetList( $numRows=100000000, $order="productName ASC" ) {
        $conn = DatabaseManager::getConnection();
        $sql = "SELECT SQL_CALC_FOUND_ROWS *, UNIX_TIMESTAMP(PurchaseDate) AS purchaseDate FROM table_asset
        ORDER BY " . mysql_escape_string($order) . " LIMIT :numRows";
    
        $st = $conn->prepare( $sql );
        $st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
        $st->execute();
        $list = array();
    
        while ( $row = $st->fetch() ) {
            $asset = new AssetManager( $row );
            $list[] = $asset;
        }
    
        // Now get the total number of assets that matched the criteria
        $sql = "SELECT FOUND_ROWS() AS totalRows";
        $totalRows = $conn->query( $sql )->fetch();
        $conn = null;
        return ( array ( "results" => $list, "totalRows" => $totalRows[0] ) );
    }
    ?> 

The author of the pagination code said the select statement MUST be like this:

SELECT title FROM articles WHERE title != '' ORDER BY title ASC $pages->limit;

The emphasis is on $pages->limit as that is what makes the whole pagination class to work.

My question now is how do I incorporate $pages->limit in my code above so that I can get the result paginated? Is there other way to paginate my result?

@papadammy:

$sql = "SELECT SQL_CALC_FOUND_ROWS *, UNIX_TIMESTAMP(PurchaseDate) AS purchaseDate FROM table_asset
        ORDER BY " . mysql_escape_string($order) . " " . $pages->limit;

--
Matt Doyle, Elated
3rd Edition of my jQuery Mobile book out now! Learn to build mobile web apps. Free sample chapter: http://store.elated.com/

Thanks Matt. I will try it out and get back to you.

Matt, I really do appreciate your effort at helping me. I've been disturbed for some time now about my brother who had a fatal accident and that's why I'd not been able to get back to you because the accident really touched me. I will let you know the output once I resume work fully. God bless you man.

Matt and Chris, thanks a lot for your help. The code worked but it didn't split my result as desired. Instead of showing me 10 results per page, it still displayed all the 21 results I have in the database although, it created the link for 3 pages at the base of the page. I set the default items per page to 10. Is there any way out of this? Thanks in advance.

@papadammy: I'm sorry to hear about your brother, that's terrible news.

I don't know how this pagination script works so it's difficult for me to help. Can you post the URL of the page with the problem so we can see what the problem is exactly?

--
Matt Doyle, Elated
3rd Edition of my jQuery Mobile book out now! Learn to build mobile web apps. Free sample chapter: http://store.elated.com/

Thanks Matt. I'm still developing the application locally on my system, hence there is no way of posting the link. I think I will simply look for another pagination script on the net and use for the project. Thanks for being nice and a great mentor. You are making impact.

@papadammy: You're welcome. Feel free to ask if you need any more help!

--
Matt Doyle, Elated
3rd Edition of my jQuery Mobile book out now! Learn to build mobile web apps. Free sample chapter: http://store.elated.com/

Thanks a lot Matt. Merry Xmas and happy new year in advance.