MySQl Query based on filter value

Question

I am having a form which contains some field(language,Datefrom,Dateto,Status).There is a submit button.I want to fetch the data based on the value filled on these field.My query to ignore the field which is not filled.How can i write the query.

Select * from tbldep where (language='$language') AND ( Date between '$Datefrom' AND '$Dateto')AND (status='$status')

My query to ignore the value which is not filled. Suppose user has not filled the value of date in DateFrom field in that case query should fetch the all record which are less than DateTo value.

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Apr 09 '23 at 11:41

score 0 · Answer 1 · answered Apr 09 '23 at 11:26

0

IFNULL. If there is no date, replace the null with a default that will get the data you want.

Select * 
  from tbldep 
  where (language='$language') 
    AND ( Date between IFNULL('$Datefrom', '1900/01/01') AND '$Dateto')
    AND (status='$status')

answered Apr 09 '23 at 11:26

Tom Boyd

385
1
7

If language is null or Dateto is null what will be query for that ? – gorakh Apr 09 '23 at 11:41
No idea. I don't know what your application does if someone doesn't fill out the boxes as you expect. More than that, I don't know what you want it to do. I suggest you build logic in to your application to set default values for each of these fields and restrict what uses can select from them. – Tom Boyd Apr 09 '23 at 12:25

score 0 · Answer 2 · answered Apr 09 '23 at 21:12

You can use boolean logic - although that’s a bit lengthy to express:

select * 
from tbldep 
where (:lang    is null or language = :lang) 
  and (:dt_from is null or date    >= :dt_from)
  and (:dt_to   is null or date    <= :dt_to)
  and (:stat    is null or status   = :stat)

Note that this uses bind parameters (with the : prefix) instead of string concatenation to build the query (which is unreliable and unsafe).

MySQl Query based on filter value

2 Answers2