#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Edinburgh
    Posts
    19
    Rep Power
    0

    Question Only show 10 records on a page at a time


    Hi there

    I'm working on a page that will show a list of completed customer feedback records. I have limited this page to only show 10 records at a time.

    Could someone please help me as i would like to have a NEXT button to show the next 10 records and so on.

    Here is my connection:
    Code:
    <?php
    $username="#######";
    $password="#######";
    $database="#######";
    
    mysql_connect(localhost,$username,$password);
    @mysql_select_db($database) or die( "Unable to select database");
    $query="SELECT * FROM ratings ORDER BY id DESC limit 10";
    $result=mysql_query($query);
    
    $num=mysql_numrows($result);
    
    mysql_close();
    ?>
    Thank you

    Hudbarnett
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,128
    Rep Power
    9398
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Edinburgh
    Posts
    19
    Rep Power
    0
    Originally Posted by requinix
    The magic word is "pagination".
    Thank you, i'll do a search on pagination. Still very new to php and trying to teach myself.

    Thanks for your help.

    Hudbarnett
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    actually, the logic behind pagination is very simply:

    If you're on page p (starting with 1), and there are e entries per page, skip (p - 1) * e entries and select e entries. Just look at MySQL's LIMIT syntax on how to do this.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Edinburgh
    Posts
    19
    Rep Power
    0
    Originally Posted by Jacques1
    Hi,

    actually, the logic behind pagination is very simply:

    If you're on page p (starting with 1), and there are e entries per page, skip (p - 1) * e entries and select e entries. Just look at MySQL's LIMIT syntax on how to do this.
    I found an example which i have been able to get working with my database. It gives me a good understanding about how this was done. I'm now trying to get my head around showing the data in a table format rather than just raw data.

    Here is my code that i'm working with, any pointers?

    Code:
    <?php
    // database connection info
    $conn = mysql_connect('localhost','#####','#####') or trigger_error("SQL", E_USER_ERROR);
    $db = mysql_select_db('#####',$conn) or trigger_error("SQL", E_USER_ERROR);
    
    // find out how many rows are in the table 
    $sql = "SELECT COUNT(*) FROM ratings";
    $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
    $r = mysql_fetch_row($result);
    $numrows = $r[0];
    
    // number of rows to show per page
    $rowsperpage = 5;
    // find out total pages
    $totalpages = ceil($numrows / $rowsperpage);
    
    // get the current page or set a default
    if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
       // cast var as int
       $currentpage = (int) $_GET['currentpage'];
    } else {
       // default page num
       $currentpage = 1;
    } // end if
    
    // if current page is greater than total pages...
    if ($currentpage > $totalpages) {
       // set current page to last page
       $currentpage = $totalpages;
    } // end if
    // if current page is less than first page...
    if ($currentpage < 1) {
       // set current page to first page
       $currentpage = 1;
    } // end if
    
    // the offset of the list, based on current page 
    $offset = ($currentpage - 1) * $rowsperpage;
    
    // get the info from the db 
    $sql = "SELECT AccountNo, Name, q1 , q2, q3, q4, q5, q6, q7, q8 FROM ratings ORDER BY id DESC LIMIT $offset, $rowsperpage";
    $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
    
    // while there are rows to be fetched...
    while ($list = mysql_fetch_assoc($result)) {
       // echo data
       echo $list['AccountNo'] . " : " . $list['Name'] . " : " . $list['q1'] . " : " . $list['q2'] . " : " . $list['q3'] . " : " . $list['q4'] . " : " . $list['q5'] . " : " . $list['q6'] . " : " . $list['q7'] . " : " . $list['q8'] . "<br />";
    
    } // end while
    
    /******  build the pagination links ******/
    // range of num links to show
    $range = 3;
    
    // if not on page 1, don't show back links
    if ($currentpage > 1) {
       // show << link to go back to page 1
       echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
       // get previous page num
       $prevpage = $currentpage - 1;
       // show < link to go back to 1 page
       echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
    } // end if 
    
    // loop to show links to range of pages around current page
    
    for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
       // if it's a valid page number...
       if (($x > 0) && ($x <= $totalpages)) {
          // if we're on current page...
          if ($x == $currentpage) {
             // 'highlight' it but don't make a link
             echo " [<b>$x</b>] ";
          // if not current page...
          } else {
             // make it a link
             echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
          } // end else
       } // end if 
    } // end for
                     
    // if not on last page, show forward and last page links        
    if ($currentpage != $totalpages) {
       // get next page
       $nextpage = $currentpage + 1;
        // echo forward link for next page 
       echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
       // echo forward link for lastpage
       echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
    } // end if
    /****** end build pagination links ******/
    ?>
    Thanks
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Don't just copy and paste code from the internet. Most of what you'll find is just bad and sometimes even dangerous.

    "Your" code isn't particularly bad, but it isn't good either, because it relies on the obsolete "mysql_" functions and doesn't have any escaping. Also it's unnecessary to count the total number of entries and get the actual entries separately. MySQL can do that with SQL_CALC_FOUND_ROWS.

    So what you should do is maybe look at the code for some ideas and then write your own script. You'll also learn much more from that.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Edinburgh
    Posts
    19
    Rep Power
    0
    Originally Posted by Jacques1
    Don't just copy and paste code from the internet. Most of what you'll find is just bad and sometimes even dangerous.

    "Your" code isn't particularly bad, but it isn't good either, because it relies on the obsolete "mysql_" functions and doesn't have any escaping. Also it's unnecessary to count the total number of entries and get the actual entries separately. MySQL can do that with SQL_CALC_FOUND_ROWS.

    So what you should do is maybe look at the code for some ideas and then write your own script. You'll also learn much more from that.
    Thank you for your advice, i'll try and create my own page as i know that you are right about learning more form it.

    Thanks

    Hudbarnett

IMN logo majestic logo threadwatch logo seochat tools logo