Welcome Guest, Not a member yet? Register   Sign In
How to show date rows equals current date?
#1
Question 

We have a human resource management system that need some modifications. I'd like to show all personnel on leave using the date stamp.
PHP Code:
<?php
                                    $link 
mysqli_connect("localhost""root""""ihrmis");
                                    
$result mysqli_query($link"select COUNT(employee_id) AS count FROM ats_leave_apps WHERE DATE('M Y') = CURDATE()");
                                    
                                    if(!
$result) {
                                        die(
'Error: 'mysqli_error($link));
                                    } else {
                                        
$num_rows mysqli_fetch_assoc($result);
                                        echo 
$num_rows['count'];
                                    }
                                    
?>

Below is the structure of our table where to get those datas
   

We want that if the date rows equal to the current date it will show in the dashboard of our system. I hope some generous hearts to help us. Thanks in advance.
Reply
#2

@jelz2018,

What are the field names for the Month, Day and Year? This will be helpful in figuring out how the query statement should be written.
Reply
#3

(This post was last modified: 03-02-2019, 02:04 AM by jelz2018.)

(03-01-2019, 07:23 AM)php_rocs Wrote: @jelz2018,

What are the field names for the Month, Day and Year?  This will be helpful in figuring out how the query statement should be written.

The field names are located in ats_leave_apps table where in if you check the attachment image the field name "multiple" is for Day, and the other are month and year. I'm not so sure on how to combine those fields multiple,month and year so that if the current date will be equal to those fields the query will count those and show in our dashboard. I hope I explained it well for your reference. Thanks a lot in advance.
Reply
#4

I need your help about this.
Reply
#5

@jelz2018,

Combining them isn't the problem. I noticed in your example image that you had a date of 11-13, that is going to be a problem. I do have a suggestion for the rest. Here you go.

Assuming that you have access to MySQL directly try testing this first (to make sure it works):
select DATE(concat(year,'-',month,'-',multiple)) as CombinedDate from ats_leave_apps

If it works (it will not work for the records that have a date range) then try this:
select COUNT(employee_id) AS count FROM ats_leave_apps WHERE DATE(concat(year,'-',month,'-',multiple)) = CURDATE()")

The next issue that you will have will be the multiple column. You will need to determine how you wish to handle date ranges. You can create a if condition to determine what value should represent the date. Once you determine how you want to handle it let me know and I can probably give you a suggestion.
Reply
#6

(03-02-2019, 07:20 PM)php_rocs Wrote: @jelz2018,

Combining them isn't the problem.  I noticed in your example image that you had a date of 11-13, that is going to be a problem.  I do have a suggestion for the rest.  Here you go.

Assuming that you have access to MySQL directly try testing this first (to make sure it works):
   select DATE(concat(year,'-',month,'-',multiple)) as CombinedDate from ats_leave_apps

If it works (it will not work for the records that have a date range) then try this:
select COUNT(employee_id) AS count FROM ats_leave_apps WHERE DATE(concat(year,'-',month,'-',multiple)) = CURDATE()")

The next issue that you will have will be the multiple column.  You will need to determine how you wish to handle date ranges.  You can create a if condition to determine what value should represent the date.  Once you determine how you want to handle it let me know and I can probably give you a suggestion.

This is the result when I run the command.

PHP Code:
select DATE(concat(year,'-',month,'-',multiple))
MySQL saidDocumentation

#1054 - Unknown column 'year' in 'field list' 
Reply
#7

And also do the multiple column will be recognized by the system as the day column?
Reply
#8

@jelz2018,

What we are doing is putting together a string that should be converted to a date value in order for it to be compared to the current date value.

Basically what select DATE(concat(year,'-',month,'-',multiple)) should give you is (1st record)... '2018-07-30'

The error message that you received is stating that the year field does not exist. Is that right? If so, what is the actual field name?

The day column (multiple) is a little bit tricky. We will need to put a condition in place to handle how it is used in the concat function.
Reply
#9

It's working sir but not working in date ranges. I don't know how to make it work. I tried to file a leave with date range eg. March 5-6, 2019 not working.
Reply
#10

@jelz2018,

I have a suggestion on how to get the date range to work but I need to know some more information. Do you wish to use both the beginning and ending dates or just the beginning date or the ending date of the date range? This is important because it will allow me to suggest a solution for you. Please let me know which one you prefer.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB