The form component
A series of input fields that can be filled in by the user. The form contents can be posted and handled by another sql file in your site. The value entered by the user in a field named x will be accessible to the target SQL page as a variable named $x. For instance, you can create a SQL page named "create_user.sql" that would contain "INSERT INTO users(name) VALUES($name)" and a form with its action property set to "create_user.sql" that would contain a field named "name".
Top-level parameters
action
class
enctype
When method="post"
, this specifies how the form-data should be encoded
when submitting it to the server.
id
method
reset
title
validate
validate_color
validate_outline
Row-level parameters
name
autocomplete
autofocus
checked
class
create_new
description
disabled
dropdown
formaction
formenctype
When type
is submit
or image
, this specifies how the form-data
should be encoded when submitting it to the server.
Takes precedence over any enctype
set on the form
element.
NOTE: when a file
type input is present, then formenctype="multipart/form-data"
is automatically applied to the default validate button.
id
label
max
maxlength
min
minlength
multiple
options
pattern
placeholder
prefix
prefix_icon
readonly
required
searchable
step
suffix
type
value
width
Example 1
The best way to manage forms in SQLPage is to create at least two separate files:
- one that will contain the form itself, and will be loaded when the user visits the page,
- one that will handle the form submission, and will redirect to whatever page you want to display after the form has been submitted.
For instance, if you were creating a form to manage a list of users, you could create:
- a file named
users.sql
that would contain a list of users and a form to create a new user, - a file named
create_user.sql
that would insert the new user in the database, and then redirect tousers.sql
.
create_user.sql
could contain the following sql statement to safely insert the new user in the database:
INSERT INTO users(name) VALUES(:username)
RETURNING 'redirect' AS component, 'users.sql' AS link
When loading the page, the value for :username
will be NULL
if no value has been submitted.
select
'form' as component,
'create_user.sql' as action;
select
'username' as name;
Result
Example 2
A user registration form, illustrating the use of required fields, and different input types.
select
'form' as component,
'User' as title,
'Create new user' as validate;
select
'First name' as name,
'John' as placeholder;
select
'Last name' as name,
TRUE as required,
'We need your last name for legal purposes.' as description;
select
'Resume' as name,
'textarea' as type;
select
'Birth date' as name,
'date' as type,
'2010-01-01' as max,
'1994-04-16' as value;
select
'Password' as name,
'password' as type,
'^(?=.*[A-Za-z])(?=.*\d)[A-Za-z\d]{8,}$' as pattern,
TRUE as required,
'Minimum eight characters, at least one letter and one number.' as description;
select
'I accept the terms and conditions' as label,
'terms' as name,
'checkbox' as type,
TRUE as required;
Result
Example 3
Create prepended and appended inputs to make your forms easier to use.
select
'form' as component;
select
'Your account' as name,
'mail' as prefix_icon,
'Email:' as prefix,
'@mydomain.com' as suffix;
Result
Example 4
This example illustrates the use of the select
type.
In this select input, the various options are hardcoded, but they could also be loaded from a database table,
using a function to convert the rows into a json array like
-
json_group_array()
in SQLite, -
json_agg()
in Postgres, -
JSON_ARRAYAGG()
in MySQL, or -
FOR JSON PATH
in Microsoft SQL Server.
In SQLite, the query would look like
SELECT
'select' as type,
json_group_array(json_object(
'label', name,
'value', id
)) as options
FROM fruits
select
'form' as component,
'examples/show_variables.sql' as action;
select
'Fruit' as name,
'select' as type,
TRUE as searchable,
1 as value,
'[{"label": "Orange", "value": 0}, {"label": "Apple", "value": 1}, {"label": "Banana", "value": 3}]' as options;
Result
Example 5
Multi-select
You can authorize the user to select multiple options by setting the multiple
property to true
.
This creates a more compact (but arguably less user-friendly) alternative to a series of checkboxes.
In this case, you should add square brackets to the name of the field.
The target page will then receive the value as a JSON array of strings, which you can iterate over using
- the
json_each
function in SQLite and Postgres, - the
OPENJSON
function in Microsoft SQL Server. - in MySQL, json manipulation is less straightforward: see the SQLPage MySQL json example
More information on how to handle JSON in SQL.
The target page could then look like this:
insert into best_fruits(id) -- INSERT INTO ... SELECT ... runs the SELECT query and inserts the results into the table
select CAST(value AS integer) as id -- all values are transmitted by the browser as strings
from json_each($preferred_fruits); -- json_each returns a table with a "value" column for each element in the JSON array
Example multiselect generated from a database table
As an example, if you have a table of all possible options (my_options(id int, label text)
),
and another table that contains the selected options per user (my_user_options(user_id int, option_id int)
),
you can use a query like this to generate the multi-select field:
select 'select' as type, true as multiple, json_group_array(json_object(
'label', my_options.label,
'value', my_options.id,
'selected', my_user_options.option_id is not null
)) as options
from my_options
left join my_user_options
on my_options.id = my_user_options.option_id
and my_user_options.user_id = $user_id
select
'form' as component,
'examples/show_variables.sql' as action;
select
'Fruits' as label,
'fruits[]' as name,
'select' as type,
TRUE as multiple,
TRUE as create_new,
'Good fruits...' as placeholder,
TRUE as searchable,
'press ctrl to select multiple values' as description,
'[{"label": "Orange", "value": 0, "selected": true}, {"label": "Apple", "value": 1}, {"label": "Banana", "value": 3, "selected": true}]' as options;
Result
Example 6
This example illustrates the use of the radio
type.
The name
parameter is used to group the radio buttons together.
The value
parameter is used to set the value that will be submitted when the user selects the radio button.
The label
parameter is used to display a friendly name for the radio button.
The description
parameter is used to display a helper text near the radio button.
We could also save all the options in a database table, and then run a simple query like
SELECT 'form' AS component;
SELECT * FROM fruit_option;
In this example, depending on what the user clicks, the target index.sql
page will be loaded with a the variable $fruit
set to the string "1", "2", or "3".
select
'form' as component,
'GET' as method,
'index.sql' as action;
select
'fruit' as name,
'radio' as type,
1 as value,
'An apple a day keeps the doctor away' as description,
'Apple' as label;
select
'fruit' as name,
'radio' as type,
2 as value,
'Oranges are a good source of vitamin C' as description,
'Orange' as label,
TRUE as checked;
select
'fruit' as name,
'radio' as type,
3 as value,
'Bananas are a good source of potassium' as description,
'Banana' as label;
Result
Example 7
When you want to include some information in the form data, but not display it to the user, you can use a hidden field.
This can be used to track simple data such as the current user's id, or to implement more complex flows, such as a multi-step form, where the user is redirected to a different page after each step.
This can also be used to implement CSRF protection,
if your website has authenticated users that can perform sensitive actions through simple links.
But note that SQLPage cookies already have the SameSite=strict
attribute by default, which protects you against CSRF attacks by default in most cases.
select
'form' as component,
'Delete' as validate,
'red' as validate_color;
select
'hidden' as type,
'resource_id' as name,
'1234' as value;
select
'confirm' as name,
'Please type "sensitive resource" here to confirm the deletion' as label,
TRUE as required;
Result
Example 8
This example illustrates the use of custom validation buttons and half-width fields.
select
'form' as component,
'User' as title,
'Create new user' as validate,
'green' as validate_color,
'Clear' as reset;
select
'first_name' as name,
'First name' as label,
'John' as placeholder,
4 as width;
select
'middle_name' as name,
'Middle name' as label,
'Fitzgerald' as placeholder,
4 as width;
select
'last_name' as name,
'Last name' as label,
'Doe' as placeholder,
4 as width;
select
'email' as name,
'Email' as label,
'[email protected]' as placeholder,
12 as width;
select
'password' as name,
'Password' as label,
'password' as type,
6 as width;
select
'password_confirmation' as name,
'Password confirmation' as label,
'password' as type,
6 as width;
select
'terms' as name,
'I accept the terms and conditions' as label,
'checkbox' as type,
TRUE as required;
Result
Example 9
File upload
You can use the file
type to allow the user to upload a file.
The file will be uploaded to the server, and you will be able to access it using the
sqlpage.uploaded_file_path
function.
Here is how you could save the uploaded file to a table in the database:
INSERT INTO uploaded_file(name, data)
VALUES (
:filename,
sqlpage.read_file_as_data_url(sqlpage.uploaded_file_path('my_file'))
)
select
'form' as component,
'multipart/form-data' as enctype,
'Upload a picture' as title,
'Upload' as validate,
'examples/handle_picture_upload.sql' as action;
select
'my_file' as name,
'file' as type,
'image/png, image/jpeg' as accept,
'Picture' as label,
'Upload a small picture' as description,
TRUE as required;
Result
Example 10
Form Encoding
You can specify the way form data should be encoded by setting the enctype
top-level property on the form.
You may also specify formenctype
on submit
and image
type inputs.
This will take precedence over the enctype
specified on the form and is
useful in the case there are multiple submit
buttons on the form.
For example, an external site may have specific requirements on encoding type.
As a rule of thumb, multipart/form-data
is best when fields may contain
copious non-ascii characters or for binary data such as an image or a file.
However, application/x-www-form-urlencoded
creates less overhead when
many short ascii text values are submitted.
select
'form' as component,
'post' as method,
'multipart/form-data' as enctype,
'Submit with different encoding types' as title,
'Submit with form encoding type' as validate,
'examples/handle_enctype.sql' as action;
select
'data' as name,
'text' as type,
'Data' as label,
TRUE as required;
select
'percent_encoded' as name,
'submit' as type,
'Submit as' as label,
4 as width,
'examples/handle_enctype.sql' as formaction,
'application/x-www-form-urlencoded' as formenctype,
'application/x-www-form-urlencoded' as value;
select
'multipart_form_data' as name,
'submit' as type,
'Submit as' as label,
4 as width,
'examples/handle_enctype.sql' as formaction,
'multipart/form-data' as formenctype,
'multipart/form-data' as value;
Result
Example 11
Bulk data insertion
You can use the file
type to allow the user to upload a CSV
file containing data to insert in a table.
SQLPage can load data from a CSV file and insert it into a database table.
SQLPage re-uses PostgreSQL's COPY
syntax
to specify the format of the CSV file, but makes it work with all supported databases.
When connected to a PostgreSQL database, SQLPage will use the native
COPY
statement, for super fast and efficient on-database CSV parsing. But it will also work transparently with other databases, by parsing the CSV locally and emulating the same behavior with simpleINSERT
statements.
Here is how you could easily copy data from a CSV to a table in the database:
copy product(name, description) from 'product_data_input'
with (header true, delimiter ',', quote '"');
If you want to pre-process the data before inserting it into the final table, you can use a temporary table to store the data, and then insert it into the final table:
-- temporarily store the data in a table with text columns
create temporary table if not exists product_tmp(name text, description text, price text);
delete from product_tmp;
-- copy the data from the CSV file into the temporary table
copy product_tmp(name, description, price) from 'product_data_input';
-- insert the data into the final table, converting the price column to an integer
insert into product(name, description, price)
select name, description, CAST(price AS integer) from product_tmp
where price is not null and description is not null and length(description) > 10;
This will load the processed CSV into the product table, provided it has the following structure:
name,description,price
"SQLPage","A tool to create websites using SQL",0
"PostgreSQL","A powerful open-source relational database",0
"SQLite","A lightweight relational database",0
"MySQL","A popular open-source relational database",0
select
'form' as component,
'CSV import' as title,
'Load data' as validate,
'examples/handle_csv_upload.sql' as action;
select
'product_data_input' as name,
'file' as type,
'text/csv' as accept,
'Products' as label,
'Upload a CSV with a name, description, and price columns' as description,
TRUE as required;