PHP PDO mySQL query returns column names and integers

Question

My PDO query of the database returns unnecessary values into the array.

Array
(
    [contentID] => 9
    [0] => 9
    [type] => operations
    [1] => operations
    [type_alts] => pages
    [2] => pages
    [url] => ctt-partners
    [3] => ctt-partners
    [title] => CTT Partners
    [4] => CTT Partners
    [subtitle] => 
    [5] => 
    [online] => 1
    [6] => 1
    [access] => 0
    [7] => 0
    [req] => 0
    [8] => 0

)

I am after the array not returning the identical integer fields as well as the names. For example [0] => 9, [1] => operations. I don't want these as well.

Why are they here and how can I get rid of them.

Thanks,

Answer 1

Your current fetch type must be :

PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set

Whereas for your requirement, it should be:

PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set

fetch_style

Controls how the next row will be returned to the caller. This value must be one of the PDO::FETCH_* constants, defaulting to value of PDO::ATTR_DEFAULT_FETCH_MODE (which defaults to PDO::FETCH_BOTH).

Reference:

Answer 2

So just force PDO to do that ...

try {
    $pdo = new PDO(...);
    $pdo->query('SELECT * FROM foo');

    foreach ($pdo->query('SELECT * FROM foo', PDO::FETCH_ASSOC) as $row) {
        ...
    }
} catch (PDOException $e) {
    // error handling
}

Just have a look at the different PDO fetch modes. http://www.php.net/manual/en/pdostatement.setfetchmode.php

Answer 3

Have a look at the documentation of PDOStatement::fetch():

The first parameter defines how the next row will be returned to the caller. There are pre-defined constants available:

◦ PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set

◦ PDO::FETCH_BOTH (default): returns an array indexed by both column name and 0-indexed column number as returned in your result set

...
— http://www.php.net/manual/pdostatement.fetch.php

You probably call the function with FETCH_BOTH or without any argument which defaults to FETCH_BOTH.

Change the fetch type to FETCH_ASSOC.