What are the best practices for handling database connections and queries in PHP classes?

When working with database connections and queries in PHP classes, it is important to follow best practices to ensure efficient and secure data handling. One common approach is to establish a separate database connection class that handles the connection logic, allowing for easy reuse and maintenance. Additionally, using prepared statements for SQL queries helps prevent SQL injection attacks and improves performance. 

class Database {
    private $host = 'localhost';
    private $username = 'root';
    private $password = '';
    private $dbname = 'mydatabase';
    private $conn;

    public function __construct() {
        $this->conn = new mysqli($this->host, $this->username, $this->password, $this->dbname);

        if ($this->conn->connect_error) {
            die("Connection failed: " . $this->conn->connect_error);
        }
    }

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

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

    public function close() {
        $this->conn->close();
    }
}

// Example usage
$db = new Database();
$result = $db->query("SELECT * FROM users");
while ($row = $result->fetch_assoc()) {
    // Handle each row
}
$db->close();

Keywords

PDO MySQLi prepared statements singleton pattern error handling

Related Questions