• Pagination Script and Tutorial for PHP MySQL Programmers Paging Results

    By Adams:

    Hello fellow php codersHappy This is a pagination script and tutorial created custom for educational purposes. Spread it around to people asking about how to page out database result sets. We are using PHP, MySQL, CSS, and HTML. Very simple and jQuery Ajax could be applied to make the page changing not require refreshing of the page.

    This script is accompanied by this in depth VIDEO TUTORIAL:
    http://www.developphp.com/view.php?tid=1039

    First here is what this script will render in a browser:

    Develop PHP browser display window

    Total Items: 381

    Page 6 of 39            Back   4    5    6    7    8    Next

    Alexander

    United States of America

    PedaDunkPamma

    United States of America

    lathiful

    Indonesia

    seyf

    Tunisia

    John

    Canada

    mike

    United States of America

    Kenneth

    Norway

    Daniel

    United States of America

    Dave

    United States of America

    Gorge

    United States of America

    Page 6 of 39          Back   4    5    6    7    8    Next

    Below is the PHP, MySQL, HTML, and CSS code that renders the output above.

    You have to put your mysql connection data, and alter the SQL queries(both queries).

    Learn HTML
    <?php
    // Adam's Custom PHP MySQL Pagination Tutorial and Script
    // You have to put your mysql connection data and alter the SQL queries(both queries)
    // This script is in tutorial form and is accompanied by the following video:
    // http://www.youtube.com/watch?v=K8xYGnEOXYc

    mysql_connect("DB_Host_Here","DB_Username_Here","DB_Password_Here") or die (mysql_error());
    mysql_select_db("DB_Name_Here") or die (mysql_error());
    //////////////  QUERY THE MEMBER DATA INITIALLY LIKE YOU NORMALLY WOULD
    $sql = mysql_query("SELECT id, firstname, country FROM myTable ORDER BY id ASC");
    //////////////////////////////////// Adam's Pagination Logic ////////////////////////////////////////////////////////////////////////
    $nr = mysql_num_rows($sql); // Get total of Num rows from the database query
    if (isset($_GET['pn'])) { // Get pn from URL vars if it is present
        $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); // filter everything but numbers for security(new)
        //$pn = ereg_replace("[^0-9]", "", $_GET['pn']); // filter everything but numbers for security(deprecated)
    } else { // If the pn URL variable is not present force it to be value of page number 1
        $pn = 1;
    }
    //This is where we set how many database items to show on each page
    $itemsPerPage = 10;
    // Get the value of the last page in the pagination result set
    $lastPage = ceil($nr / $itemsPerPage);
    // Be sure URL variable $pn(page number) is no lower than page 1 and no higher than $lastpage
    if ($pn < 1) { // If it is less than 1
        $pn = 1; // force if to be 1
    } else if ($pn > $lastPage) { // if it is greater than $lastpage
        $pn = $lastPage; // force it to be $lastpage's value
    }
    // This creates the numbers to click in between the next and back buttons
    // This section is explained well in the video that accompanies this script

    $centerPages = "";
    $sub1 = $pn - 1;
    $sub2 = $pn - 2;
    $add1 = $pn + 1;
    $add2 = $pn + 2;
    if ($pn == 1) {
        $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
        $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
    } else if ($pn == $lastPage) {
        $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
        $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
    } else if ($pn > 2 && $pn < ($lastPage - 1)) {
        $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' . $sub2 . '</a> &nbsp;';
        $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
        $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
        $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
        $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a> &nbsp;';
    } else if ($pn > 1 && $pn < $lastPage) {
        $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a> &nbsp;';
        $centerPages .= '&nbsp; <span class="pagNumActive">' . $pn . '</span> &nbsp;';
        $centerPages .= '&nbsp; <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a> &nbsp;';
    }
    // This line sets the "LIMIT" range... the 2 values we place to choose a range of rows from database in our query
    $limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage;
    // Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax
    // $sql2 is what we will use to fuel our while loop statement below
    $sql2 = mysql_query("SELECT id, firstname, country FROM myTable ORDER BY id ASC $limit");
    //////////////////////////////// END Adam's Pagination Logic ////////////////////////////////////////////////////////////////////////////////
    ///////////////////////////////////// Adam's Pagination Display Setup /////////////////////////////////////////////////////////////////////

    $paginationDisplay = ""; // Initialize the pagination output variable
    // This code runs only if the last page variable is ot equal to 1, if it is only 1 page we require no paginated links to display
    if ($lastPage != "1"){
        // This shows the user what page they are on, and the total number of pages
        $paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '
    &nbsp;  &nbsp;  &nbsp; ';
        // If we are not on page 1 we can place the Back button
        if ($pn != 1) {
            $previous = $pn - 1;
            $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous . '"> Back</a> ';
        }
        // Lay in the clickable numbers display here between the Back and Next links
        $paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';
        // If we are not on the very last page we can place the Next button
        if ($pn != $lastPage) {
            $nextPage = $pn + 1;
            $paginationDisplay .=  '&nbsp;  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '"> Next</a> ';
        }
    }
    ///////////////////////////////////// END Adam's Pagination Display Setup ///////////////////////////////////////////////////////////////////////////
    // Build the Output Section Here

    $outputList = '';
    while($row = mysql_fetch_array($sql2)){

        $id = $row["id"];
        $firstname = $row["firstname"];
        $country = $row["country"];

        $outputList .= '<h1>' . $firstname . '</h1><h2>' . $country . ' </h2><hr />';
       
    } // close while loop

    ?>
    <html>
    <head>
    <title>Adam's Pagination</title>

    <style type="text/css">
    <!--

    .pagNumActive {
        color: #000;
        border:#060 1px solid; background-color: #D2FFD2; padding-left:3px; padding-right:3px;
    }
    .paginationNumbers a:link {
        color: #000;
        text-decoration: none;
        border:#999 1px solid; background-color:#F0F0F0; padding-left:3px; padding-right:3px;
    }
    .paginationNumbers a:visited {
        color: #000;
        text-decoration: none;
        border:#999 1px solid; background-color:#F0F0F0; padding-left:3px; padding-right:3px;
    }
    .paginationNumbers a:hover {
        color: #000;
        text-decoration: none;
        border:#060 1px solid; background-color: #D2FFD2; padding-left:3px; padding-right:3px;
    }
    .paginationNumbers a:active {
        color: #000;
        text-decoration: none;
        border:#999 1px solid; background-color:#F0F0F0; padding-left:3px; padding-right:3px;
    }
    -->
    </style>

    </head>
    <body>
       <div style="margin-left:64px; margin-right:64px;">
         <h2>Total Items: <?php echo $nr; ?></h2>
       </div>
          <div style="margin-left:58px; margin-right:58px; padding:6px; background-color:#FFF; border:#999 1px solid;"><?php echo $paginationDisplay; ?></div>
          <div style="margin-left:64px; margin-right:64px;"><?php print "$outputList"; ?></div>
          <div style="margin-left:58px; margin-right:58px; padding:6px; background-color:#FFF; border:#999 1px solid;"><?php echo $paginationDisplay; ?></div>
    </body>
    </html>
  • You might also like

    No comments:

    Post a Comment

    Good day precious one, We love you more than anything.

Powered by Blogger.