I am trying to create a employee availability calendar. I have 2 tables in my MySQL database that
I believe need to be joined to get the result that I want.
Personnel:
employee_id | first_name | last_name | department_id |
Emp_Avail:
event_id | employee_id | start_date | end_date | leave_info |
I want the result to be like this:
| Month YYYY |
| T | W | T | F | S | S | M | T | W | .... | M |
first_name | last_name | dept_id | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | .... | 30 |
John | Smith | 7 | | V | V | V | - | - | | S | S | .... | |
Robert | Best | 7 | V | | | S | - | - | S | | | .... | |
Carrie | Williams | 5 | | | | V | - | - | V | V | V | .... | |
I was able to left join the table using the query below:
$mysql_join_table = "select P.employee_id, P.first_name, P.last_name, P.department_id,
E.event_id, E.employee_id, E.start_date, E.end_date, E.leave_info
from personnel P left join emp_avail E
on P.employee_id = E.employee_id
where P.is_active = 1
order by P.first_name";
$result = mysql_query($mysql_join_table);
However, the result is not as the result I desribe above.
| Month YYYY |
| T | W | T | F | S | S | M | T | W | .... | M |
first_name | last_name | dept_id | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | .... | 30 |
John | Smith | 7 | | V | V | V | - | - | | S | S | .... | |
John | Smith | 7 | | V | V | V | - | - | | S | S | .... | |
Robert | Best | 7 | V | | | S | - | - | S | | | .... | |
Carrie | Williams | 5 | | | | V | - | - | V | V | V | .... | |
The problems that I have are:
First
The result that I have does not check the records for the specific month. I want the result
to check whether the start/end date is in the Month that is selected. How to check that the
result will only display the records in the particular month and year (user select month and
year from drop-down list)? I have my start_date and end_date fields in my database as "date"
in YYYY-MM-DD. I believe I need to explode the dates and compare the months, I have this code:
$startDate = $row[6]; //$row[6] is array from $result which is array that content start_date
$exp_startDate = explode('-', $startDate);
$month_startDate = $exp_startDate[2];
$endDate = $row[7]; //$row[7] is array from $result which is array that content end_date
$exp_endDate = explode('-', $endDate);
$month_endDate = $exp_endDate[2];
but, I don't know if I did it corectly or what I have to do with it. How do I use it as a
condition in the query? javascript:smilie('🙁')
Second
With the Left Join query that I have, there are possibilities that one employee could have
more than one related records in Emp_Avail tables (i.e John Smith might have a vacation for a
couple of days and also sick for a couple of days in other occasion in the same month). If I
use the query, the employee's name that have more than one records will appear as many as the
records the employee has in the Emp_Avail table. But instead of showing it as rows, all I want
is that only its leave_info of the respective employee to be populated into related dates in that
specific month on the result page. If my "comparing month" problem is solved, can this problem
be solved base on the solution for my first problem? Do I need to create another array to solve
my second problem?
I hope you do not get confused of what my problems are because of my long explanation (I wish I
can make it shorter). I attach the full code of the result page that I have so far in case any
of you want to check.
I really appreciate any help from you guys or at least you can give me some idea about what I
have or need to do.
Thanks.
