PPPHP

SQL Variables

Make your queries dynamic without writing PHP glue. Drop a token like :userid or :session.region into a WHERE clause and it's replaced at run time with a safe, correctly-quoted value — the logged-in user, a session value, a request parameter, and more.

Always safe. Values are escaped and quoted for you, by type — a string is quoted, a number goes in raw, a list becomes a comma-separated set for IN (…). Never add quotes yourself around a variable. This is injection-safe even when the value came from the browser.

The variables

VariableResolves toExample
:useridThe logged-in user's idowner_id = :userid
:usernameThe logged-in user's login namerep = :username
:user.fieldAny column from the current user's rowdept = :user.department
:session.keyA value you stored in the sessionregion = :session.region
:get.key · :post.keyA request parameterstatus = :get.status
:field.nameAnother field's value on the same form (Add/Edit) — cascading dropdownscountry_id = :field.country
:master.fieldA field from the master record (master/detail)order_id = :master.id
:languageThe current UI languagelang = :language
:{name with spaces}Braces for names with spaces/dots:{master.Customer ID}

A missing value becomes an empty string ('') — it never breaks the query. Positional :1, :2 placeholders and PostgreSQL ::casts are left alone.

Also available: :form.name (alias of :field), :request.key (either GET or POST), :usergroup (alias of :userrole), and the full offset family up to :yearsago.N / :yearsfromnow.N.

Dynamic date & context variables

These resolve at run time — perfect for rolling-window filters and audit columns, with no PHP.

VariableResolves toExample
:today · :nowCurrent date · date-timedue <= :today
:yesterday · :tomorrow±1 daycreated >= :yesterday
:startofweek · :endofweekThis week's Mon / Sunc >= :startofweek
:startofmonth · :endofmonthThis month's first / last dayc BETWEEN :startofmonth AND :endofmonth
:startoflastmonth · :endoflastmonthLast month's first / last day — pair with this month's for month-over-monthc >= :startoflastmonth AND c < :startofmonth
:startofyear · :endofyearThis year's first / last dayc >= :startofyear
:daysago.N · :daysfromnow.NA rolling window of N dayscreated >= :daysago.30
:hoursago.N · :monthsago.NN hours / months back (also …fromnow)ts >= :hoursago.24
:userroleThe current user's role/groupvisibility = :userrole
:ipThe visitor's IP addresslast_ip = :ip
:cookie.keyA request cookietheme = :cookie.theme
:uuidA fresh UUID v4 (handy on INSERT)SET token = :uuid
// "orders placed this month"
created BETWEEN :startofmonth AND :today
// "everything in the last 30 days"
created >= :daysago.30
// "logged in the last day, from this IP"
last_seen >= :hoursago.24 AND ip = :ip

Where you can use them

PlaceHow
WHERE tabs (List page filters)Type the variable straight into a tab's condition.
Lookup Wizard WHEREMake a dropdown depend on the current user/session (see below).
Dashboard chartsA chart component's WHERE box.
Master/Detail child gridsA relation's extra filter — scope the child rows shown under a master.
ExportThe active WHERE tab carries into the export automatically.
Query module — SQL editorType a variable straight into the statement and run it; it resolves against your session before the query executes.
Query module — Additional WHERE tabsSame as List-page tabs, with a live row-count preview.
Your own SQLWrap it in DB::PrepareSQL() in any event / Custom Button.

Variables vs. query parameters

The Query module uses the same :name syntax for two different things, and it helps to know which you are looking at. Everything on this page is a variable: the platform knows the name and fills it in from the session. Any other :name is a parameter — the Query module shows a field for it and asks you for a value each time you run.

Variable — :useridParameter — :min_total
Who supplies the valueThe platform, from the sessionWhoever runs the query
Do you see a field for itNoYes, above the SQL editor
How it reaches the databaseReplaced by a safely quoted literalSent separately as a bound value
If there is no valueBecomes ''Becomes NULL
Can it be a table or column nameNo — both stand in for a value only

So WHERE owner_id = :userid AND total >= :min_total asks you for exactly one thing: the minimum total. The owner is already known.

A variable inside quotes stays as you typed it. Running SELECT 'ratio a:b' in the Query module returns the text a:b — the :b is part of a string, not a variable, and is left alone. The same is true inside -- and /* */ comments.
Reaching for IS NULL? The obvious way to write an optional filter, (:thing IS NULL OR col = :thing), works on MySQL and SQLite but fails on PostgreSQL: a value that appears only inside IS NULL gives it no type to work from. Write col = COALESCE(:thing, col) instead — it means the same thing and runs everywhere.

Recipes

0 · A cascading dropdown (depends on another field)

The classic dependent lookup — a City dropdown that only shows cities in the Country chosen elsewhere on the same form. Reference the sibling field with :field.name; the list re-filters live the moment that field changes (the control sends its value automatically).

WhereDesigner → the City field → Lookup Wizard → WHERE conditionPHP
country_id = :field.country

Need data from another table? Use a sub-query — still fed by the form:

WhereLookup Wizard → WHERE conditionPHP
-- only cities whose region matches the selected country's region
region_id IN (SELECT region_id FROM countries WHERE id = :field.country)

1 · A dropdown that only shows the current user's records

The classic dependent lookup — a Lookup Wizard field whose options are scoped to whoever is logged in.

WhereDesigner → a field → Lookup Wizard → WHERE conditionPHP
owner_id = :userid

Now the dropdown lists only rows the signed-in user owns — no PHP required.

2 · A List-page tab scoped to me

WhereSQL Query / Additional WHERE tabs → a tab's conditionPHP
assigned_to = :userid AND status = 'open'

3 · Filter by a value you stashed in the session

WhereGlobal Events → Application → After Login (store it once)PHP
$_SESSION['region'] = pp()->user()['region'] ?? '';
Where…then any WHERE tab / Lookup / chartPHP
region = :session.region

4 · In your own query

WhereCustom Button → Server, a field event, or an action_*PHP
// :userid is expanded to the logged-in id, then run
$mine = dbAll(DB::PrepareSQL("SELECT * FROM tickets WHERE assigned_to = :userid ORDER BY due"));

// mix with positional :1 for other values
$sql  = DB::PrepareSQL("SELECT * FROM logs WHERE user_id = :userid AND level >= :1", 3);
$rows = dbAll($sql);

5 · A Master/Detail child grid, scoped

Give a relation an extra filter to control which child rows appear under a master — on top of the foreign-key link. Dynamic variables shine here.

WhereTables → Relation → Filter (WHERE)PHP
status = 'active'                     // only active line items
// or dynamic:
created >= :daysago.30                 // only the last 30 days of children
assigned_to = :userid                 // only children assigned to me

6 · An export that follows the visible tab

When a List page has Additional WHERE tabs, whichever one is active is applied to the export too — so "Export" from a This month or My records tab gives exactly those rows. Just put the variable in the tab's condition:

WhereSQL Query / WHERE tabs → a tab's conditionPHP
created BETWEEN :startofmonth AND :today
assigned_to = :userid

7 · An IN-list from a session array

Whereanywhere a WHERE is acceptedPHP
// $_SESSION['cartIds'] = [12, 15, 20];
product_id IN (:session.cartIds)      // → product_id IN (12,15,20)
Prefer plain bound ? parameters (dbAll / dbRow / dbValue) for values you already have in PHP. Reach for SQL variables when the query lives in the Designer (a WHERE tab, a lookup, a chart) and needs to know who's looking — that's where they shine. The permission-aware pp()->table() builder is the code-side equivalent: pp()->table('tickets')->where('assigned_to', pp()->userId()).