PDO: Fetch associative array

Matej Kuna aka Maťok 1 June 2017

I'm quite a fan of good old PDO. Backend of my applications is typically made by doctrine ORM. It means everything is generated because I'm a lazy bastard, but using ORM when I need several simple queries... Why? On the other hand when I need complex queries they cannot be done in ORM... Well, ORM is not best nor fast solution in every situation. I don't want to write diss on ORM, so hurray to learn something.

Fetch associative array

public function getArticles()
{
    $sql = 'SELECT * FROM article
            WHERE
            published_at < NOW()
            AND article_status_id = :published';

    $statement = $this->connection->prepare($sql);
    $statement->bindValue('published', 1, Type::INTEGER);
    $statement->execute();

    return $statement->fetchAll();
}

What if you need array keys same as primary key in DB? You can alter/create new array via foreach... or you can do little magic with PDO:

public function getArticles()
{
    $sql = 'SELECT * FROM article
            WHERE
            published_at < NOW()
            AND article_status_id = :published';

    $statement = $this->connection->prepare($sql);
    $statement->bindValue('published', 1, Type::INTEGER);
    $statement->execute();

    return $statement->fetchAll(\PDO::FETCH_ASSOC | \PDO::FETCH_GROUP | \PDO::FETCH_UNIQUE);
}

Each item (article) has one property less, because primary key (article_id) is now key in result set, hell yeah... that's what we wanted!