PDO Paging - Fatal error: Out of memory

It is a bad idea to use PDOStatement::fetchAll() if you have no idea how many records will be returned and stored in memory. Keep in mind that the PDOStatement class also works as an iterator, avoiding the premeditated use of memory. In your case, use PDOStatement::rowCount() to get the number of records in hand and iterate with the PDOStatement::fetch() method.

I particularly prefer running a query with COUNT(*) before, to get the total number of records, and another definitive query to retrieve them, using mechanisms with OFFSET and LIMIT . It does not make sense to request rows from the database that will not be used during execution.

By mistake, the one with query with where seems to be the problem, the idela is paging it, another problem is that this query is executed twice. See:

} else {
    $sql = "SELECT * FROM noticias WHERE 1 ORDER BY Nid DESC";
    $stmt = $DB->prepare($sql);
}
$stmt->execute(); //primeira vez que a consulta é executada.
$total_count = count($stmt->fetchAll());

//... código omitido.

$sql2 = $sql . " limit " . ($lower_limit) . " ,  " . ($page_limit) . " ";
$stmt = $DB->prepare($sql2);

//código omitido.

$stmt->execute(); //segunda vez que a mesma consulta é executada.
$results = $stmt->fetchAll();

I think the code can be simplified like this:

$pagenum = isset($_GET['pagenum']) ? $_GET['pagenum'] : 1;

$limite = ($_GET["show"] <> "" && is_numeric($_GET["show"]) ) ? $_GET["show"] : 30;
$offset = ($pagenum * $limit) - $limit; 


try {
    $keyword = trim($_GET["keyword"]);
    if (!empty($keyword)) {
        $sql = "SELECT * FROM noticias WHERE conteudo LIKE :keyword OR titulo LIKE :keyword ORDER BY Nid DESC";
        $stmt = $DB->prepare($sql);

        $likekeyword = "%".$keyword."%";
        $stmt->bindParam(':keyword', $likekeyword, PDO::PARAM_STR);

    } else {
        $sql = "SELECT * FROM noticias WHERE 1 ORDER BY Nid DESC LIMIT :limit, :offset";
        $stmt = $DB->prepare($sql);
        $stmt->bindValue(':limit', $limite, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    }

    $stmt->execute();
    $results = $stmt->fetchAll();

    //Faz o calculo do número de registros por página
    $total_registros = $DB->query("SELECT COUNT(*) FROM noticias")->fetchColumn() or die($db->error);
    $paginas = floor($total_registros / $limite);



} catch (Exception $ex) {
    echo $ex->getMessage();
}


//imprime os resultados e os link das páginas(segundo for).
foreach($results as $item){
    printf("id: %d - título: %s - idade: %d <br>", $item['id'], $item['titulo']);
}


for($i = 1; $i <= $paginas; $i++){
    printf('<a href="?page=%d">%d</a>|', $i, $i);
}

Reference:

PHP Script Paging

Try to separate some of the function variables, so you can better visualize what's going on.

As I said before in the comments, there are several alternatives to the count function you were using along with the fetchAll method, which were being repeated unnecessarily times. Also, fetchAll has default method of search or PDO::FETCH_BOTH .

<?php

$dsn = "mysql:host=localhost;dbname=exemplo;charset=utf8;";
$pagina = isset($_GET['pagina']) ? (int)$_GET['pagina'] : 1;
$pagina = ($pagina > 0) ? $pagina : 1;
$termo_busca = isset($_GET['s']) ? (string)$_GET['s'] : '';
$por_pagina = 3;
$offset = ($pagina -1) * $por_pagina;
$dados = array();
$total = "";
$i = 1;

try {
    $pdo = new pdo($dsn, 'root', '');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "SELECT * FROM noticias WHERE conteudo LIKE :busca OR titulo LIKE :busca ORDER BY Nid DESC LIMIT {$por_pagina} OFFSET {$offset}";
    if($prepare = $pdo->prepare($sql)){
        if($prepare->execute(array(':busca'=>'%'.$termo_busca.'%'))){
            while($linha = $prepare->fetch(PDO::FETCH_OBJ)){
                $dados[] = $linha;
            }
            # condição para corrigir a paginacao quando existe um termo de busca
            if($termo_busca):
                $where = "WHERE conteudo LIKE '%{$termo_busca}%' OR titulo LIKE '%{$termo_busca}%'";
            else:
                $where = "";
            endif;  

            $count = $pdo->query("SELECT COUNT(*) FROM notcias {$where}")->fetchColumn();
            $total = ceil($count/$por_pagina);
        }
    }
} catch(PDOException $e){
    die($e->getMessage());
}

# imprimir os resultados e paginar
if($dados){
    foreach($dados as $object){
        print $object->titulo . "<br/>";
    }

    do{
        if($termo_busca):
            print "<a href=\"{$_SERVER['PHP_SELF']}?s={$termo_busca}&pagina={$i}\"> {$i} </a>";
        else:
            print "<a href=\"{$_SERVER['PHP_SELF']}?pagina={$i}\"> {$i} </a>";
        endif;

        $i++;
    } while($i <= $total);
} else {
    print "Nenhum resltado encontrado <a href=\"{$_SERVER['PHP_SELF']}\">voltar</a>";
}

?>

This query is internal, and returns the number of results corresponding to the given criteria:

$pdo->query("SELECT COUNT(*) FROM notcias {$where}"); # retorna COUNT(*)=> #numero

The fetchColumn function returns a single column in the following row of the result set. By adding to the previous query, it returns the exact result, with the COUNT(*) function returning only one column as a result of the query.

$pdo->query("SELECT COUNT(*) FROM notcias {$where}")->fetchColumn();

Or you can still use the query this way:

$pdo->query("SELECT * FROM exemplo {$where}")->rowCount(); 

There are still other ways to count the number of rows in the database without overloading the memory, you just have to look for it.