Let's take 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 that the page variable will come through the GET method, and must be an integer value. It will be useful for us 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, the count must be displayed from 1 , but in SQL this count must be made from 0 . This is why we use the ($page * $limit) - $limit) : calculation to normalize the value to be used in MYSQL.

• The following is a connection to the bank through the PDO .

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

• Then we have SELECT with 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 $limit variable.

• 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 http_build_query function. It generates data in url coding through array . When we add ['page' => $i] + $_GET we are ensuring that the value of page already in $_GET does not overwrite the value we want to have as page within http_build_query .

See why it's important to use PDO instead of mysql_*

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