subhra subhra - 1 month ago 9
MySQL Question

MySQL comparing dates doesn't return results

I need one help. I am unable to fetch value from table as per some condition using PHP and MySQL. I am explaining my table below.


db_subcat:


id subcat_id sub_name from_date to_date
------------------------------------------------------------
1 60 Ram 2016-10-25 2016-10-28
2 60 Ram
3 61 Raj


Here some row has no
from_date and to_date
value.I am explaining my query below.

$date="2016-10-23";
$sql="select * from db_subcat
where from_date <='".$date."' and to_date >= '".$date."' and from_date !='' and to_date !=''
group by subcat_id
union all select * from db_basic
where from_date ='' and to_date =''
group by sucat_id";


Here My problem is i can not get value while table has no entry for
(from_date ='' and to_date ='')
or
(from_date !='' and to_date !='')
.Here i need to fetch value using the all following conditions.

1- if
from_date and to_date
has value and if value exist then it will match.

2- If
from_date and to_date
has value or blank for both condition value should fetch.

3- if table has no entry for
(from_date ='' and to_date ='')
or
(from_date !='' and to_date !='')
.

Please help me to resolve this issue.

Answer

If I understood you correctly, you probably need to use ISNULL and you can combine the two queries :

select * from db_subcat
where ((from_date IS NULL OR from_date ='') AND (to_date IS NULL OR to_date =''))
   OR (from_date <='".$date."' and to_date >= '".$date."') 
group by subcat_id

Note that this GROUP BY clause is invalid in all DBMS except MySQL. You shouldn't do that with a SELECT * statement, always specify the columns and each column should be either in the GROUP BY clause or with an aggregation function wrapped around it.