What are the best practices for handling SQL queries and result sets within a PHP class?

When handling SQL queries and result sets within a PHP class, it is important to properly sanitize user input to prevent SQL injection attacks, use prepared statements to improve performance and security, and handle errors gracefully to provide meaningful feedback to users. 

class DatabaseHandler {
    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);
        }
        
        $result = $statement->execute();
        
        if ($result === false) {
            die("Error executing query: " . $this->connection->error);
        }
        
        $resultSet = $statement->get_result();
        
        $data = [];
        while ($row = $resultSet->fetch_assoc()) {
            $data[] = $row;
        }
        
        $statement->close();
        
        return $data;
    }
}

Keywords

SQL queries result sets PHP class PDO error handling

Related Questions