PDO Pagination - Fatal error: Out of memory

Question

I am making a pagination, but it is generating a memory error.

Is there any way to optimize or fix the fault? Or is it on the same server?

Note: the php.ini this set the memory in 512.

if (!(isset($_GET['pagenum']))) {
    $pagenum = 1;
} else {
    $pagenum = $_GET['pagenum'];
}
$page_limit = ($_GET["show"] <> "" && is_numeric($_GET["show"]) ) ? $_GET["show"] : 30;

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";
        $stmt = $DB->prepare($sql);
    }

    $stmt->execute();
    $total_count = count($stmt->fetchAll());

    $last = ceil($total_count / $page_limit);

    if ($pagenum < 1) {
        $pagenum = 1;
    } elseif ($pagenum > $last) {
        $pagenum = $last;
    }

    $lower_limit = ($pagenum - 1) * $page_limit;
    $lower_limit = ($lower_limit < 0) ? 0 : $lower_limit;

    $sql2 = $sql . " limit " . ($lower_limit) . " ,  " . ($page_limit) . " ";

    $stmt = $DB->prepare($sql2);

    if ($keyword <> "" ) {
        $stmt->bindParam(':keyword', $likekeyword, PDO::PARAM_STR);
    }

    $stmt->execute();
    $results = $stmt->fetchAll();
} catch (Exception $ex) {
    echo $ex->getMessage();
}

code displays counters

 <div class="col-lg-12 center">
  <ul class="pagination pagination-sm">
   <?php
   for ($i = 1; $i <= $last; $i++) {
   if ($i == $pagenum) {
    ?>
   <li class="active"><a href="javascript:void(0);" ><?php echo $i ?></a></li>
            <?php
          } else {
            ?>
            <li><a href="noticias_listar.php?pagenum=<?php echo $i; ?>&keyword=<?php echo $_GET["keyword"]; ?>" class="links"  onclick="displayRecords('<?php echo $page_limit; ?>', '<?php echo $i; ?>');" ><?php echo $i ?></a></li>
            <?php
          }
        }
        ?>
      </ul>
    </div>

Answer 1

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

I particularly prefer to run 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 makes no sense to request lines from the database that will not be used during execution.

Answer 2

Try separating some of the variables from the functions, so you can better visualize what’s going on.

As I said earlier in the comments, there are several alternatives to the function count you were using along with the method fetchAll, and that were being repeated unnecessary times. Recalling also that the fetchAll has as default search method the 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 criterion:

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

The function fetchColumn returns a single column in the following row of the result set. Adding to the query Teriror, it returns the exact result, and the function COUNT(*) returns only one column for 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 also other ways to count the number of existing lines in the database without overloading the memory, you just have to search.

Answer 3

By error, the one with Where query seems to be the problem, the idella is to page it, another problem is that this query is executed twice. Behold:

} 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 believe the code can be simplified in this way:

$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