☰

query_bind()

public function query_bind(string $sql, array $data, ?string $return_type = null): mixed

Description

Executes a raw SQL query with named parameter binding for maximum security. This is the required methodology for executing SQL queries with dynamic values in Trongate v2. It uses prepared statements with named parameters to prevent SQL injection attacks. Supports returning results as objects or arrays, or returning null for non-SELECT queries.

Parameters

Parameter Type Description Default Required
sql string The SQL query with named parameters (e.g., :name, :age). Question mark placeholders are not supported. - Yes
data array Associative array where keys match parameter names in the SQL query (without the colon). - 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'

Critical Requirement: Trongate v2 requires named parameters (e.g., :age, :city). Unnamed parameters using question mark placeholders (?) are not supported and will not work. This is a deliberate design decision to ensure code clarity and consistency across all Trongate projects.

Example #1: SELECT with Named Parameters

The code sample below demonstrates executing a SELECT query with named parameters and returning objects.

PHP
$sql = "SELECT * FROM users WHERE age > :min_age AND status = :status AND city = :city ORDER BY last_login DESC";
$params = [
    'min_age' => 18,
    'status' => 'active',
    'city' => 'London'
];

$users = $this->db->query_bind($sql, $params, 'object');

foreach ($users as $user) {
    echo $user->username . ' - Age: ' . $user->age . ' - City: ' . $user->city . '';
}

Example #2: Complex JOIN with Array Results

The code sample below demonstrates executing a complex JOIN query with date range parameters.

PHP
$sql = "SELECT 
          o.id as order_id,
          o.order_date,
          c.name as customer_name,
          COUNT(oi.id) as item_count,
          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 BETWEEN :start_date AND :end_date
          AND o.status = :status
        GROUP BY o.id
        HAVING total_amount > :min_total
        ORDER BY total_amount DESC";

$params = [
    'start_date' => '2024-01-01',
    'end_date' => '2024-01-31',
    'status' => 'completed',
    'min_total' => 100.00
];

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

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

Example #3: UPDATE with Parameter Binding

The code sample below demonstrates executing an UPDATE query with parameter binding.

PHP
// Update user's last login and increment login count
$sql = "UPDATE users SET last_login = :login_time, login_count = login_count + 1 WHERE id = :user_id";
$params = [
    'login_time' => date('Y-m-d H:i:s'),
    'user_id' => 123
];

$this->db->query_bind($sql, $params); // Returns null for UPDATE
echo "User login updated.";

Example #4: DELETE with Multiple Conditions

The code sample below demonstrates executing a DELETE query with multiple parameterized conditions.

PHP
// Delete expired sessions for a specific user agent
$sql = "DELETE FROM sessions WHERE expires_at < :now AND user_agent = :user_agent";
$params = [
    'now' => date('Y-m-d H:i:s'),
    'user_agent' => 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)'
];

$this->db->query_bind($sql, $params);
echo "Expired sessions cleaned up.";

Example #5: INSERT with Form Data Using Trongate's post()

The code sample below demonstrates executing an INSERT query with form data safely retrieved using Trongate's post() function.

PHP
// Safely retrieve and validate form data using Trongate's post() function
$comment_data = [
    'comment' => post('comment', true), // Clean up whitespace
    'user_id' => post('user_id', false, true), // Cast to numeric
    'post_id' => post('post_id', false, true), // Cast to numeric
    'rating' => post('rating', false, true) // Cast to numeric
];

// Validate required fields
if (!empty($comment_data['comment']) && $comment_data['user_id'] > 0 && $comment_data['post_id'] > 0) {
    $sql = "INSERT INTO comments (comment, user_id, post_id, rating, created_at) 
            VALUES (:comment, :user_id, :post_id, :rating, NOW())";
    
    $this->db->query_bind($sql, $comment_data);
    echo "Comment added successfully.";
} else {
    echo "Please fill in all required fields.";
}

Important: Named Parameters Are Required

PHP
// ❌ WRONG - Unnamed parameters not supported in Trongate v2
$sql = "SELECT * FROM users WHERE age > ? AND city = ?";
$params = [25, 'London'];
$users = $this->db->query_bind($sql, $params, 'object'); // This will FAIL!

// ✅ CORRECT - Named parameters required
$sql = "SELECT * FROM users WHERE age > :age AND city = :city";
$params = ['age' => 25, 'city' => 'London'];
$users = $this->db->query_bind($sql, $params, 'object'); // This works

Trongate v2 only accepts named parameters. This ensures consistent, readable code and eliminates confusion about parameter ordering.

Important Notes

  • All parameters ($sql and $data) are required (though $data can be an empty array).
  • Named parameters are mandatory - use colon-prefixed placeholders like :name, :age.
  • Question mark placeholders (?) are not supported in Trongate v2.
  • Data types are automatically detected and bound correctly (integers, strings, booleans, nulls).
  • 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 with bound values will be displayed before execution.
  • For queries with no parameters, you can pass an empty array: query_bind($sql, [], 'object')
  • Always use Trongate's post() function to safely retrieve form data with options for cleaning and type casting.

When to use query_bind():

  • Any query with user input (forms, search, filters)
  • Queries with dynamic WHERE conditions
  • Complex JOIN queries with parameters
  • INSERT/UPDATE/DELETE operations with data
  • Any query where values come from outside your code

When to use query():

  • Static, hard-coded reporting queries (no parameters)
  • Migration scripts with no user input
  • Complex analytical queries with no parameters
  • Administrative operations with trusted, hard-coded data

Rule of thumb: If your SQL has any dynamic values, you must use query_bind() with named parameters.