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 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    20
    Thanked 1 Time in 1 Post

    Pagination of MySQL Results

    Hello,
    Could someone help paginate the following code? I have tried so many tutorials without results. I would be grateful if it could be 7 rows a page.
    Thank you
    PHP Code:
    <?php

    $con 
    mysql_connect("***","***","***");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }

    mysql_select_db("b7_463487_LPS"$con);

    $query mysql_query("SELECT * FROM points ORDER BY LastName ASC, FirstName ASC") or die(mysql_error());
    $rows mysql_num_rows($data);  

    while(
    $row mysql_fetch_array($query))
      {

     
    $link 'addpoints.php?firstname=' $row['FirstName'] . '&lastname=' $row['LastName'] . '&Points=' $row['Points']; 
     
    $link2 'confirmdelete.php?firstname=' $row['FirstName'] . '&lastname=' $row['LastName']; 

      echo 
    $row['FirstName'] . " " $row['LastName'] ." / "$row['Tutor'] . " / " $row['Points'] ;
      echo 
    " ";
      echo 
    " / ";
      echo 
    "<a href='".$link."'> +points </a>"
      echo 
    "/";
      echo 
    "<a href='".$link2."'> -delete </a>"
      echo 
    "/";

      echo 
    "<br />";
      }  

    mysql_close($con)

    ?>

  • #2
    New to the CF scene
    Join Date
    Aug 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm no expert, but here's how I'd do it:

    Use a variable that lets PHP know what page we're on:

    In your SELECT statement, add LIMIT 7, because that's how many rows you want on each page.
    Code:
    $query = mysql_query("SELECT * FROM points ORDER BY LastName ASC, FirstName ASC LIMIT 7") or die(mysql_error());
    After the LIMIT 7 (and a comma), we have to add our offset (i.e., how many rows we're going to skip to get to the first of the seven we want to show). In your case, you don't know what the offset is, so we have to define it.

    We can create a variable ($page, let's say) and multiply it by 7 (and create another variable from that), depending what page we're on. Page 2, for example, where we want to show rows 8-15, would have the URI: http://website.com/index.php?page=2

    The code to go along with that would go like this:
    Code:
    
    $page = $_GET['page'];
    $pageoffset = $page*7;
    
    $query = mysql_query("SELECT * FROM points ORDER BY LastName ASC, FirstName ASC LIMIT 7,$pageoffset") or die(mysql_error());
    I think think think this'll work. Again, I'm not an expert, so I'm not positive, but it seems right to me. Let me know how it works out!
    Last edited by iFrank; 08-14-2007 at 06:12 PM.

  • #3
    New to the CF scene
    Join Date
    Aug 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You might have to change "$pageoffset = $page*7" to this:
    Code:
    if ($page != ' ')
                  { $pageoffset = 0; }
           else
                  { $pageoffset = $page*7; }

  • #4
    Regular Coder
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    20
    Thanked 1 Time in 1 Post
    Thank you. That works.

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    For a full-featured pagination class that takes about 10 minutes to integrate, check out http://www.firepages.com.au .


  •  

    Posting Permissions

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