Hi I want write the mysql query conditionally. I am basically using the Wordpress, please have a look at my same tables.
tbl1
id name
1 A
2 B
3 C
4 D
5 E
tbl2
id post_id key value
1 1 m_type 1
2 1 m_date 2013-11-22
3 2 m_type 2
4 2 m_date 2013-11-20
5 2 m_routine 2
6 3 m_type 2
7 3 m_date 2013-11-10
8 3 m_routine null
9 3 m_custom_rountine 5
I have the maintenance date in tbl2. So I want to get the all the post id filtered by following types.
Maintenance
- Due today
- Week due.
- Year due.
- over due.
tbl2 has the due date m_date
. But My problem when m_type = 1
. I need to add the where clause like WHERE CURDATE() = m_date
else m_type =2
means m_date will be the starting date of maintenance and I need to increment the date using m_routine or m_custom_routine.
For example,
m_type = 2
m_date = 2013-11-22
m_routine = 2
Then I want check
CURDATE() = 2013-11-22 OR 2013-11-24 OR 2013-11-26 etc.
I was thinking to use CASE .. WHEN... THEN
in WHERE clause. But I am not getting it properly.
Desired output
CURDATE() = 2013-11-22
Output post_id = 1,2 (2-routine incremented date from 2013-11-20)
CURDATE() = 2013-11-15
Output post_id = 3 (custom_routine incremented date from 2013-11-10) Hope I explained well enough. Please someone help me.
SUDO code
if(m_type == 1){
m_date = CURDATE();
}
else if(m_type == 2){
increment_date = m_date increment m_rountine (OR) m_date increment m_custom_rountine
CURDATE() = increment_date
}
Edit:-
I created the sql fiddle for your ref