SQL result has field and index

3

I have a function in PHP to automate the queries I make:

//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 a sql operation I just call the function like this:

//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 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
    )
)

I always return a int field with the result repeated.

The current way I use to manipulate the data and return it to the desired end array is by using the combination of foreach and checking whether the type is int and, if not, add to a new array , like this:

$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 the final array :

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 query results that have type int , returning only fields and values?

    
asked by anonymous 28.07.2016 / 15:05

2 answers

4

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

Solution

To return an associative array specify the PDO::FETCH_ASSOC constant, this can be done ( in line ) in call 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 for 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 DB with PHP?

Manual - remaining in the PDO

    
28.07.2016 / 15:11
5

You do not need to do the foreach, just change the following line:

$q->fetchAll();

By:

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

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

To complement, if you want them as Object:

$q->fetchAll(PDO::FETCH_OBJ);
    
28.07.2016 / 15:10