Data returned in an array is duplicated

Question

I am running a SELECT in the database through a function and returning the result as a multidimensional array, where each primary index refers to a record, and the secondary indices are the database fields with the values.

Below is the assembly of the function return:

$resultado = $this->conn->query($sql);
if ($resultado->rowCount() > 0) {
   foreach($resultado as $chave => $valor){
      $retorno[$chave] = $valor;
   }
   return $retorno;
} else {
   return false;
}

When I take the return and execute one var_dump(), the result comes out like this:

array (size=3)
  0 => 
    array (size=4)
      'usu_id' => string '1' (length=1)
      0 => string '1' (length=1)
      'usu_nome' => string 'Administrador' (length=13)
      1 => string 'Administrador' (length=13)

The second dimension of the array is creating two indexes for the return: "0" and "usu_id", as well as "1" and "usu_name".

Is that correct? PHP does it anyway or is there an error in the way I’m assembling the return array?

Answer 1

By default the return of PDO queries is the PDO::FETCH_BOTH which returns duplicated data being an index with the field name and another numeric index.

You can define the data format in three ways:

In the constructor that affects all queries made by that connection.

$options = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
$pdo = new PDO('.....', $options);

In the method query() only the current query is affected.

$result = $pdo->query('select ....', PDO::FETCH_ASSOC);

And finally in the extraction method fetch()/fetchAll() where the format is passed as argument.

 $result = $pdo->query('select ....');
 $info = $result->fetchAll(PDO::FETCH_ASSOC);
 foreach($info as $item){
    echo $item['chave'] .'<br>';
 }

As only returns the data without any manipulation can give the return right in query() or in the fetch() if you want to add error handling.

$resultado = $this->conn->query($sql);
if ($resultado->rowCount() > 0) {
    return $resultado->fetchAll(PDO::FETCH_ASSOC);
} else {
   return $this->conn->errorInfo();
}