SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Thread: PHP Paging

  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP Paging

    I've searched the forums and have not really found much that has helped me. I'm looking to understand how to create a paging system for a potential large number of records. Good possibility on 1 million+ in time.

    I've tried implementing PEAR:PAGING but there doesn't seem to be a nice tidy little "here ya go" package. All examples online showing how to integrate PEAR with database Paging (MDB2, Pager, etc) seem to not work my my application... Not sure what's wrong, but I get errors...

    I've tried Googling "PHP Pagination" or "PHP Paging" and I get a large number of results... The challenge is that I don't know how these paging examples would work with a large number of records, nor do I know what to look for in a paging system.

    So what I'm curious about is this? What is the preferred method of Paging of members here who are paging a large number of records?

  2. #2
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd suggest that with 1M records the standard paging technique (for example links to first, previous, next, last and a span of 10 pages around the current page) wouldn't be very usable. With 100 results per page you'd have 10,000 pages and the majority the middle would just not get used.

    Another way to search would be necessary I think.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not all records would be paged at the same time...

    For example, there may be 1000 clientID records spread over those million records. So each client could have 10,000 records for example.

    I'd like to have the ability to show the 10,000 results in a nicely paged system like you describe, but it will be accessing a table with a million records.

  4. #4
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1M records should be okay if you have an index on the appropriate column (clientID). Issue a separate query to do the count when determining how many page links you'll need.

    If you're using an InnoDB table, count queries can be slow, so it may be an idea to cache that result to avoid re-counting each time a user changes page. On the initial view of the list you could determine the number of applicable records (matching the clientID) and store that number in the session, for 5 or 10 minutes and use that value on subsequent pages to construct the page links.

    Then your queries to get real data will look something like this:
    Code SQL:
    SELECT cols, that, i, need 
    FROM `table` 
    WHERE clientID = 1234 
    ORDER BY something 
    LIMIT 300,100

  5. #5
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    936
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Here is a nice class for pagination, just extend it my friend.
    http://net.tutsplus.com/tutorials/ph...data-with-php/

    And optimize the mysql queries....

    I'm also currently enhancing this class.

    Good Luck.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @ cranial-bore

    I think I understand. The first Query returns the total number of records. The second Query returns the contents of the information... For example, While there may be 10,000 records, LIMIT will cause me to query a set amount for the page.

    @ solidcodes

    I appreciate the link you sent. The challenge is that when you read further into the comments, it would appear that other users have indicated that with large tables, this paging system would become inefficient.

  7. #7
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, the LIMIT clause works with start and for values
    LIMIT 0,100 gets the first 100 rows
    LIMIT 100,100 gets the next 100 rows
    LIMIT 100, 200 would get 200 rows, starting from 100

    So you'll adjust these offsets depending on which page the user is on. The count query is simply to know how many pages are required
    PHP Code:
    $total_records 10923;
    $per_page 100;
    $pages ceil($total_records/$per_page);

    //110 pages required 

  8. #8
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try something like this:

    Code:
    $numResults = 50 //number of results on each page
    $page = ceil($_GET['page']);
    
    if($page <= 0){
    $page = 1;
    }
    
    $low = ($numResults * $page) - $numResults;
    
    $sql = 'SELECT * FROM table WHERE value=\'value\' LIMIT ' . $low . ',' . $numResults;
    $res = mysql_query($sql);
    
    while($row = mysql_fetch_array($res)){
    echo 'whatever is in each row' . row['value'];
    }
    That should be right, obviously it can be adjusted for people who like messing with you like typing ?page=asdf into the url bar.

  9. #9
    SitePoint Enthusiast
    Join Date
    Jan 2006
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got everything to work perfectly. 2 Queries, one counts the number of records, the second limits the records returned... It only shows the most recent 5 pages but lets it go waaaaay back if desired and I've integrated a handy little "This page loaded in 0.004 seconds" timer. I'm sure this will come in handy!

    Thanks for the help everyone!

  10. #10
    SitePoint Member
    Join Date
    Jul 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Build a SQL query script with the page number and page size.
    And, then query the database, get what you need.

  11. #11
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    I am tryiing to use the pagination class recommended by 'solidcodes'..

    However i am having problems reading out the fields from the database. Visit the link below:-

    http://www.skindeepapparel.com/lou/gallery.php

    The code i have is shown below:-

    PHP Code:
                <?php

                
    include("paginator.php"); //Paginator class is within this file
            
                
    $pages = new Paginator;  
                
    $pages->items_total $num_rows[0];  
                
    $pages->mid_range 9;  
                
    $pages->paginate();  
                echo 
    $pages->display_pages(); 
                               
                include(
    "conn.php"); 
                                         
                
    $query "SELECT image_name FROM images WHERE image_name != '' ORDER BY image_id ASC $pages->limit";
                
    $result =  mysql_query ($query);
                
    $row mysql_fetch_array($result,MYSQL_ASSOC);
                
                echo 
    "<div><a href='images/$row[image_name]' rel='lightbox' title='$row[caption]'><img src='images/$row[image_name]' alt='' width='80' height='80'/></a><span>$row[caption]</span></div>";
                
                
    mysql_close();    
                
    ?>
    I think it is the how i am reading out the fields from the database..

    Can anyone help

    Regards


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
  •