Use PDO with prepared statements and transactions to prevent SQL injection and ensure data atomicity across multiple operations. 2. Apply the Repository Pattern to abstract database logic from business logic, improving testability and maintainability. 3. Choose Active Record for simple applications needing rapid development, and Data Mapper for complex domains requiring clear separation of concerns. 4. Utilize a Query Builder to construct dynamic SQL queries safely and readably without string concatenation. 5. Implement caching with tools like Redis or Memcached to reduce database load by storing frequently accessed query results. 6. Employ persistent connections cautiously for connection reuse and use lazy loading to defer database access until necessary. 7. Use ORMs like Doctrine or Eloquent wisely, avoiding performance pitfalls such as N 1 queries through eager loading and partial data fetching.

When working with PHP and databases, moving beyond basic CRUD operations opens the door to more robust, scalable, and maintainable applications. Advanced database interaction patterns help manage complexity, improve performance, and ensure data integrity. Below are key patterns and techniques used in modern PHP applications for sophisticated database handling.

1. Using PDO with Prepared Statements and Transactions

While MySQLi and raw SQL queries are common, PDO (PHP Data Objects) provides a consistent interface for multiple databases and supports advanced features like prepared statements and transactions—critical for security and reliability.

try {
    $pdo = new PDO('mysql:host=localhost;dbname=myapp', $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Begin transaction
    $pdo->beginTransaction();

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

    $userId = $pdo->lastInsertId();

    $stmt = $pdo->prepare("INSERT INTO profiles (user_id, bio) VALUES (?, ?)");
    $stmt->execute([$userId, 'Software Developer']);

    // Commit if all succeed
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollback();
    throw $e;
}

Why it matters:

• Prevents SQL injection via parameterized queries.
• Ensures atomicity: either both inserts succeed, or none do.
• Works across different database systems (MySQL, PostgreSQL, SQLite).

2. Repository Pattern for Abstraction

The Repository Pattern decouples business logic from database logic by creating an abstraction layer. This makes code more testable and easier to maintain.

interface UserRepositoryInterface {
    public function findById(int $id): ?User;
    public function findByEmail(string $email): ?User;
    public function save(User $user): void;
}

class PdoUserRepository implements UserRepositoryInterface {
    private $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }

    public function findById(int $id): ?User {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE id = ?");
        $stmt->execute([$id]);
        $data = $stmt->fetch();

        return $data ? new User($data['id'], $data['name'], $data['email']) : null;
    }

    public function save(User $user): void {
        if ($user->getId()) {
            // Update
            $stmt = $this->pdo->prepare("UPDATE users SET name = ?, email = ? WHERE id = ?");
            $stmt->execute([$user->getName(), $user->getEmail(), $user->getId()]);
        } else {
            // Insert
            $stmt = $this->pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
            $stmt->execute([$user->getName(), $user->getEmail()]);
            $user->setId((int)$this->pdo->lastInsertId());
        }
    }
}

Benefits:

• Switch data sources easily (e.g., from MySQL to MongoDB or in-memory arrays for testing).
• Centralizes query logic.
• Promotes single responsibility and dependency injection.

3. Active Record vs Data Mapper

Two common ORM-like patterns:

• Active Record: Each object wraps a database row and includes persistence methods (e.g., $user->save()). Simple but couples domain and data layers.
• Data Mapper: Separates domain objects from persistence logic. More complex but cleaner for large apps.

Example (simplified Active Record):

class User {
    public function save() {
        if ($this->id) {
            // UPDATE
        } else {
            // INSERT
        }
    }
}

Use Active Record for:

• Small to medium applications.
• Rapid prototyping.

Use Data Mapper (or Repository) for:

• Complex domains.
• Applications requiring testability and separation of concerns.

Popular tools like Eloquent (Laravel) use Active Record, while Doctrine supports both patterns, favoring Data Mapper.

4. Query Builder for Dynamic Queries

Writing raw SQL for dynamic conditions (e.g., filters, search) can become messy. A Query Builder helps construct SQL safely and fluently.

Using Doctrine DBAL QueryBuilder:

$qb = $connection->createQueryBuilder();
$qb->select('u.name', 'u.email')
   ->from('users', 'u')
   ->where('u.active = :active')
   ->andWhere('u.created_at > :date')
   ->setParameter('active', true)
   ->setParameter('date', '2023-01-01');

$result = $qb->executeQuery()->fetchAllAssociative();

Advantages:

• Avoids SQL string concatenation.
• Handles parameter binding automatically.
• Improves readability and maintainability.

5. Caching Query Results

Frequent database queries can slow down applications. Use caching (e.g., Redis, Memcached) to store expensive query results.

$cacheKey = 'user_profile_' . $userId;
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);

$cached = $redis->get($cacheKey);
if ($cached) {
    $profile = json_decode($cached, true);
} else {
    $stmt = $pdo->prepare("SELECT * FROM profiles WHERE user_id = ?");
    $stmt->execute([$userId]);
    $profile = $stmt->fetch();

    $redis->setex($cacheKey, 3600, json_encode($profile)); // Cache 1 hour
}

Best practices:

• Cache read-heavy, infrequently changed data.
• Use cache invalidation strategies (e.g., on update/delete).
• Consider cache stampede protection (e.g., random TTL jitter).

6. Connection Pooling and Lazy Loading

PHP traditionally doesn’t support persistent connections well due to its request-per-process model, but you can:

• Use persistent PDO connections cautiously:

  $pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_PERSISTENT => true]);

• Implement lazy loading in repositories to defer database calls until needed.

Note: Persistent connections can cause issues (e.g., locks, stale states), so monitor carefully.

7. Using ORM Wisely: Doctrine or Eloquent

While ORMs simplify development, misuse can lead to performance issues (e.g., N 1 queries).

Avoid N 1 problem:

// BAD: Loads posts, then queries for each author
$posts = $repo->findAll();
foreach ($posts as $post) {
    echo $post->getAuthor()->getName(); // Triggers new query each time
}

// GOOD: Eager load authors
$posts = $repo->findAllWithAuthors();

Tips:

• Use eager loading for related data.
• Fetch partial data with DTOs when full entities aren’t needed.
• Profile queries using tools like Symfony Profiler or Laravel Telescope.

Final Thoughts

Advanced database patterns in PHP revolve around:

• Separation of concerns (Repository, Data Mapper).
• Security and consistency (PDO, transactions).
• Performance (caching, query builders, ORMs used wisely).

You don’t need all patterns at once. Start with prepared statements and transactions, then introduce abstraction layers as your app grows.

Basically, write code that’s safe today and adaptable tomorrow.

The above is the detailed content of Advanced Database Interaction Patterns in PHP. For more information, please follow other related articles on the PHP Chinese website!