How to create a page numerator?

Question

I am developing a page "News" with several news, and it will be displayed only 9 news per page, above that, should go to page 2 and so on... how do I do this?

Answer 1

let’s do a basic example in PHP:

try{

    $page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);

    if (!$page) {
        $page = 1; // Se a "page" não existir, o seu valor é 1
    }

    $limit = 9;

    $offset = ($page * $limit) - $limit; // normaliza o offset utilizado no banco

    $pdo = new PDO('...'); // seu dados de conexão aqui

    $stmt = $pdo->prepare('SELECT count(*) as count FROM noticias');


    $stmt->execute();

    $result = $stmt->fetch(PDO::FETCH_ASSOC);

    $number_of_pages = ceil($result['count'] / $limit); // calcula o total máximo de páginas, arredondando pra cima :)

    unset($stmt, $result);

    $stmt = $pdo->prepare("SELECT * FROM notiicias LIMIT {$offset}, {$limit}"); // consulta com os limites

    $stmt->execute();

    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);



} catch(Exception $e){

    echo $e->getMessage();
}

Generating links in HTML:

    <?php for($i = 1; $i < $number_of_pages; $i++): ?>
        <a href="?<?php echo http_build_query(array('page' => $i) +  $_GET)"><?php echo $i ?></a>
   <?php endfor ?>

Explaining:

• filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT) is a new way for PHP to handle data coming from the client. That is, we define the variable page will come through the method GET, and should be an integer value. It will be useful to know which current page we are on. By default, see that its value is 1;

• The calculation of $offset is done as follows. For the user should display the count from 1, however, in SQL, this count should be made from 0. That’s why we use the calculation ($page * $limit) - $limit): to normalize the value to be used in MYSQL.

• Next we have a connection to the bank through the PDO.

• After obtaining the number of existing lines in the news table, we calculate what is the maximum number of pages we can generate. This is done using ceil($result['count'] / $limit. The function ceil has the job of rounding the number up, because we are working with integers.

• Then we have the SELECT with a LIMIT, where the first argument of LIMIT is the initial offset where we start the query; and the second argument is the limit per page, which in this case is 9, defined in the variable $limit.

• And finally, we have a for, which generates the links for you to click accordingly and display the results according to the page. I tried to leave the code as organized as possible. So I used the function http_build_query. It generates data in url encoding through a array. When we add up ['page' => $i] + $_GET we are ensuring that the value of page already existing in the $_GET does not overwrite the value we want to have as page inside http_build_query.

See why it’s important to use PDO instead of the duties mysql_*

To understand more about the "array sum", see the explanation in Handbook