SQLite ignore blank search criteria
lazarus / 16-Databases

----------------------------------------------------------------------
Hikari no Tenshi · 2014-12-11 09:48

Hi all.
I'm a beginner in programming, so I need Ypur help.

I have code to search my database:

Code: [Select]procedure TForm1.Button5Click(Sender: TObject);
begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='SELECT * FROM vymoghy WHERE (record_count =
"'+Edit1.text+'") and (strftime("%Y-%m-%d", data_vymoghy) >=
"'+FormatDateTime('YYYY-MM-DD', ZVDateTimePicker1.DateTime)+'" and
strftime("%Y-%m-%d", data_vymoghy) <= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker2.DateTime)+'") and (borzhnyk LIKE "%'+Edit2.text+'%")
and (kod_borzhnyka = "'+Edit3.text+'")';
   SQLQuery1.Active:=True;
end;

How to make it to ignore empty Edit and unchecked DateTimePicker?

Thanks.

----------------------------------------------------------------------
Never · 2014-12-11 10:33

Add before your [ SQLQuery1.Active:=False; ]

for the [ empty Edit  ]
Code: [Select]if lenght(trim(edit.text))=0 then exit;
and do somthing similar for the DateTimePicker value as well

----------------------------------------------------------------------
Hikari no Tenshi · 2014-12-11 10:57

Thanks, but I think it's not the thing I need to.

Program must do search by 1 or 2 criteria and ignore other empty.

I think, I need something like this:
Code: [Select]WHERE
(case when '{Edit1}'='' then 1=1 else record_count = '{Edit1}' end)
AND
(case when ifnull({DateTimePicker1},1)=1 then 1=1 else data_vymoghy >=
{DateTimePicker1} end) AND
(case when ifnull({DateTimePicker2},1)=1 then 1=1 else data_vymoghy <=
{DateTimePicker2} end) AND
(case when '{Edit2}'='' then 1=1 else borzhnyk LIKE '%{Edit2}%' end)
AND
(case when '{Edit3}'='' then 1=1 else kod_borzhnyka = '{Edit3}' end)

----------------------------------------------------------------------
Never · 2014-12-11 11:15

oh i see i'm sorry forked on the [and] in your sql string
but i'm confused....
you want the values from the coresponting fields in any case?
regardless the values of the edit and datepicker?
if this is the case then select them but  do not add them in the query
or
if you want the values any way and you might perfom a query as well
then you can use an [ or ] in place of the [ and  ] operator for these
fields

----------------------------------------------------------------------
Hikari no Tenshi · 2014-12-11 11:41

[OR] doesn't work as expected.
All Edit's and DateTimePicker's must be in query, but they must be
ignored if empty or null.
Query must return result, even if only one criteria is not empty and
it found match on corresponding field of table.

----------------------------------------------------------------------
Never · 2014-12-11 12:07

Quote[OR] doesn't work as expected.never had any issues with this
can't see why is not working for you thought...
QuoteAll Edit's and DateTimePicker's must be in query, but they must
be ignored if empty or null.
you can create seperate queries depenting on Quoteif empty or nullif
empty or nul
     yourSql1
else
    yourSql2

----------------------------------------------------------------------
Hikari no Tenshi · 2014-12-11 12:19

I have another program, that I made with constructor My Visual
Database, with search button query
Code: [Select]SELECT *
FROM
(
SELECT
zapyty.record_count,
strftime('%d.%m.%Y',zapyty.data),
zapyty.zmist,
zapyty.dokument,
zapyty.dodatok,
zapyty.adresat,
user.name,
zapyty.prymitka

FROM zapyty
LEFT JOIN user ON zapyty.id_user = user.id

WHERE
(case when '{EditCounter1}'='' then 1=1 else zapyty.record_count =
'{EditCounter1}' end) AND
(case when ifnull({DateTimePicker1},1)=1 then 1=1 else zapyty.data >=
{DateTimePicker1} end) AND
(case when ifnull({DateTimePicker2},1)=1 then 1=1 else zapyty.data <=
{DateTimePicker2} end) AND
(case when '{Edit1}'='' then 1=1 else zapyty.zmist LIKE '%{Edit1}%'
end) AND
(case when '{Edit2}'='' then 1=1 else zapyty.adresat LIKE '%{Edit2}%'
end) AND
(case when {ComboBox1}=-1 then 1=1 else user.id = '{ComboBox1}' end)
AND
(case when '{Edit3}'='' then 1=1 else zapyty.prymitka LIKE '%{Edit3}%'
end)

ORDER BY
zapyty.record_count DESC

LIMIT 200

) as zapyty
ORDER BY
zapyty.record_count ASC;
and it works.
But I don't know how to make it as simple as there.

