Browse query records with PHP PDO

Question

I’ve always worked with ASP connections using ADODB and navigating the regressions that the query returned to me was not a problem because there were:

<%
rs.movenext //anda para proxima linha do retorno da query
rs.moveprevious //anda para linha anterior do retorno da query
rs.movefirst //anda para a primeira linha do retorno da query
rs.movelast //anda para a ultima linha do retorno da query
%>

more information about these commands here.

I’m having trouble navigating my PHP query records using PDO. I did not find anything that is equivalent to these commands mentioned above for example.

Let’s say I have one foreach to go through a query, but within each loop I need to check if the next record is the last record to add some more information in this current loop... how to do?

<?php
$sql = "Select * From Tabela";
$sql = $db->prepare($sql_pai);
$sql->execute();
if($sql->rowCount() > 0){
   foreach($sql as $rs){
      echo $rs["nome"];
      //VERIFICAR SE O PROXIMO É O ULTIMO PARA ADICIONAR MAIS INFO
   }
}
?>

Answer 1

the method PDOStatement::fetch() takes the cursor orientation as the second parameter, with the value of one of the constants PDO::FETCH_ORI_*. These parameters are only valid if the cursor of PDOStatement is created by passing the attribute PDO::ATTR_CURSOR worthwhile PDO::CURSOR_SCROLL.

This way you can navigate using as follows

$sql = "Select * From Tabela";
$statement = $db->prepare($sql, array(
    PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL,
));
$statement->execute();
$statement->fetch(PDO::FETCH_BOTH, PDO::FETCH_ORI_NEXT);  // retorna próximo
$statement->fetch(PDO::FETCH_BOTH, PDO::FETCH_ORI_PRIOR); // retorna anterior
$statement->fetch(PDO::FETCH_BOTH, PDO::FETCH_ORI_FIRST); // retorna primeiro
$statement->fetch(PDO::FETCH_BOTH, PDO::FETCH_ORI_LAST);  // retorna último
$statement->fetch(PDO::FETCH_BOTH, PDO::FETCH_ORI_ABS, $n); // retorna posição $n
$statement->fetch(PDO::FETCH_BOTH, PDO::FETCH_ORI_REL, $n); // retorna posição $n relativa à posição atual

See more details on documentation and in predefined constants.

With the functions presented you can create a loop to solve your problem.

Note: I used PDO::FETCH_BOTH because it is the default method, but you can change to the mode used in your project.

Answer 2

In PHP you can browse an array similar to the following functions:

• current() - Returns the current element in an array
• each() - Returns the key pair/current value of an array and advances its cursor
• reset() - Makes the internal pointer of an array point to its first element
• prev() - Back the internal pointer of an array
• next() - Advance the internal pointer of an array
• end() - Makes the internal pointer of an array point to its last element

Remember that this works only for navigation with the Array. PDO is only an abstraction layer for the connection with the bank.

Maybe what you’re looking for is some ORM.