MFormations
Modern PHP Engineering

Chapitre 5

05 — Bases de Données PHP

05 — Bases de Données PHP

Cours 05 — Bases de Données PHP

05.1 PDO — PHP Data Objects

PDO est l'extension native PHP pour l'accès aux bases de données. Elle supporte MySQL, PostgreSQL, SQLite, Oracle, MSSQL, etc.

Connexion

<?php

declare(strict_types=1);

// Connexion MySQL
$dsn = 'mysql:host=localhost;port=3306;dbname=app_db;charset=utf8mb4';
$username = 'root';
$password = 'secret';
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
];

$pdo = new PDO($dsn, $username, $password, $options);

// Connexion PostgreSQL
$dsn = 'pgsql:host=localhost;port=5432;dbname=app_db';
$pdo = new PDO($dsn, $username, $password, $options);

// Connexion SQLite
$pdo = new PDO('sqlite:/path/to/database.sqlite');

Prepared statements

<?php

declare(strict_types=1);

// SELECT avec paramètres nommés
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND active = :active');
$stmt->execute(['email' => 'alice@example.com', 'active' => true]);
$user = $stmt->fetch(); // false si pas de résultat

// SELECT avec paramètres positionnels
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([42]);
$user = $stmt->fetch();

// Fetch modes
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);           // Tableau associatif
$user = $stmt->fetch(PDO::FETCH_OBJ);                  // Objet stdClass
$user = $stmt->fetch(PDO::FETCH_CLASS, User::class);   // Hydratation en classe
$count = $stmt->fetchColumn();                         // Une seule colonne
$pairs = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);          // [key => value]

// INSERT
$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$stmt->execute(['name' => 'Alice', 'email' => 'alice@example.com']);
$lastId = $pdo->lastInsertId();

// UPDATE
$stmt = $pdo->prepare('UPDATE users SET name = :name WHERE id = :id');
$stmt->execute(['name' => 'Alice Updated', 'id' => 1]);
$affected = $stmt->rowCount(); // Nombre de lignes affectées

// DELETE
$stmt = $pdo->prepare('DELETE FROM users WHERE id = :id');
$stmt->execute(['id' => 1]);

Transactions

<?php

declare(strict_types=1);

try {
    $pdo->beginTransaction();

    $stmt = $pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE id = :from');
    $stmt->execute(['amount' => 100, 'from' => 1]);

    $stmt = $pdo->prepare('UPDATE accounts SET balance = balance + :amount WHERE id = :to');
    $stmt->execute(['amount' => 100, 'to' => 2]);

    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
    log_error("Transaction échouée : " . $e->getMessage());
    throw $e;
}

Error modes

<?php

// Mode silencieux (défaut) — vérifier errorCode()
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$stmt = $pdo->query('SELECT *');
if ($stmt === false) {
    $error = $pdo->errorInfo();
}

// Mode exception (RECOMMANDÉ)
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
    $pdo->query('SELECT *');
} catch (PDOException $e) {
    echo $e->getMessage(); // "SQLSTATE[42000]: ..."
}

// Mode warning
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

Repository pattern avec PDO

<?php

declare(strict_types=1);

class UserRepository
{
    public function __construct(
        private readonly PDO $pdo,
    ) {}

    public function findById(int $id): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM users WHERE id = ?');
        $stmt->execute([$id]);
        $result = $stmt->fetch();
        return $result ?: null;
    }

    public function findByEmail(string $email): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM users WHERE email = ?');
        $stmt->execute([$email]);
        $result = $stmt->fetch();
        return $result ?: null;
    }

    public function findAll(int $page = 1, int $perPage = 20): array
    {
        $offset = ($page - 1) * $perPage;
        $stmt = $this->pdo->prepare(
            'SELECT * FROM users ORDER BY id LIMIT ? OFFSET ?'
        );
        $stmt->execute([$perPage, $offset]);
        return $stmt->fetchAll();
    }

    public function create(array $data): int
    {
        $columns = implode(', ', array_keys($data));
        $placeholders = ':' . implode(', :', array_keys($data));

        $stmt = $this->pdo->prepare(
            "INSERT INTO users ($columns) VALUES ($placeholders)"
        );
        $stmt->execute($data);
        return (int) $this->pdo->lastInsertId();
    }

    public function update(int $id, array $data): bool
    {
        $sets = implode(', ', array_map(
            fn(string $col): string => "$col = :$col",
            array_keys($data)
        ));

        $stmt = $this->pdo->prepare(
            "UPDATE users SET $sets WHERE id = :id"
        );
        $stmt->execute([...$data, 'id' => $id]);
        return $stmt->rowCount() > 0;
    }

    public function delete(int $id): bool
    {
        $stmt = $this->pdo->prepare('DELETE FROM users WHERE id = ?');
        $stmt->execute([$id]);
        return $stmt->rowCount() > 0;
    }
}

05.2 Eloquent ORM

Eloquent est l'ORM de Laravel. Il suit le pattern Active Record.

