date comparison with form variable

[wy85-bit]

Hi Everyone:

Hope you're doing well. I have a question about comparing dates. I'm creating this very simple website with SQLPAGE but I cannot seem to get it to work the way I want to. Basically I cannot seem to get this to work => where today_ = :Datum. Usually you can compare a date with a date string but it doesn't seem to work like that with form variables. I pasted my code below. Any help would be greatly appreciated!

Kind regards,
Winnie

--------------------------------------------------------------------------------------------------------------------------------------------------

select
    'hero' as component,
    'Welkom op de Seat Registration site van Veneficus.' as title,
    'Op deze pagina kan je jouw stoel registereren zodat je altijd een plekje hebt op kantoor!' as description;

DROP TABLE IF EXISTS range;

create table range as 
WITH range AS (
    SELECT 0 as v
    UNION ALL
    SELECT v + 1
    FROM range t
    WHERE t.v < 5000)
select * from range;

drop table if exists future_dates;

CREATE TABLE future_dates as
select date(DATETIME('2025-08-25','+'||v || ' ' ||'days')) as today_ FROM range;

drop table if exists seats;
create table seats as 
select * ,
35 as seats_available
from future_dates;

select 
    'form' as component,
    'Vul de datum in van de registratie' as title,
    'Add Location' as validate,     -- Form submission button label
    '#demo' as target,
    'demo' as id
select 'Datum' as name,
'Datum format: yyyy-mm-dd' as label;

select 'table' as component
select * from seats where today_ = (select case date('now') when (date('now')) then (date('now')) else date('now') end)

select 
    'form' as component,
    'Add registration' as title,
    'Add Registration' as validate,     -- Form submission button label
    '#demo' as target,
    'demo' as id
select 'Registration' as name; 

select 
    'form' as component,
    'Remove Registration' as title,
    'Remove Registration' as validate,     -- Form submission button label
    '#demo' as target,
    'demo' as id
select 'RemoveRegistration' as name; 

DROP TABLE If EXISTS tmp_result_reserved;
create table tmp_result_reserved as
    select 
    :Registration,
    case :Registration
    when :Registration not like '%[^0-9]' then 0
    when (:Registration*1::int) then :Registration*1
    else 0
    end as seats_reserved;

DROP TABLE if exists tmp_result_freed;
create table tmp_result_freed as
    select
    :RemoveRegistration,
    case :RemoveRegistration
    when :RemoveRegistration not like '%[^0-9]' then 0
    when (:RemoveRegistration::int) then :RemoveRegistration::int
    else 0
    end as seats_freed;

update seats
set seats_available= case 
when ((select seats_reserved from tmp_result_reserved)::int)<0 then seats_available
when (select seats_available - (select seats_reserved from tmp_result_reserved)::int  from seats where today_ = date('now'))::int  > (select 0)
then (select seats_available - (select seats_reserved from tmp_result_reserved)::int  from seats where today_ = date('now'))::int
else seats_available
end
where
(today_)= date('now');

update seats
set seats_available = 
case 
when ((select seats_freed from tmp_result_freed)::int)<0 then seats_available
when (select seats_available + (select seats_freed from tmp_result_freed)::int  from seats where today_ = date('now'))::int  <= (select 35)
then (select seats_available + (select seats_freed from tmp_result_freed)::int  from seats where today_ = date('now'))::int
else seats_available
end
where (today_)=date('now'); 

select 'table' as component,
'Result of Registration' as title
select case 
when :Registration::int < 0 then
'Helaas, de aanvraag is niet juist. Kan geen negatief aantal stoelen registreren. Probeer het nog een keer.'
when :RemoveRegistration::int <0 then
'Helaas, de aanvraag is niet juist. Kan geen negatief aantal stoelen toevoegen. Probeer het nog een keer.'
when (select seats_available + (select seats_freed from tmp_result_freed)::int  from seats where today_=date('now'))::int  < (select 35)
then 'Registratie is verwerkt!'
when (select seats_available - (select seats_reserved from tmp_result_reserved)::int  from seats where today_=date('now'))::int  > (select 0)
then 'Registratie is verwerkt!'
when :RemoveRegistration not like '%[^0-9]' then 'Helaas, de aanvraag is niet juist. Aantal stoelen kan niet alphanumeriek zijn. Probeer het nog een keer.'
when :Registration not like '%[^0-9]' then 'Helaas, de aanvraag is niet juist. Aantal stoelen kan niet alphanumeriek zijn. Probeer het nog een keer.'
else 'Helaas, de aanvraag is niet juist. Probeer het nog een keer.'
end as 'result Of Registration';

Versions

select printf('
You are currently running SQLite %s and SQLPage %s
', sqlite_version(), sqlpage.version()
) as contents_md;