----------------------------------------------------------------------
Never · 2014-12-11 12:30

use this
Code: [Select]SELECT
zapyty.record_count,
strftime('%d.%m.%Y',zapyty.data),
zapyty.zmist,
zapyty.dokument,
zapyty.dodatok,
zapyty.adresat,
user.name,
zapyty.prymitka

FROM zapyty
LEFT JOIN user ON zapyty.id_user = user.idto create a view in sqlite
db
and perform your queries on the created view

----------------------------------------------------------------------
Hikari no Tenshi · 2014-12-11 21:39

Well, it's isn't right and it's ugly, but my only working code is:
Code: [Select]procedure TForm1.Button5Click(Sender: TObject);
begin
   if (Form1.Edit1.text<>'') and
(Form1.ZVDateTimePicker1.Checked=False) and
(Form1.ZVDateTimePicker2.Checked=False) and (Form1.Edit2.text='') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where record_count =
"'+Edit1.text+'"';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=True) and
(Form1.ZVDateTimePicker2.Checked=False) and (Form1.Edit2.text='') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='SELECT * FROM vymoghy WHERE
strftime("%Y-%m-%d", data_vymoghy) >= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker1.DateTime)+'"';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=False) and
(Form1.ZVDateTimePicker2.Checked=True) and (Form1.Edit2.text='') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='SELECT * FROM vymoghy WHERE
strftime("%Y-%m-%d", data_vymoghy) <= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker2.DateTime)+'"';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=False) and
(Form1.ZVDateTimePicker2.Checked=False) and (Form1.Edit2.text<>'') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='SELECT * FROM vymoghy WHERE borzhnyk LIKE
"%'+Edit2.text+'%"';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=False) and
(Form1.ZVDateTimePicker2.Checked=False) and (Form1.Edit2.text='') and
(Form1.Edit3.text<>'') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='SELECT * FROM vymoghy WHERE kod_borzhnyka LIKE
"'+Edit3.text+'"';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text<>'') and
(Form1.ZVDateTimePicker1.Checked=True) and
(Form1.ZVDateTimePicker2.Checked=False) and (Form1.Edit2.text='') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where (record_count =
"'+Edit1.text+'") and (strftime("%Y-%m-%d", data_vymoghy) >=
"'+FormatDateTime('YYYY-MM-DD', ZVDateTimePicker1.DateTime)+'")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=True) and
(Form1.ZVDateTimePicker2.Checked=True) and (Form1.Edit2.text='') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where
(strftime("%Y-%m-%d", data_vymoghy) >= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker1.DateTime)+'") and (strftime("%Y-%m-%d",
data_vymoghy) <= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker2.DateTime)+'")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=False) and
(Form1.ZVDateTimePicker2.Checked=True) and (Form1.Edit2.text<>'') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where
(strftime("%Y-%m-%d", data_vymoghy) <= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker2.DateTime)+'") and (borzhnyk LIKE
"%'+Edit2.text+'%")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=False) and
(Form1.ZVDateTimePicker2.Checked=False) and (Form1.Edit2.text<>'') and
(Form1.Edit3.text<>'') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where (borzhnyk LIKE
"%'+Edit2.text+'%") and (kod_borzhnyka LIKE "'+Edit3.text+'")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text<>'') and
(Form1.ZVDateTimePicker1.Checked=False) and
(Form1.ZVDateTimePicker2.Checked=False) and (Form1.Edit2.text='') and
(Form1.Edit3.text<>'') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where (record_count =
"'+Edit1.text+'") and (kod_borzhnyka LIKE "'+Edit3.text+'")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=True) and
(Form1.ZVDateTimePicker2.Checked=False) and (Form1.Edit2.text='') and
(Form1.Edit3.text<>'') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where
(strftime("%Y-%m-%d", data_vymoghy) >= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker1.DateTime)+'") and (kod_borzhnyka LIKE
"'+Edit3.text+'")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=False) and
(Form1.ZVDateTimePicker2.Checked=True) and (Form1.Edit2.text='') and
(Form1.Edit3.text<>'') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where
(strftime("%Y-%m-%d", data_vymoghy) <= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker2.DateTime)+'") and (kod_borzhnyka LIKE
"'+Edit3.text+'")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text<>'') and
(Form1.ZVDateTimePicker1.Checked=False) and
(Form1.ZVDateTimePicker2.Checked=False) and (Form1.Edit2.text<>'') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where (record_count =
"'+Edit1.text+'") and (borzhnyk LIKE "%'+Edit2.text+'%")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text<>'') and
(Form1.ZVDateTimePicker1.Checked=False) and
(Form1.ZVDateTimePicker2.Checked=True) and (Form1.Edit2.text='') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where (record_count =
"'+Edit1.text+'") and (strftime("%Y-%m-%d", data_vymoghy) <=
"'+FormatDateTime('YYYY-MM-DD', ZVDateTimePicker2.DateTime)+'")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=True) and
(Form1.ZVDateTimePicker2.Checked=False) and (Form1.Edit2.text<>'') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where
(strftime("%Y-%m-%d", data_vymoghy) >= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker1.DateTime)+'") and (borzhnyk LIKE
"%'+Edit2.text+'%")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text<>'') and
(Form1.ZVDateTimePicker1.Checked=True) and
(Form1.ZVDateTimePicker2.Checked=True) and (Form1.Edit2.text<>'') and
(Form1.Edit3.text<>'') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where (record_count =
"'+Edit1.text+'") and (strftime("%Y-%m-%d", data_vymoghy) >=
"'+FormatDateTime('YYYY-MM-DD', ZVDateTimePicker1.DateTime)+'") and
(strftime("%Y-%m-%d", data_vymoghy) <= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker2.DateTime)+'") and (borzhnyk LIKE "%'+Edit2.text+'%")
and (kod_borzhnyka LIKE "'+Edit3.text+'")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=True) and
(Form1.ZVDateTimePicker2.Checked=True) and (Form1.Edit2.text<>'') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where
(strftime("%Y-%m-%d", data_vymoghy) >= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker1.DateTime)+'") and (strftime("%Y-%m-%d",
data_vymoghy) <= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker2.DateTime)+'") and (borzhnyk LIKE
"%'+Edit2.text+'%")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=True) and
(Form1.ZVDateTimePicker2.Checked=True) and (Form1.Edit2.text<>'') and
(Form1.Edit3.text<>'') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where
(strftime("%Y-%m-%d", data_vymoghy) >= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker1.DateTime)+'") and (strftime("%Y-%m-%d",
data_vymoghy) <= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker2.DateTime)+'") and (borzhnyk LIKE "%'+Edit2.text+'%")
and (kod_borzhnyka LIKE "'+Edit3.text+'")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text<>'') and
(Form1.ZVDateTimePicker1.Checked=True) and
(Form1.ZVDateTimePicker2.Checked=True) and (Form1.Edit2.text='') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where (record_count =
"'+Edit1.text+'") and (strftime("%Y-%m-%d", data_vymoghy) >=
"'+FormatDateTime('YYYY-MM-DD', ZVDateTimePicker1.DateTime)+'") and
(strftime("%Y-%m-%d", data_vymoghy) <= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker2.DateTime)+'")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text<>'') and
(Form1.ZVDateTimePicker1.Checked=True) and
(Form1.ZVDateTimePicker2.Checked=True) and (Form1.Edit2.text<>'') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where (record_count =
"'+Edit1.text+'") and (strftime("%Y-%m-%d", data_vymoghy) >=
"'+FormatDateTime('YYYY-MM-DD', ZVDateTimePicker1.DateTime)+'") and
(strftime("%Y-%m-%d", data_vymoghy) <= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker2.DateTime)+'") and (borzhnyk LIKE
"%'+Edit2.text+'%")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=False) and
(Form1.ZVDateTimePicker2.Checked=True) and (Form1.Edit2.text<>'') and
(Form1.Edit3.text<>'') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where
(strftime("%Y-%m-%d", data_vymoghy) <= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker2.DateTime)+'") and (borzhnyk LIKE "%'+Edit2.text+'%")
and (kod_borzhnyka LIKE "'+Edit3.text+'")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text<>'') and
(Form1.ZVDateTimePicker1.Checked=True) and
(Form1.ZVDateTimePicker2.Checked=False) and (Form1.Edit2.text<>'') and
(Form1.Edit3.text<>'') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy where (record_count =
"'+Edit1.text+'") and (strftime("%Y-%m-%d", data_vymoghy) >=
"'+FormatDateTime('YYYY-MM-DD', ZVDateTimePicker1.DateTime)+'") and
(borzhnyk LIKE "%'+Edit2.text+'%") and (kod_borzhnyka LIKE
"'+Edit3.text+'")';
   SQLQuery1.Active:=True;
   end
   else if (Form1.Edit1.text='') and
