PDO PHP Fetch Class

Solution 1:

This means that when using PDO to return a result into a custom object, you are required to set out the member variables which correspond to the query result keys.

such as:

class User
{
    //Predefine Here
    public $id;
    public $username;
    public $password;
    public $email;
    public $hash;

    public function profileLink()
    {
         return sprintf('<a href="/profile/%s">%s</a>',$this->id,$this->username);
    }
}

$result = $sth->fetchAll(PDO::FETCH_CLASS, "User");
foreach($result as $user)
{
    echo $user->profileLink();
}

This way PDO can set the variables to the object outside of its internal scope.

if you user class was like so:

class User
{
}

then PDO Would not be able to set the values from outside the scope, as there are no properties defined.

Solution 2:

Say you have this snippit of code

<?php
class Foo {
    public $bar;

    public function __construct()
    {
        $this->bar = 1;
    }
}

$stmt = $dbh->prepare("SELECT bar FROM foo");
$stmt->setFetchMode(PDO::FETCH_CLASS, 'Foo'); 
$stmt->execute();
$obj = $stmt->fetch()
?>

The bar propery for $obj will be set to "1" not what is retreived from the database.

If you would like it to be set to the result from the database instead of "1" you can change the fetch mode to

$stmt->setFetchMode(PDO::FETCH_CLASS|PDO::FETCH_PROPS_LATE, 'Foo'); 

This causes the constructor to be called before assigning the results to the properties

Solution 3:

Instead of using: FetchAll(PDO::FETCH_CLASS, 'classname') You can use: fetchObject('classname')

Example:

class Car extends DatabaseTable {

const TABLE_NAME = 'cars';

// Table Columns
private $color;
private $brand;
private $type;

public function __construct($pdo, $id = false)
{
    parent::__construct($pdo, TABLE_NAME, $id);
}

public static function getAll($pdo, $order = 'id') {
    $query =   'SELECT * FROM ' . self::TABLE_NAME . ' 
                ORDER BY ' . $order;

    $statement = $pdo->prepare($query);

    $objects = [];
    while ($obj = $statement->fetchObject(self::class, [$pdo])) {
        $objects[$obj->getId()] = $obj;
    }
    return $objects;
}

The parent Constructor just sets its 3 properties like: $this->id = $id;

Solution 4:

Looking back at PDO::FETCH_CLASS, you don't really need to define the variables as public nor private (as of PHP 7.0 ) , you could define an empty class and PHP PDO will populate the attributes as $Class->attribute even if they are not defined.

this is very useful because you can reuse classes with multiple queries that treat the same table but might return different columns