select 
    'chart'   as component,
    'Bezetting Kantoor aantal vrije stoelen' as title,
    0         as ymin,
    TRUE      as toolbar; 
 select   'Bezetting Kantoor' as series,
  today_ as x,
seats_available as value
from seats
where today_ between date('now') and date(date('now')+ '5 days');

select 
    'chart'   as component,
    'Bezetting Kantoor aantal bezette stoelen' as title,
    0         as ymin,
    TRUE      as toolbar; 
 select   'Bezetting Kantoor' as series,
  today_ as x,
35-seats_available as value
from seats
where today_ between date('now') and  date(date('now')+ '5 days');

[lovasoa]

Short answer: the problem wasn’t SQLite type casting, it was which variables are set where and inconsistent use of SQLPage variable. You were recreating tables on every page load, using invalid SQL constructs, and comparing today_ = :Datum against a variable that wasn’t actually set the way you expected. Below I explain the main problems, give a clean, minimal working approach, and show debug tips so you can verify variables on the page.

What was wrong (summary)

1. You recreate the schema on every page load.
   DROP TABLE / CREATE TABLE inside the page will erase data and is wasteful. Create seats once with a setup script.

2. Variable confusion: page / URL variables vs. form bind variables.
   In the SQLPage pattern the page/URL variable (used across components) is usually $date (or a SET selected_date = ... alias). Form submission values show up as bind variables like :amount or :Registration only for the submission, and not automatically as the page selection unless you route them into the URL or handle them in the action script. Your where today_ = :Datum was using a variable that wasn’t reliably set.

3. Invalid / non-portable SQL and mistakes:

   • CASE :Registration when :Registration not like '%[^0-9]' then ... is not valid syntax. Use CASE WHEN condition THEN ... END.
   • ::int cast syntax is not SQLite. Use CAST(x AS INTEGER) or arithmetic x+0.
   • LIKE '%[^0-9]' uses a PCRE character class — SQLite LIKE does not support that. Use numeric form inputs and server-side CAST/validation instead.
   • Re-used names and confusing CTE/table names (range used many ways) make the SQL harder to reason about.

4. Overly complex updates and subqueries. Your update logic uses multiple nested selects from seats which is inefficient and error-prone. Keep updates direct and atomic. Use database constraints for server-side validation, and form field properties for client-side validation.

Minimal, correct approach

1. Create seats once (schema/setup run one time).
2. Let the page set a canonical page variable (e.g. selected_date) from $date or default to DATE('now').
3. Use a form that sets the URL param (GET) or posts to a handler that updates the table for the given date.
4. Use CAST(... AS INTEGER) for numeric validation in SQL and constrain values using CASE so seat counts stay between 0 and 35.

(A) Setup script — run once

You can use SQLPage migrations for this.

-- db_schema.sql (Run this file ONCE to set up your database)

DROP TABLE IF EXISTS seats;
CREATE TABLE seats (
    today_ DATETIME PRIMARY KEY,
    seats_available INTEGER NOT NULL CHECK (seats_available BETWEEN 0 AND 35)
);

-- Populate the seats table with dates and initial seat counts for a range
-- This avoids recreating the table and wiping data on every page load.
-- We'll add 35 available seats for the next 365 days starting from today.
WITH RECURSIVE range_cte(today_) AS (
    SELECT DATE('now')
    UNION ALL
    SELECT DATE(today_, '+1 day') FROM range_cte
    WHERE today_ < DATE('now', '+1 year') -- Dates for the next year
)
INSERT INTO seats (today_, seats_available)
SELECT today_, 35 AS seats_available
FROM range_cte;

(B) Main page (seats.sql) — canonical date and display

-- Set a default date for the page if no date is provided in the URL
SET selected_date = COALESCE($date, DATE('now'));

-- Hero section
SELECT
    'hero' AS component,
    'Welcome to the Veneficus Seat Registration site.' AS title,
    'On this page, you can register your seat so you always have a spot in the office!' AS description;

-- Form to select the date
-- This uses GET method to update the $date URL parameter, which then drives other components.
SELECT
    'form' AS component,
    'Select Registration Date' AS title,
    'GET' AS method,
    true as auto_submit,
    'Select' AS validate;
SELECT
    'Date' AS label,
    'date' AS type,
    'date' AS name,
    $selected_date AS value, -- Prefill with current selected date or today
    printf('Available Seats for *%s*: **%d**', $selected_date, seats_available) AS description_md,
    4 AS width
FROM seats
WHERE today_ = $selected_date;

-- Add Registration Form
SELECT
    'form' AS component,
    sqlpage.link('seats_register.sql', json_object(
        'date', $selected_date
    )) as action,
    'Add Registration' AS title,
    'Add Registration' AS validate,
    'green' AS validate_color,
    'Add' AS id; -- Action defaults to current page
