Result SQL has field and index

Question

I have a PHP function to automate the queries I do:

//arquivo funcao.php
function executaSql($sql, $param=array()){
    try {
        include "config.php";
        $q = $conn->prepare($sql);
        $q->execute($param);
        $response = $q->fetchAll();
        if ( count($response) ) { 
            return $response;
        } else {
            return 0;
        }
    } catch(PDOException $e) {
        return 'ERROR: ' . $e->getMessage();
    }
}

And then whenever I need to do an operation with sql I just call the function so:

//arquivo consulta.php
$sql = executaSql("SELECT * FROM cadastro_produto");
$sql = executaSql("SELECT * FROM cadastro_categoria");

//e assim por diante
//Os métodos de INSERT, UPDATE, DELETE, etc.. São gerenciados por outra função para verificação de erros

But the result I get is always something like this:

Array (
    [0] => Array (
        [nome_categoria] => Painel
        [0] => Painel
        [tipo_categoria] => 3
        [1] => 3
    )
    [1] => Array (
        [nome_categoria] => Chapa
        [0] => Chapa
        [tipo_categoria] => 7
        [1] => 7
    )
)

A field always returns to me int with the repeated result.

The current mode I use to manipulate the data and return to array desired end is using the combination of foreach and check if the type is int and, if not, add to a new array, thus:

$data = array();
$i=0;
foreach($sql as $row) {
    foreach ($row as $key => $value) {
        if(!is_int($key)){
            $data[$i][$key] = $value;
        }
    }
    $i++;
}

After all this process I get to array final:

Array (
    [0] => Array (
        [nome_categoria] => Painel
        [tipo_categoria] => 3
    )
    [1] => Array (
        [nome_categoria] => Chapa
        [tipo_categoria] => 7
    )
)

So my question is: Is there any way to automate this process by removing the query results that have the type int, returning only fields and values?

Answer 1

No need to foreach, just change the following line:

$q->fetchAll();

For:

$q->fetchAll(PDO::FETCH_ASSOC);

So defines that you want the rows to be returned as an array.

To complement, if you want them as an object:

$q->fetchAll(PDO::FETCH_OBJ);

Answer 2

This duplication of information happens because no return format has been defined, PDO::FETCH_BOTH is the default value, it means the return of fetch()/fetchAll() will be an array where the indexes will be the column names and their positions (which are determined in select). Curiosity: the function mysqli_fetch_array() works in the same way, to avoid expense specify a format! :).

Solution

To return an associative array specify the constant PDO::FETCH_ASSOC, this can be done(in line) in the call for fetch()/fetchAll() or direct global form on the connection, specifying the fourth argument, which is an array of settings in this case just specify a value to PDO::ATTR_DEFAULT_FETCH_MODE.

<?php
   $opcoes = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
   $db = new PDO('mysql:host=localhost;dbname=catalogo', 'usuario', 'senha', $opcoes);

Related:

Using PDO is the safest way to connect to a PHP BD?

Manual - other PDO constants