query()

public function query(string $sql, ?string $return_type = null): mixed

Description

Executes a raw SQL query without parameter binding. This method is for executing SQL statements that don't require external parameter values. Use query_bind() for queries that need parameter binding. The method supports returning results as objects or arrays, or returning null for non-SELECT queries.

Security Warning: This method does NOT use parameter binding. Never pass user input directly into the SQL string. For queries with user input, use query_bind() instead to prevent SQL injection.

Parameters

Parameter	Type	Description	Default	Required
sql	string	The raw SQL query to execute. Must be properly escaped if containing dynamic values.	-	Yes
return_type	string\|null	Result format: 'object' for objects, 'array' for associative arrays, or null for no return (non-SELECT queries).	null	No

Return Value

Type	Description
mixed	Array of objects if `$return_type === 'object'` Array of associative arrays if `$return_type === 'array'` null if `$return_type` is null or doesn't match 'object'/'array'

Example #1: SELECT Query Returning Objects

The code sample below demonstrates executing a SELECT query and returning results as objects.

PHP

$sql = "SELECT * FROM users WHERE age > 18 AND status = 'active' ORDER BY last_login DESC";
$users = $this->db->query($sql, 'object');

foreach ($users as $user) {
    echo $user->username . ' - Last login: ' . $user->last_login . '';
}

Example #2: SELECT Query Returning Arrays

The code sample below demonstrates executing a JOIN query and returning results as associative arrays.

PHP

$sql = "SELECT 
          o.id as order_id, 
          o.order_date, 
          c.name as customer_name,
          SUM(oi.quantity * oi.price) as total_amount
        FROM orders o
        JOIN customers c ON o.customer_id = c.id
        JOIN order_items oi ON o.id = oi.order_id
        WHERE o.order_date >= '2024-01-01'
        GROUP BY o.id
        ORDER BY total_amount DESC";

$orders = $this->db->query($sql, 'array');

foreach ($orders as $order) {
    echo "Order #" . $order['order_id'] . " - Customer: " . $order['customer_name'] . " - Total: $" . $order['total_amount'] . "";
}

Example #3: Non-SELECT Query (No Return)

The code sample below demonstrates executing a data modification query without returning results.

PHP

// Clean up expired sessions
$sql = "DELETE FROM sessions WHERE expires_at < NOW()";
$this->db->query($sql); // Returns null

// Update multiple records
$sql = "UPDATE products SET discount_price = price * 0.9 WHERE category_id = 3 AND stock_quantity > 0";
$this->db->query($sql); // Returns null

// Create a temporary table
$sql = "CREATE TEMPORARY TABLE temp_orders AS 
        SELECT customer_id, COUNT(*) as order_count 
        FROM orders 
        WHERE YEAR(order_date) = 2024 
        GROUP BY customer_id";
$this->db->query($sql); // Returns null

Example #4: Complex Query with Database Functions

The code sample below demonstrates executing a query using database-specific functions.

PHP

// Using MySQL date functions
$sql = "SELECT 
          DATE(created_at) as date_only,
          COUNT(*) as daily_count,
          AVG(amount) as average_amount
        FROM transactions
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
        GROUP BY DATE(created_at)
        ORDER BY date_only DESC";

$daily_stats = $this->db->query($sql, 'array');

foreach ($daily_stats as $day) {
    echo $day['date_only'] . ": " . $day['daily_count'] . " transactions, avg: $" . number_format($day['average_amount'], 2) . "";
}

Important Notes

The $sql parameter is required.
CRITICAL SECURITY: This method does NOT use parameter binding. Never concatenate user input directly into the SQL string.
For queries with dynamic values, use query_bind() instead for security.
Returns null for:
- Non-SELECT queries (INSERT, UPDATE, DELETE, etc.)
- When $return_type is null
- When $return_type is not 'object' or 'array'
Uses PDO::FETCH_OBJ for object returns (properties match column names).
Uses PDO::FETCH_ASSOC for array returns (associative arrays).
If debug mode is enabled, the SQL query will be displayed before execution.
Use this method only for:
- Static SQL queries (no user input)
- Queries using only database literals and functions
- Administrative operations with trusted data
- Complex queries that can't use the simpler helper methods
Avoid using for:
- Queries with any user-provided values
- Form input processing
- Search queries with user-entered terms
- URL parameter-based queries