SELECT
    'amount' AS name,
    'Number to Register' AS label,
    'number' AS type,
    'number' AS inputmode, -- For better mobile keyboard
    TRUE AS required,
    1 as min,
    seats_available as max,
    printf('Add seats (max %d)', seats_available) AS description
FROM seats WHERE today_ = $selected_date;

-- Remove Registration Form
SELECT
    'form' AS component,
    sqlpage.link('seats_register.sql', json_object(
        'date', $selected_date,
        'remove', 'true'
    )) as action,
    'Remove Registration' AS title,
    'Remove Registration' AS validate,
    'red' AS validate_color,
    'Remove' AS id; -- Action defaults to current page
SELECT
    'amount' AS name,
    'Number to Remove' AS label,
    'number' AS type,
    'number' AS inputmode,
    TRUE AS required,
    1 as min,
    35 - seats_available as max,
    printf('Release seats (max %d)', 35 - seats_available) AS description
FROM seats WHERE today_ = $selected_date;

-- Charts displaying seat availability for the selected date and next 5 days
SELECT
    'chart' AS component,
    'Office Occupancy: Available Seats' AS title,
    0 AS ymin,
    TRUE AS toolbar,
    TRUE AS time;
SELECT
    'Office Occupancy' AS series,
    today_ AS x,
    seats_available AS y
FROM seats
WHERE today_ BETWEEN $selected_date AND DATE($selected_date, '+5 days')
ORDER BY today_;

(C) Simple handler for add/remove (seats_register.sql)

-- Handle form submissions for adding/removing registrations
SET delta = case $remove when 'true' then :amount else -:amount end;

UPDATE seats
SET seats_available = seats_available + CAST($delta AS INTEGER)
WHERE today_ = $date;

SELECT
    'redirect' as component,
    sqlpage.link('seats.sql', json_object('date', $date)) as link;

Quick tips & best practices

• Use GET for selecting dates so $date is in the URL and available everywhere. Use POST (form submit) when changing data, and route to a handler script.
• Keep param names consistent: $date for the page/URL, :amount or :Registration for form bind variables in the handler.
• Validate numerics using CAST(... AS INTEGER) and also set type='number' / min / max on the HTML form inputs.
• Don’t DROP and CREATE tables in the render page. Seed the DB once.
• Replace regex checks with numeric validation. If you need regex, you’ll have to enable an extension; simpler to use typing and CAST.

[wy85-bit]

Hi Lovasoa,

Thank you so much for your elaborate answer and tremendous help, you saved my life!

I want to understand your code and I want to learn from you so I have the following questions:

• What do you mean with SQLPage migrations? Do you mean .sql files that alter the database? I just have to paste it in the file and it runs right? ( I use the web UI for SQLPage)
• You mentioned 'Set a default date for the page if no date is provided in the URL'. What URL are you refering to? Do you mean when no date is selected?
• Where is the variable $remove actually set? I must be blind but I cannot find this/don't understand this part. Same goes for the $date variable (or is this set using the json_object function?)
• Can you say something more about client and server side? Where do we use client side code and where do we use server side code in SQLPage?
• What exactly is a handler?
• Can you say something more about why the :Datum variable wasn't reliably set? my little brain has trouble comprehending this

Hoping to learn more from you so I'm hoping you're willing to answer my questions.

Best and thank you,
Winnie

[lovasoa]

Hi ! You're welcome.

• about migrations: see https://sql-page.com/your-first-sql-website/migrations. They are not supported on https://editor.datapage.app
• Each of your pages has an URL, and you can pass parameters to it through the URL. You can open your site in a separate tab to interact with it directly and see its URL. For instance https://19f24a3d.ca.datapage.app/seats.sql?date=2025-09-11 is the URL of my private temporary instance of the page, with $date set to tomorrow. All GET variables (referenced with $var) are passed as URL parameters.
• $remove is a URL parameter. It's set here:

  •     sqlpage.link('seats_register.sql', json_object(
             'date', $selected_date,
             'remove', 'true'
         )) as action,

  • see https://sql-page.com/functions?function=link
• Client-side code runs in the user’s browser and handles what they see and interact with, like HTML, CSS, and JavaScript. Server-side code runs on a remote server and handles data processing, database operations, authentication, and other behind-the-scenes logic. When you use SQLPage, you don't have to worry about it. What I meant was just:
  • use database constraints everywhere you can (this gives you a security guarantee)
  • and also use properties of the form component to limit what users can type in a field (this provides a nice user experience but malicious users can work around it)
• I call the separate sql file that receives the form fields posted by the first page a handler.
• :variables are set only after the form is submitted, in the handler (which can be the same page as the one that contains the form). But when the form is loaded initially, they are all NULL.

[wy85-bit]

Hi Lovasoa,

Thank you for your clear answers, much appreciated. Hope to learn more from you in the future~!

Best,
Winnie