What are the recommended ways to structure and organize database queries within PHP classes?

When structuring and organizing database queries within PHP classes, it is recommended to separate the database logic from the business logic by creating a separate class for database operations. This helps in maintaining a clean and organized codebase, making it easier to manage and update database queries in the future. Additionally, using prepared statements can help prevent SQL injection attacks and improve query performance. 

class Database {
    private $connection;

    public function __construct($host, $username, $password, $database) {
        $this->connection = new mysqli($host, $username, $password, $database);
        if ($this->connection->connect_error) {
            die("Connection failed: " . $this->connection->connect_error);
        }
    }

    public function executeQuery($sql, $params = []) {
        $statement = $this->connection->prepare($sql);
        if ($statement === false) {
            die("Error preparing query: " . $this->connection->error);
        }
        
        if (!empty($params)) {
            $types = str_repeat('s', count($params));
            $statement->bind_param($types, ...$params);
        }

        $statement->execute();
        $result = $statement->get_result();
        
        $rows = [];
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }

        $statement->close();
        return $rows;
    }
}

// Example of using the Database class
$database = new Database("localhost", "username", "password", "database_name");
$results = $database->executeQuery("SELECT * FROM users WHERE id = ?", [1]);
foreach ($results as $row) {
    echo $row['username'] . "<br>";
}

Keywords

SQL PDO OOP database design query optimization

Related Questions