What are some common pitfalls when trying to separate database logic in PHP?

One common pitfall when trying to separate database logic in PHP is mixing database queries with business logic in the same file, making the code harder to maintain and debug. To solve this, it's recommended to use a separate class or layer for handling database operations, such as a Data Access Object (DAO) or Object-Relational Mapping (ORM) framework. 

// Example of separating database logic using a DAO class

class UserDao {
    private $db;

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

    public function getUserById($userId) {
        $query = "SELECT * FROM users WHERE id = :id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':id', $userId);
        $stmt->execute();
        return $stmt->fetch();
    }

    public function updateUserEmail($userId, $newEmail) {
        $query = "UPDATE users SET email = :email WHERE id = :id";
        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':email', $newEmail);
        $stmt->bindParam(':id', $userId);
        $stmt->execute();
    }
}

// Implementation example
$db = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
$userDao = new UserDao($db);
$user = $userDao->getUserById(1);
$userDao->updateUserEmail(1, 'newemail@example.com');

Keywords

SQL injection ORM PDO prepared statements database abstraction layer

Related Questions