Modèles

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class User extends Model
{
    use SoftDeletes; // Suppression logique (deleted_at)

    protected $table = 'users';
    protected $primaryKey = 'id';
    public $timestamps = true; // created_at, updated_at

    protected $fillable = [
        'name', 'email', 'password', 'role_id',
    ];

    protected $hidden = [
        'password', 'remember_token',
    ];

    protected $casts = [
        'email_verified_at' => 'datetime',
        'is_admin' => 'boolean',
        'settings' => 'array',   // JSON → array PHP
        'options' => 'collection',
    ];
}

// Utilisation
$user = new User();
$user->name = 'Alice';
$user->email = 'alice@example.com';
$user->save();

User::create([
    'name' => 'Bob',
    'email' => 'bob@example.com',
]);

$user = User::find(1);
$user->update(['name' => 'Alice Updated']);
$user->delete();

$users = User::where('active', true)
    ->where('role_id', '>', 1)
    ->orderBy('name')
    ->paginate(20);

Relations

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations;

class User extends Model
{
    // Une-à-un
    public function profile(): Relations\HasOne
    {
        return $this->hasOne(Profile::class);
    }

    // Une-à-plusieurs
    public function posts(): Relations\HasMany
    {
        return $this->hasMany(Post::class);
    }

    // Appartient-à
    public function role(): Relations\BelongsTo
    {
        return $this->belongsTo(Role::class);
    }

    // Plusieurs-à-plusieurs
    public function roles(): Relations\BelongsToMany
    {
        return $this->belongsToMany(Role::class)
            ->withPivot('assigned_at')
            ->withTimestamps();
    }

    // HasManyThrough
    public function comments(): Relations\HasManyThrough
    {
        return $this->hasManyThrough(Comment::class, Post::class);
    }

    // Morph (polymorphique)
    public function images(): Relations\MorphMany
    {
        return $this->morphMany(Image::class, 'imageable');
    }
}

// Chargement
$user = User::with('posts', 'profile')->find(1);
$user->load('roles');

// Chargement conditionnel
$user = User::with(['posts' => fn($q) => $q->where('published', true)])->find(1);

// Eager loading par défaut
class User extends Model {
    protected $with = ['profile'];
}

Scopes

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;

class User extends Model
{
    // Scope local
    public function scopeActive(Builder $query): Builder
    {
        return $query->where('active', true);
    }

    public function scopeByRole(Builder $query, string $role): Builder
    {
        return $query->whereHas('role', fn($q) => $q->where('name', $role));
    }

    public function scopeRecentlyCreated(Builder $query): Builder
    {
        return $query->where('created_at', '>=', now()->subDays(7));
    }
}

// Utilisation
$admins = User::active()
    ->byRole('admin')
    ->recentlyCreated()
    ->get();

Accessors et Mutators

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Casts\Attribute;

class User extends Model
{
    // Accessor (lecture)
    public function getNameAttribute(string $value): string
    {
        return ucfirst($value);
    }

    // Mutator (écriture)
    public function setPasswordAttribute(string $value): void
    {
        $this->attributes['password'] = bcrypt($value);
    }

    // Accessor/Mutator moderne (PHP 8+)
    protected function fullName(): Attribute
    {
        return Attribute::make(
            get: fn(mixed $value, array $attributes) => 
                "{$attributes['first_name']} {$attributes['last_name']}",
            set: fn(string $value) => [
                'first_name' => explode(' ', $value)[0],
                'last_name' => explode(' ', $value)[1] ?? '',
            ],
        );
    }
}

// Utilisation
$user = User::find(1);
echo $user->full_name; // Accessor

$user->full_name = 'Alice Martin'; // Mutator
$user->save();

Events

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    protected $dispatchesEvents = [
        'created' => UserCreated::class,
        'updated' => UserUpdated::class,
        'deleted' => UserDeleted::class,
    ];

    // Ou avec les boot methods
    protected static function booted(): void
    {
        static::creating(function (User $user) {
            $user->uuid = (string) str()->uuid();
        });

        static::created(function (User $user) {
            Log::info('Utilisateur créé', ['id' => $user->id]);
        });
    }
}

Factories

<?php

namespace Database\Factories;

use App\Models\User;
use Illuminate\Database\Eloquent\Factories\Factory;

class UserFactory extends Factory
{
    protected $model = User::class;

    public function definition(): array
    {
        return [
            'name' => fake()->name(),
            'email' => fake()->unique()->safeEmail(),
            'password' => bcrypt('password'),
            'active' => fake()->boolean(80),
        ];
    }

    public function admin(): static
    {
        return $this->state(fn(array $attributes) => [
            'role_id' => Role::where('name', 'admin')->first()->id,
        ]);
    }
}

// Utilisation
User::factory()->count(10)->create();
User::factory()->admin()->create();

05.3 Doctrine ORM

Doctrine est l'ORM de Symfony. Il suit le pattern Data Mapper.

