script - php<?= ?>

Script de paginación PHP simple

Question

Tengo filas de datos procedentes de la base de datos, me gustaría tener una tabla con una paginación simple, ¿cuál es la forma más fácil de hacerlo?
Me alegraría si alguien pudiera proporcionar.

Answer 1

Algunos de los tutoriales que encontré que son fáciles de entender son:

• http://www.phpfreaks.com/tutorial/basic-pagination

Tiene más sentido dividir tu lista en fragmentos de tamaño de página, y solo consultar tu base de datos un fragmento a la vez. Esto reduce drásticamente el tiempo de procesamiento del servidor y el tiempo de carga de la página, y le da al usuario información más pequeña para digerir, por lo que no se atraganta con la basura que tratas de darle. El acto de hacer esto se llama paginación.

Una rutina de paginación básica parece larga y aterradora al principio, pero una vez que cierras los ojos, tomas una respiración profunda y miras cada parte del guión individualmente, encontrarás que en realidad es algo bastante fácil

La secuencia de comandos:

// find out how many rows are in the table 
$sql = "SELECT COUNT(*) FROM numbers";
$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 = 10;
// 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 id, number FROM numbers 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[''id''] . " : " . $list[''number''] . "<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 ******/
?>

• http://www.tonymarston.net/php-mysql/pagination.html

Este tutorial está dirigido a desarrolladores que deseen ofrecer a sus usuarios la posibilidad de recorrer un gran número de filas de bases de datos en fragmentos manejables en lugar de hacerlo todo de una sola vez.

Answer 2

Esta es una mezcla de HTML y código, pero es bastante básica, fácil de entender y debería ser bastante simple de desacoplar para satisfacer sus necesidades, creo.

try {

    // Find out how many items are in the table
    $total = $dbh->query(''
        SELECT
            COUNT(*)
        FROM
            table
    '')->fetchColumn();

    // How many items to list per page
    $limit = 20;

    // How many pages will there be
    $pages = ceil($total / $limit);

    // What page are we currently on?
    $page = min($pages, filter_input(INPUT_GET, ''page'', FILTER_VALIDATE_INT, array(
        ''options'' => array(
            ''default''   => 1,
            ''min_range'' => 1,
        ),
    )));

    // Calculate the offset for the query
    $offset = ($page - 1)  * $limit;

    // Some information to display to the user
    $start = $offset + 1;
    $end = min(($offset + $limit), $total);

    // The "back" link
    $prevlink = ($page > 1) ? ''<a href="?page=1" title="First page">&laquo;</a> <a href="?page='' . ($page - 1) . ''" title="Previous page">&lsaquo;</a>'' : ''<span class="disabled">&laquo;</span> <span class="disabled">&lsaquo;</span>'';

    // The "forward" link
    $nextlink = ($page < $pages) ? ''<a href="?page='' . ($page + 1) . ''" title="Next page">&rsaquo;</a> <a href="?page='' . $pages . ''" title="Last page">&raquo;</a>'' : ''<span class="disabled">&rsaquo;</span> <span class="disabled">&raquo;</span>'';

    // Display the paging information
    echo ''<div id="paging"><p>'', $prevlink, '' Page '', $page, '' of '', $pages, '' pages, displaying '', $start, ''-'', $end, '' of '', $total, '' results '', $nextlink, '' </p></div>'';

    // Prepare the paged query
    $stmt = $dbh->prepare(''
        SELECT
            *
        FROM
            table
        ORDER BY
            name
        LIMIT
            :limit
        OFFSET
            :offset
    '');

    // Bind the query params
    $stmt->bindParam('':limit'', $limit, PDO::PARAM_INT);
    $stmt->bindParam('':offset'', $offset, PDO::PARAM_INT);
    $stmt->execute();

    // Do we have any results?
    if ($stmt->rowCount() > 0) {
        // Define how we want to fetch the results
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $iterator = new IteratorIterator($stmt);

        // Display the results
        foreach ($iterator as $row) {
            echo ''<p>'', $row[''name''], ''</p>'';
        }

    } else {
        echo ''<p>No results could be displayed.</p>'';
    }

} catch (Exception $e) {
    echo ''<p>'', $e->getMessage(), ''</p>'';
}

Answer 3

 <?php
// Custom PHP MySQL Pagination Tutorial and Script
// You have to put your mysql connection data and alter the SQL queries(both queries)

mysql_connect("DATABASE_Host_Here","DATABASE_Username_Here","DATABASE_Password_Here") or die (mysql_error());
mysql_select_db("DATABASE_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");
//////////////////////////////////// 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 Pagination Logic ////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////// 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 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>Simple Pagination</title>
</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>