Navigate query records with PHP PDO


I have always worked with ASP connections using ADODB and navigating the logs that the query returned to me was not a problem since they 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 on these commands here .

I'm having trouble navigating the records in my PHP query using PDO. I have not found something that is equivalent to these commands mentioned above.

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

$sql = "Select * From Tabela";
$sql = $db->prepare($sql_pai);
if($sql->rowCount() > 0){
   foreach($sql as $rs){
      echo $rs["nome"];
asked by anonymous 16.04.2015 / 15:21

2 answers


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

This way you can navigate using

$sql = "Select * From Tabela";
$statement = $db->prepare($sql, array(
$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 the documentation and pre-defined settings .

With the functions shown 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 it to the mode used in your project.

16.04.2015 / 19:23

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

  • current() - Return the current element in an array
  • each() - Returns the current key / value pair of an array and advances its cursor
  • reset() - Makes the internal pointer of an array point to its first element
  • prev() - Rewind 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. The PDO is only an abstraction layer for the connection with the database.

Maybe what you're looking for is some ORM .

16.04.2015 / 15:59