where() or orWhere()

The where() method is the most versatile condition builder in decMuc/PDOdb. It supports all comparison operators, arrays, subqueries, SQL functions, and even custom injection-safe expressions.

Security Notice: All where() inputs are internally checked for suspicious patterns such as 1; SLEEP(1), logic breaks, or unsafe casting. This heuristic system prevents many types of SQL injection — even if untyped input is passed.
⚠️ For maximum safety, you should use type-specific filters like whereInt() or whereBool() where possible.
Advanced details: Heuristic WHERE check

This protection layer (internally called PDOdb_HEURISTIC_WHERE_CHECK) is enabled by default. You can disable it globally or per instance if required.

Basic usage

The where() method adds a simple AND condition to the query. It accepts one to three parameters, depending on the type of comparison:

  • where(column, value) – compares with = by default
  • where(column, operator, value) – for custom operators like !=, >, LIKE etc.
$db->where('id', 5);
$db->where('status', 'active');
$db->where('price', '>', 10);
$db->where('title', 'LIKE', '%special%');

$db->get('products');

All where() conditions are automatically combined using AND.

Adding OR conditions

To add a logical OR between conditions, use the orWhere() method. It accepts the same parameters as where() and must follow an existing condition. 

$db->where('status', 'active');
$db->orWhere('status', 'pending');
$db->where('type', 'user');

$db->get('accounts');

This creates a query like:
SELECT * FROM accounts WHERE status = 'active' OR status = 'pending' AND type = 'user'

⚠️ Parentheses are not automatically added. Use subqueries or manual logic if grouping is required.

Mixing where() and orWhere()

You can chain where() and orWhere() freely to build complex conditions during query composition. This gives you full flexibility for dynamic logic. 

$db->where('type', 'user');
$db->orWhere('type', 'admin');
$db->where('active', 1);
$db->orWhere('email_verified', 1);

$db->get('users');

This builds a query like:
WHERE type = 'user' OR type = 'admin' AND active = 1 OR email_verified = 1
Note: Parentheses are not applied automatically.

If you require strict control over logic grouping, consider using subqueries or manual query construction.

What values are expected in where() conditions?

The where() and orWhere() methods support a wide range of input types. However, not all combinations are valid. Here's what is supported:

  • Scalar values (int, string, float)
  • null (converted to IS NULL / IS NOT NULL)
  • Arrays (for IN / NOT IN)
  • Subqueries (objects with a getSubQuery() method)
  • Special functions like $db->now()

The $operator must be a valid SQL operator (e.g. =, >, LIKE, IN, IS NOT). Anything else will be rejected automatically for safety reasons.

❌ Invalid Examples:
where('id', '1 OR 1=1') – flagged as suspicious
→ deferred and only blocked if id is a numeric or strict field
where('col', '(SELECT ... )') – raw subqueries as strings → not allowed
where('SUM(price)', 100) – aggregate functions → blocked

This validation is handled automatically by secureWhere() and can be extended via whereInt(), whereDate(), and similar safe variants.

Chaining multiple where() and orWhere() calls

You can call where() and orWhere() repeatedly in any order. All conditions are accumulated until the query is executed. 

$db
  ->where('role', 'customer')
  ->where('country', 'DE')
  ->orWhere('newsletter_optin', 1);

$users = $db->get('users');

This generates:
WHERE role = 'customer' AND country = 'DE' OR newsletter_optin = 1

Checking for NULL and NOT NULL

You can check for NULL or NOT NULL values using the standard where() method by passing null as the value.

  • where('column', null) → generates IS NULL
  • where('column', null, 'IS NOT') → generates IS NOT NULL
// Find all users where deleted_at IS NULL
$db->where('deleted_at', null);

// Find all orders where shipped_at IS NOT NULL
$db->where('shipped_at', null, 'IS NOT');

$db->get('orders');

All where() conditions are also available as orWhere() by simply changing the method name.

For improved type safety and readability, you can also use:

Using BETWEEN and NOT BETWEEN conditions

To filter a value range (e.g. prices or dates), pass an array with exactly two values and set the operator to BETWEEN or NOT BETWEEN.

  • where('price', [10, 20], 'BETWEEN') → price BETWEEN 10 AND 20
  • where('created_at', ['2020-01-01', '2020-12-31'], 'NOT BETWEEN') → excludes that range
// Match range of prices
$db->where('price', [10, 20], 'BETWEEN');

// Exclude a date range
$db->where('created_at', ['2020-01-01', '2020-12-31'], 'NOT BETWEEN');

$db->get('products');

Use whereBetween() or whereNotBetween() for type-safe variants. These explicitly validate range values.

orWhere*() versions are also available.

Using LIKE and NOT LIKE

To match patterns in a column, use the SQL operators LIKE or NOT LIKE together with percent signs (%) as wildcards. The pattern is passed as a string, and you specify the operator explicitly.

  • where('name', 'John%', 'LIKE') → starts with "John"
  • where('email', '%@gmail.%', 'NOT LIKE') → exclude Gmail addresses
  • orWhere('notes', '%urgent%', 'LIKE') → contains the word "urgent"
// Starts with 'admin'
$db->where('username', 'admin%', 'LIKE');

// Excludes Gmail addresses
$db->where('email', '%@gmail.%', 'NOT LIKE');

// Contains 'SQL injection'
$db->orWhere('notes', '%SQL injection%', 'LIKE');

$db->get('users');

LIKE and NOT LIKE are both fully supported. Just pass the pattern and use the operator explicitly.

LIKE and NOT LIKE are both fully supported. Just pass the pattern and use the operator explicitly.

EXISTS and NOT EXISTS

EXISTS and NOT EXISTS are fully supported and allow you to check whether a given subquery returns any rows. To use these conditions, you must pass a subquery object created via subQuery(). 

$sub = $db->subQuery('s');
$sub->where('user_id', $db->func('u.id'));
$sub->get('orders', 'user_id');

$db->where('EXISTS', $sub);
// or
$db->where('NOT EXISTS', $sub);

These advanced WHERE conditions rely on subquery support and cannot be used with plain SQL strings. For details and more examples, see Subqueries and Nested Conditions.