+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: How to show date rows equals current date? (/showthread.php?tid=72921)
RE: How to show date rows equals current date? - php_rocs - 03-07-2019
@jelz2018,
We're in the home stretch. I'm definitely awake now after a good nights rest (I was starting to run on fumes). Ok, I see what the issue was, I had one to many parenthesis.
Here you go...
Code:
select count(employee_id) as count
from (
select employee_id,year,month,
case when LOCATE('-',multiple) > 0 then substring(multiple,1,locate('-',multiple) - 1)
else multiple end as 1st_date,
case when LOCATE('-',multiple) > 0 then substring(multiple,locate('-',multiple) + 1,length(multiple) - locate('-',multiple))
else multiple end as 2nd_date
from ats_leave_apps
) as d
where curdate() between (DATE(concat(year,'-',month,'-',1st_date)) - INTERVAL 1 DAY) and (DATE(concat(year,'-',month,'-',2nd_date))
+ INTERVAL 1 DAY)I fixed the previous code too.
RE: How to show date rows equals current date? - php_rocs - 03-07-2019
@jelz2018,
I should have done this last night it would have been easier (I probably was really tired). Anyway...here the query is with your sample data. Enjoy http://sqlfiddle.com/#!9/6831ef/1/0
RE: How to show date rows equals current date? - jelz2018 - 03-07-2019
The code is now perfectly working sir without any error. One last hit sir, do I need to put an (AND) if let's say I will add another condition like if 'approved' column will be equal to 1 which approved leave and 2 disapproved of course I want approved = 1. Thank you for your generous heart for helping me out on this. I know positive rating of 1 is not enough. But I sincerely thank you.
RE: How to show date rows equals current date? - php_rocs - 03-07-2019
@jelz2018,
Yes, you can add an AND if you need to. It was my pleasure helping. Challenges like this keeps my mind engaged and I enjoy helping others. I love doing it.
RE: How to show date rows equals current date? - jelz2018 - 03-07-2019
I'm having problem adding it. It gives me error. I added it after the last close parenthesis.
RE: How to show date rows equals current date? - php_rocs - 03-08-2019
@jelz2018,
Is the approved column in the ats_leave_apps table? if so, here is my solution http://sqlfiddle.com/#!9/4a8d64/2/0
I added the approved column to the FROM datasource (If I don't the select statement doesn't see the column from the datasource). This allows me to add it to the where statement.
RE: How to show date rows equals current date? - jelz2018 - 03-08-2019
Two thumbs up sir, the code now working smoothly. Thank you a lot. I hope in the future if I need some help I can find you. God bless you more as you are very generous to others.