How can a Singleton SQL class be effectively implemented in PHP to handle database connections and query execution using PDO?

Implementing a Singleton SQL class in PHP using PDO can help manage database connections efficiently by ensuring only one instance of the class is created and used throughout the application. This can prevent multiple connections to the database, improve performance, and simplify code maintenance. 

class Database {
    private static $instance = null;
    private $connection;

    private function __construct() {
        $dsn = 'mysql:host=localhost;dbname=mydatabase';
        $username = 'username';
        $password = 'password';
        $this->connection = new PDO($dsn, $username, $password);
        $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    public static function getInstance() {
        if (self::$instance == null) {
            self::$instance = new Database();
        }
        return self::$instance;
    }

    public function query($sql) {
        return $this->connection->query($sql);
    }

    public function prepare($sql) {
        return $this->connection->prepare($sql);
    }
}

// Example usage:
$db = Database::getInstance();
$stmt = $db->prepare('SELECT * FROM users');
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

Keywords

Singleton SQL class PHP PDO database connections

Related Questions