(Form1.ZVDateTimePicker1.Checked=False) and
(Form1.ZVDateTimePicker2.Checked=False) and (Form1.Edit2.text='') and
(Form1.Edit3.text='') then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:='select * from vymoghy';
   SQLQuery1.Active:=True;
   end;
end;

----------------------------------------------------------------------
LacaK · 2014-12-12 07:05

You can use something like this:

  sqlwhere := '';
  if edit1.Text <> '' then
    sqlwhere := Format('%s and column1=''%s''', [sqlwhere,
edit1.Text]);
  if edit2.Text <> '' then
    sqlwhere := Format('%s and column2=''%s''', [sqlwhere,
edit2.Text]);
  ...

  delete(sqlwhere,1,3); // remove leading AND
  SQL.Text:= 'select * from vymoghy where' + sqlwhere;

----------------------------------------------------------------------
Hikari no Tenshi · 2014-12-12 12:32

Thanks.
But in this code I can't use:
strftime("%Y-%m-%d", data_vymoghy) >= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker1.DateTime)+'"
and
borzhnyk LIKE "%'+Edit2.text+'%".

----------------------------------------------------------------------
Hikari no Tenshi · 2014-12-12 12:48

My code:
Code: [Select]procedure TForm1.Button5Click(Sender: TObject);
   var
   sqlwhere: string;
