☰

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.

Example #2: Complex JOIN with Array Results

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

Example #3: UPDATE with Parameter Binding

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

Example #4: DELETE with Multiple Conditions

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

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.

Important: Named Parameters Are Required

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.