A form that lets users apply custom filters to search through a table

[borisfindrik]

Hi everyone,
how can I substitute empty field in form for % in query?

for table

CREATE TABLE envvals (
  date text PRIMARY KEY, 
  temp1 text NOT NULL
);

data is like

date	temp1
2024-08-09 20:06:35	28.00
2024-08-09 20:11:36	27.00
2024-08-09 20:16:36	28.00
2024-08-09 20:21:36	28.40
2024-08-09 20:31:36	27.80

For form

select 
    'form'             as component,
    'env_data.sql'     as action;
select 
    'Date and Time' as name,
    'DateTime' as date;
select
    'Temperature' as name, 
    'Temp' as temp1;

I try

select date, temp1 as "temp(C)" 
from envvals
where
      CASE
        WHEN :DateTime IS NOT NULL THEN date like concat ('%',:DateTime,'%') 
        ELSE date like '%' 
      END
and
      CASE
        WHEN :Temp IS NOT NULL THEN temp1 between :Temp and :Temp + 1  
        ELSE temp1 like '%'
      END
order by date;

for

select date, temp1 as "temp(C)"
from envvals
where date like '%2024-08-09%' and temp1 like '%';

resulting far more data then expected

select count(*)
from envvals
where date like '%2024-08-09%' and temp1 like '%';

is 288 for 5 measurements per minute

Any input most welcome!

[lovasoa]

Hello and welcome to SQLPage !

It looks like you have a problem in your form. You should have 'DateTime' as name if you want to be able to be able to use :DateTime in the next page. You can have a variable name that is different from the displayed label by using the label property. You can replace your form queries with

select 
    'Date and Time' as label,
    'DateTime' as name;
select
    'Temperature' as label, 
    'Temp' as name;

Full working value filtering form example

select 'form' as component, 
       'Search' as validate, -- Creates a submit button with the text "Search"
       'GET' as method;      -- Form data will be sent as URL parameters

select 
    'Date' as label,         -- Text shown next to the input
    'DateTime' as name,      -- Parameter name used in URL and accessed via $DateTime
    'date' as type,          -- Creates a date picker input
    $DateTime as value;      -- Pre-fills input with existing value from URL

select 'Temperature' as label, 
       'Temp' as name, 
       'number' as type,     -- Creates an input that only accepts numeric values
       $Temp as value;       -- Pre-fills input with existing value from URL

select 'table' as component;

select date, temp1 as "temp(C)"
from envvals
where
      CASE
        WHEN COALESCE($DateTime,'') <> '' THEN  -- Works both when the URL parameter is absent (NULL) and when the field was left blank (empty string coming from the form) 
          date LIKE $DateTime || '%'            -- Matches date prefix, ignoring time
        ELSE true
      END
and
      CASE
        WHEN COALESCE($Temp,'') <> '' THEN     -- Handles NULL and checks for non-empty string
          $Temp <= temp1 AND temp1 < $Temp + 1 -- excludes upper bound
        ELSE true
      END
order by date;

Try it live online: https://replit.com/@pimaj62145/SQLPage-custom-value-filtering?v=1

[borisfindrik]

Thanks, this look awesome.

[lovasoa]

Good! Out of curiosity, what app are you building? Do you have a database connected to temperature sensors ?

[borisfindrik]

Naive prediction of rain. I don't think it will work :-)
I will see results soon after the first major weather change, so far is only hot.
Measuring two temperatures, humidity and pressure with BMP085 and AM2302 connected to esp32 that sends date to mqtt on RPi.
Python script reads the data and write to sqlite db.

[lovasoa]

Sounds like a fun project! Please share your write-up on this forum if you do one when you have your results!