Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Jan 2013
    Posts
    35
    Thanks
    2
    Thanked 0 Times in 0 Posts

    trying to get pagnation to work - FIXED

    fixed
    Last edited by ahaberman25; 05-14-2013 at 06:49 AM. Reason: fixed

  • #2
    New Coder
    Join Date
    Apr 2013
    Posts
    39
    Thanks
    2
    Thanked 2 Times in 2 Posts
    The easiest way to do pagination without causing much load to the database is to take your first database query $numrows value and divide it by the number of items you want on a page.
    For instance if your $numrows is 500 and you want to display 10 items on the page, your pagination will be 500/10 which will give you 50 more pages to display. So depending on how you want to display this pages whether it is with next and previous or you want to show more pagination on the page

  • #3
    New Coder
    Join Date
    Jan 2013
    Posts
    35
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by cssbutton View Post
    The easiest way to do pagination without causing much load to the database is to take your first database query $numrows value and divide it by the number of items you want on a page.
    For instance if your $numrows is 500 and you want to display 10 items on the page, your pagination will be 500/10 which will give you 50 more pages to display. So depending on how you want to display this pages whether it is with next and previous or you want to show more pagination on the page
    Right now this code I have isnt working at all though

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Don't use your outer query for a selection as suggested. That will result in an increase in memory as you increase in records which completely defeats the purpose of using a server side pagination.
    Issue a COUNT query first. MySQL also has a SQL_CALC_FOUND_ROWS as well, which can be used with a LIMIT command. The difference is that it will return the count of everything matched with the WHERE despite the LIMIT being in place. I do not suggest its used, only because I've never used it in the past but it sounds like its a fullscan which would then make it very slow. I cannot verify that is the case.
    So the process is really quite simple:
    Construct a WHERE clause for your SQL.
    Construct a COUNT query applying the WHERE clause.
    Calculate your offsets. That's a simple process of taking your current page (typically page - 1) and multiplying it by your number perpage. So for a simple example:
    PHP Code:
    $perpage 25// or whatever
    $page = isset($_GET['page']) ? $_GET['page'] - 0;
    if (
    $page 0)
    {
        
    $page 0;
    }

    $sWhere ' WHERE field = val'//etc
    $sLimit ' LIMIT ' . ($page $perpage) . ', ' $perpage;
    if (
    $qrycnt mysql_query("SELECT COUNT(id) FROM table {$sWhere}")))
    {
        
    $numrecords mysql_result($qrycnt0);

        if (
    $qryrecords mysql_query("SELECT field, field, field FROM table {$sWhere}{$sLimit}"))
        {
            
    // this is good now
        
    }

    Looks to me that you have a pretty good idea of what you do, just missing that you need to (or at least should be) using a COUNT for total records, followed by the fetch and iteration, so you'll need two queries (since I don't suggest the SQL_CALC_ROWS_FOUND option).
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #5
    New Coder
    Join Date
    Jan 2013
    Posts
    35
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    Don't use your outer query for a selection as suggested. That will result in an increase in memory as you increase in records which completely defeats the purpose of using a server side pagination.
    Issue a COUNT query first. MySQL also has a SQL_CALC_FOUND_ROWS as well, which can be used with a LIMIT command. The difference is that it will return the count of everything matched with the WHERE despite the LIMIT being in place. I do not suggest its used, only because I've never used it in the past but it sounds like its a fullscan which would then make it very slow. I cannot verify that is the case.
    So the process is really quite simple:
    Construct a WHERE clause for your SQL.
    Construct a COUNT query applying the WHERE clause.
    Calculate your offsets. That's a simple process of taking your current page (typically page - 1) and multiplying it by your number perpage. So for a simple example:
    PHP Code:
    $perpage 25// or whatever
    $page = isset($_GET['page']) ? $_GET['page'] - 0;
    if (
    $page 0)
    {
        
    $page 0;
    }

    $sWhere ' WHERE field = val'//etc
    $sLimit ' LIMIT ' . ($page $perpage) . ', ' $perpage;
    if (
    $qrycnt mysql_query("SELECT COUNT(id) FROM table {$sWhere}")))
    {
        
    $numrecords mysql_result($qrycnt0);

        if (
    $qryrecords mysql_query("SELECT field, field, field FROM table {$sWhere}{$sLimit}"))
        {
            
    // this is good now
        
    }

    Looks to me that you have a pretty good idea of what you do, just missing that you need to (or at least should be) using a COUNT for total records, followed by the fetch and iteration, so you'll need two queries (since I don't suggest the SQL_CALC_ROWS_FOUND option).
    thank you for the detailed response, I am not sure how this information goes in with the code I currently have. As it is already broken I dont want to make it worse, do you mind adding your code to mine to show me where it goes? Thank you again

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,132
    Thanks
    2
    Thanked 328 Times in 320 Posts
    You need a total of TWO queries. One to get the count of matching rows and one with a LIMIT clause to get the actual rows for any page. These queries must query the same table. The three queries you have in your posted code aren't all even for the Beans table and cannot possibly work.

    Since you haven't stated what result you do get from your code, it's not possible to narrow down the two-dozen possible things that could be wrong with your code.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #7
    New Coder
    Join Date
    Jan 2013
    Posts
    35
    Thanks
    2
    Thanked 0 Times in 0 Posts
    this is an updated version, everything works except it shows all rows/all columns of the table. pagination shows correct amount of tabs

    PHP Code:
    <html>
    <head><title>Your Page Title</title></head>
    <body>
    <?php
    $database
    ="products";
    mysql_connect ("localhost""root""root");
    @
    mysql_select_db($database) or die( "Unable to select database");
    $result mysql_query"SELECT * FROM Beans" )
    or die(
    "SELECT Error: ".mysql_error());
    $num_rows mysql_num_rows($result);
    print 
    "There are $num_rows records.<P>";
    print 
    "<table width=400 border=1>\n";
    while (
    $get_info mysql_fetch_row($result)){
    print 
    "<tr>\n";
    foreach (
    $get_info as $field)
    print 
    "\t<td><font face=arial size=1/>$field</font></td>\n";
    print 
    "</tr>\n";
    }
    print 
    "</table>\n";


    if(!empty(
    $_GET["start"])){
        
    $start $_GET['start'];// To take care global variable if OFF
    }else{
        
    $start  0;
    }
    if(!(
    $start 0)) { // This variable is set to zero for the first page
        
    $start 0;
    }

    $eu = ($start 0);
    $limit           1// No of records to be shown per page.
    $whathis      $eu $limit;
    $back          $eu $limit;
    $next          $eu $limit;

    // to check the total number of records
    $query         mysql_query(" SELECT * FROM Beans ") or die (mysql_error());
    $total_rows     mysql_num_rows($query);

    //select the record with limitation
    $query         mysql_query(" SELECT * FROM Beans limit $eu, $limit ") or die (mysql_error());

    //code for previous
    if($back >=0) {
    echo 
    "<a href='search.php?start=$back'><font face='Verdana' size='2'>PREV</font></a>&nbsp;&nbsp;";
    }

    //code for the number of page with links
    $i     0;
    $x    1;
    for(
    $i=0;$i $total_rows;$i=$i+$limit){
    if(
    $i != $eu){
        echo 
    "<a href='search.php?start=$i'><font face='Verdana' size='2'>$x</font></a> ";
    }else { 
        echo 
    "<font face='Verdana' size='4' color=red>$x</font>";
    // Current page is not displayed as link and given font color red

    $x    $x+1;
    }
    //code for next
    if($whathis $total_rows) {
    echo 
    "<a href='search.php?start=$next'><font face='Verdana' size='2'>NEXT</font></a>";
    }    
    ?>
    </body>
    </html>


  •  

    Posting Permissions

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