What are the best practices for handling database connections in PHP scripts, especially when using MySQLi functions?

When handling database connections in PHP scripts, especially when using MySQLi functions, it is important to establish a connection to the database, execute queries, and close the connection properly to prevent resource leaks and improve performance. One common best practice is to use object-oriented programming (OOP) to create a database connection class that can be reused throughout the script. 

<?php
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 close() {
        $this->conn->close();
    }
}

// Example of using the Database class
$db = new Database();
$result = $db->query("SELECT * FROM users");
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Name: " . $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}
$db->close();
?>

Keywords

PHP MySQLi database connection error handling security

Related Questions