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..

Alex Andrienko · Accepted Answer · 2019-09-08T11:28:21.323

32

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

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

edited Sep 08 '19 at 11:28

answered Apr 16 '15 at 12:33

Alex Andrienko

506
5
4

8

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](http://stackoverflow.com/help/how-to-answer). – Jørgen R Apr 16 '15 at 13:30

score 9 · Answer 2 · edited Dec 22 '20 at 18:59

9

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;
}

edited Dec 22 '20 at 18:59

Cave Johnson

6,499
5
38
57

answered Dec 13 '12 at 22:34

timdev

61,857
6
82
92

score 4 · Answer 3 · answered Feb 11 '15 at 10:44

4

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;

answered Feb 11 '15 at 10:44

Strayobject

642
11
20

1

Also handy if you use the DBAL `$arr = array_column($db->fetchAll('SELECT id FROM foo'), 'id')` syntax. – Nate Beaty Sep 12 '19 at 21:30

score 2 · Answer 4 · answered Sep 20 '12 at 09:58

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

score 2 · Answer 5 · answered Dec 03 '20 at 16:02

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'
];

score 0 · Answer 6 · edited Mar 30 '22 at 09:52

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.

Doctrine DBAL 2: fetchAll() unnecessary array dimensions

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

6 Answers6