SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Location
    Port Harcourt
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Dealing with pagination with my code

    Good day everybody, please I need another assistance. I'm working on how to paginate my returned data from mysql and below is the method that fetches the list of asset from the database.

    I am using a pagination script I found online and making use of the script involves these lines of code to be added to th index for it to work:
    PHP Code:
    <?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:

    PHP Code:
    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'] : // 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 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] > And $i == $this->range[0]) $this->return .= " ... ";
                    
    // loop through all pages. if first, last, or in range, display
                    
    if($i==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-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 in my asset manager class:

    PHP Code:
    <?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"$numRowsPDO::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.

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
    LIMIT $this->low,$this->items_per_page 
    The LIMIT clause in pagination (after the first page) using Mysql involves 2 numbers.

    ie "LIMIT 20, 10"

    Your getAssetList method only allows one. Plus, I would guess "20, 10" would not be a good candidate for a prepared statement bindValue, but it might work.

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Location
    Port Harcourt
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cups, Thanks a lot. I will try it out. I hope to grow to your level soon and start helping out others too in php community.

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Just dive in any time you can. thanks!

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Location
    Port Harcourt
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cups, the code didn't work out. The creator of the script specify that for the pagination to be executed correctly, [$pages->limit ] must be used in the sql query. Please have a look at the query in my class method query and let me know how that piece fits in. Thanks in advance. Any other suggestion from anybody is welcome as well.

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Are you able to echo out $pages->limit onto the page? If so, what does it say?

    If you have this in your sql statement " ... LIMIT 20, 10" it is telling your datebase "starting at the 20th row, get the next 10 only".


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •