Doctrine DBAL 2: fetchAll() unnecessary array dimensions

Question

In doctrine DBAL2 when I execute a query like this:

<?php
$connection = $this->getDatabaseConnection();

$sql =  "SELECT page_url
           FROM cms_user_page
          WHERE site_id = :siteid
            AND active = '1'
    ";

$stmt = $connection->prepare($sql);
$stmt->bindValue("siteid", $id);
$stmt->execute(); 

return $stmt->fetchAll();
?>

I get a result like this:

Array
(
    [0] => Array
        (
            [page_url] => index.php?action=login
        )

    [1] => Array
        (
            [page_url] => index.php?action=shoppingcart
        )

    [2] => Array
        (
            [page_url] => index.php?action=products
        )
)

My question is, is there a fetch mode that produces an result like this:

Array
(
    [0] => index.php?action=login

    [1] => index.php?action=shoppingcart

    [2] => index.php?action=products
)

I could not find any info about fetch modes in the documentation. and i could do an array map. But thats overhead in my opinion..

Coston

Answer 1

You can pass a fetch mode parameter to fetchAll().

$stmt->fetchAll(\PDO::FETCH_COLUMN)

Contaminant

While this may answer the question it’s always a good idea to put some text in your answer to explain what you're doing. Read how to write a good answer. – Squabble

Answer 2

This answer has been edited because this answer is correct.

You can use the FETCH_COLUMN fetch mode in fetchAll():

$stmt->fetchAll(\PDO::FETCH_COLUMN)

As another user points out in the comments, fetchAll() can return data formatted in a multitude of interesting formats.

Or you can iterate with fetchColumn():

while($page_url = $stmt->fetchColumn()) { 
    echo $page_url . PHP_EOL;
}

Glomerulus

Answer 3

As of PHP5.5 you could use aray_column to achieve required result like so:

<?php
$connection = $this->getDatabaseConnection();

$sql =  "SELECT page_url
           FROM cms_user_page
          WHERE site_id = :siteid
            AND active = '1'
    ";

$stmt = $connection->prepare($sql);
$stmt->bindValue("siteid", $id);
$stmt->execute(); 
$data = array_column($stmt->fetchAll(), 'page_url');

return $data;

Pokelogan

Also handy if you use the DBAL $arr = array_column($db->fetchAll('SELECT id FROM foo'), 'id') syntax. – Whim

Answer 4

It looks like fetchAll was made deprecated in a recent version of DBAL. However I found another method called fetchFirstColumn which appears to do the same thing as fetchAll(\PDO::FETCH_COLUMN). I am currently on version 2.11.1 Doctrine DBAL.

I am using it like this in Symfony:

$statement = $connection->prepare('SELECT foo FROM bar WHERE baz = :baz');
$statement->execute([':baz' => 1]);
$result = $statement->fetchFirstColumn();

The value of $result will be a numerically indexed array starting at 0 like this:

[
    0 => 'foo1', 
    1 => 'foo2'
];

Eugine

Answer 5

If you have more than one case where you need that form of result, although i dont really understand the sense too, you could implement AbstractHydrator interface to create your own ArrayHydrator that returns the structure as you need it.

Hydration Classes reside in NS:

Doctrine\ORM\Internal\Hydration

Interatomic

Answer 6

As soon as you're requesting multiple rows in a database it does not make sense.

RDBMS stores rows and columns so the result is represented as rows and columns.

In the programming world it is called a matrix, in the PHP world it is an array.

 ________________
| id   |   name  |
|______|_________|
| 1    |   foo   |
|______|_________|
| 2    |   bar   |
|______|_________|

will results in

array(
  0 => array(
     'id'   => 1,
     'name' => 'foo',
  ),
  1 => array(
     'id'   => 2,
     'name' => 'foo',
  )
);

So no, you can't do that, you'd rather to process the result to fit your needs.

Dragoman