begin
   sqlwhere := '';
   if Edit1.Text <> '' then
   sqlwhere := Format('%s and %s', [sqlwhere, 'record_count =
"'+Edit1.text+'"']);
   if ZVDateTimePicker1.Checked=True then
   sqlwhere := Format('%s and %s', [sqlwhere, 'strftime("%Y-%m-%d",
data_vymoghy) >= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker1.DateTime)+'"']);
   if ZVDateTimePicker2.Checked=True then
   sqlwhere := Format('%s and %s', [sqlwhere, 'strftime("%Y-%m-%d",
data_vymoghy) <= "'+FormatDateTime('YYYY-MM-DD',
ZVDateTimePicker2.DateTime)+'"']);
   if Edit2.Text <> '' then
   sqlwhere := Format('%s and %s', [sqlwhere, 'borzhnyk LIKE
"%'+Edit2.text+'%"']);
   if Edit3.Text <> '' then
   sqlwhere := Format('%s and %s', [sqlwhere, 'kod_borzhnyka =
"'+Edit3.text+'"']);

   delete(sqlwhere,2,3);

   if sqlwhere='' then
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:= 'select * from vymoghy';
   SQLQuery1.Active:=True;
   end else
   begin
   SQLQuery1.Active:=False;
   SQLQuery1.SQL.Text:= 'select * from vymoghy where' + sqlwhere;
   SQLQuery1.Active:=True;
   end;
end;

----------------------------------------------------------------------
LacaK · 2014-12-12 12:57

Where is problem with this code?

----------------------------------------------------------------------
Hikari no Tenshi · 2014-12-12 13:02

No, no.
Code above is working great. Thanks for hint.