Entités

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: UserRepository::class)]
#[ORM\Table(name: 'users')]
class User
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private int $id;

    #[ORM\Column(type: 'string', length: 255)]
    private string $name;

    #[ORM\Column(type: 'string', length: 255, unique: true)]
    private string $email;

    #[ORM\Column(type: 'datetime_immutable')]
    private \DateTimeImmutable $createdAt;

    #[ORM\ManyToOne(targetEntity: Role::class, inversedBy: 'users')]
    private ?Role $role = null;

    public function __construct()
    {
        $this->createdAt = new \DateTimeImmutable();
    }

    // Getters/Setters...
}

DQL (Doctrine Query Language)

<?php

use App\Entity\User;
use Doctrine\ORM\EntityManagerInterface;

class UserRepository
{
    public function __construct(
        private readonly EntityManagerInterface $em,
    ) {}

    public function findActiveAdmins(): array
    {
        $dql = 'SELECT u FROM App\Entity\User u
                JOIN u.role r
                WHERE r.name = :roleName
                AND u.active = :active
                ORDER BY u.name ASC';

        $query = $this->em->createQuery($dql);
        $query->setParameters([
            'roleName' => 'admin',
            'active' => true,
        ]);

        return $query->getResult();
    }

    public function findRecentUsers(int $days = 7): array
    {
        $qb = $this->em->createQueryBuilder();
        $qb->select('u')
           ->from(User::class, 'u')
           ->where('u.createdAt >= :date')
           ->setParameter('date', new \DateTimeImmutable("-{$days} days"))
           ->orderBy('u.createdAt', 'DESC');

        return $qb->getQuery()->getResult();
    }
}

Migrations Doctrine

# Générer une migration
php bin/console doctrine:migrations:diff

# Exécuter les migrations
php bin/console doctrine:migrations:migrate

# Statut
php bin/console doctrine:migrations:status

# Rollback
php bin/console doctrine:migrations:migrate prev

05.4 Migrations

Phinx

<?php

// db/migrations/20250729000001_create_users.php

use Phinx\Migration\AbstractMigration;

class CreateUsers extends AbstractMigration
{
    public function up(): void
    {
        $table = $this->table('users', ['id' => false, 'primary_key' => ['id']]);
        $table->addColumn('id', 'biginteger', ['identity' => true])
              ->addColumn('name', 'string', ['limit' => 255])
              ->addColumn('email', 'string', ['limit' => 255])
              ->addColumn('password', 'string', ['limit' => 255])
              ->addColumn('active', 'boolean', ['default' => true])
              ->addColumn('created_at', 'datetime')
              ->addColumn('updated_at', 'datetime')
              ->addIndex(['email'], ['unique' => true])
              ->create();
    }

    public function down(): void
    {
        $this->table('users')->drop()->save();
    }
}

Laravel Migrations

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->boolean('active')->default(true);
            $table->foreignId('role_id')->constrained();
            $table->timestamps();
            $table->softDeletes();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('users');
    }
};

05.5 Redis avec PHP

Prédi (PHP pur)

composer require predis/predis

<?php

use Predis\Client as Redis;

$redis = new Redis([
    'scheme' => 'tcp',
    'host' => '127.0.0.1',
    'port' => 6379,
    'password' => null,
    'database' => 0,
]);

// String
$redis->set('user:1:name', 'Alice');
echo $redis->get('user:1:name');

// Avec TTL
$redis->setex('session:token', 3600, 'abc123');

// Lists
$redis->lpush('queue:emails', 'email1', 'email2');
$email = $redis->rpop('queue:emails');

// Sets
$redis->sadd('user:1:roles', 'admin', 'editor');
$roles = $redis->smembers('user:1:roles');

// Hashes
$redis->hmset('user:1', ['name' => 'Alice', 'email' => 'alice@example.com']);
echo $redis->hget('user:1', 'name');

// Sorted sets
$redis->zadd('leaderboard', 100, 'user:1');
$top = $redis->zrevrange('leaderboard', 0, 9, 'WITHSCORES');

// Cache pattern
$cacheKey = "user:{$id}";
$user = $redis->get($cacheKey);

if (!$user) {
    $user = $database->find($id);
    $redis->setex($cacheKey, 3600, serialize($user));
} else {
    $user = unserialize($user);
}

PhpRedis (extension C)

<?php

// Plus performant que predis (extension native)
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);
$redis->auth('password');

$redis->set('key', 'value');
$redis->setex('key', 3600, 'value');

Exercices

  1. Créez un repository PDO avec pagination et filtres
  2. Modélisez des relations Eloquent (User, Post, Comment, Tag)
  3. Écrivez une requête Doctrine DQL complexe avec jointures
  4. Créez une migration Laravel avec index et foreign keys
  5. Implémentez un cache Redis pour les résultats de requêtes

Références

  • php.net/manual/fr/book.pdo.php
  • laravel.com/docs/eloquent
  • doctrine-project.org
  • redis.io/documentation
  • book.cakephp.org/phinx
PrécédentChapitre 4 — 04 — Composer et l'Écosystème PHPSuivantChapitre 6 — 06 — Symfony