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.
IN (…). Never add quotes
yourself around a variable. This is injection-safe even when the value came from the browser.The variables
| Variable | Resolves to | Example |
|---|---|---|
:userid | The logged-in user's id | owner_id = :userid |
:username | The logged-in user's login name | rep = :username |
:user.field | Any column from the current user's row | dept = :user.department |
:session.key | A value you stored in the session | region = :session.region |
:get.key · :post.key | A request parameter | status = :get.status |
:field.name | Another field's value on the same form (Add/Edit) — cascading dropdowns | country_id = :field.country |
:master.field | A field from the master record (master/detail) | order_id = :master.id |
:language | The current UI language | lang = :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.
| Variable | Resolves to | Example |
|---|---|---|
:today · :now | Current date · date-time | due <= :today |
:yesterday · :tomorrow | ±1 day | created >= :yesterday |
:startofweek · :endofweek | This week's Mon / Sun | c >= :startofweek |
:startofmonth · :endofmonth | This month's first / last day | c BETWEEN :startofmonth AND :endofmonth |
:startoflastmonth · :endoflastmonth | Last month's first / last day — pair with this month's for month-over-month | c >= :startoflastmonth AND c < :startofmonth |
:startofyear · :endofyear | This year's first / last day | c >= :startofyear |
:daysago.N · :daysfromnow.N | A rolling window of N days | created >= :daysago.30 |
:hoursago.N · :monthsago.N | N hours / months back (also …fromnow) | ts >= :hoursago.24 |
:userrole | The current user's role/group | visibility = :userrole |
:ip | The visitor's IP address | last_ip = :ip |
:cookie.key | A request cookie | theme = :cookie.theme |
:uuid | A 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
| Place | How |
|---|---|
| WHERE tabs (List page filters) | Type the variable straight into a tab's condition. |
| Lookup Wizard WHERE | Make a dropdown depend on the current user/session (see below). |
| Dashboard charts | A chart component's WHERE box. |
| Master/Detail child grids | A relation's extra filter — scope the child rows shown under a master. |
| Export | The active WHERE tab carries into the export automatically. |
| Query module — SQL editor | Type a variable straight into the statement and run it; it resolves against your session before the query executes. |
| Query module — Additional WHERE tabs | Same as List-page tabs, with a live row-count preview. |
| Your own SQL | Wrap 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 — :userid | Parameter — :min_total | |
|---|---|---|
| Who supplies the value | The platform, from the session | Whoever runs the query |
| Do you see a field for it | No | Yes, above the SQL editor |
| How it reaches the database | Replaced by a safely quoted literal | Sent separately as a bound value |
| If there is no value | Becomes '' | Becomes NULL |
| Can it be a table or column name | No — 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.
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.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).
country_id = :field.country
Need data from another table? Use a sub-query — still fed by the form:
-- 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.
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
assigned_to = :userid AND status = 'open'
3 · Filter by a value you stashed in the session
$_SESSION['region'] = pp()->user()['region'] ?? '';
region = :session.region
4 · In your own query
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.
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:
created BETWEEN :startofmonth AND :today
assigned_to = :userid
7 · An IN-list from a session array
// $_SESSION['cartIds'] = [12, 15, 20];
product_id IN (:session.cartIds) // → product_id IN (12,15,20)
? 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()).