[SOLVED] Question about compare database using mySQL query

[Bigeyes]

Hello, I'm a staff at the university and working on PHP project. I have a difficulty of understanding and find a way to solve the problem that i have been looking at it for more than a week now. I would like to have some advice from expert and hopefully you guys can help me through this problem. Thanks in advance.

 

Here is what I want to do.

I have two main tables that contain all sort of data. The first one contain a template of staff schedule.

This one shows times and id for staff.

 

Next one is a weekly schedule.

This one contain a weekly schedule where staff can add their hours. Where they work, from what time to what time that they work, etc.

 

Last one is a Name ID

 

Now what I would like to do is to have the first table as a template and compare it with weekly schedule. If there is a missing shift or added shifts, it will shows on the PHP page.  So on a weekly database, it will take the end of the week and compare to 7 days template. I don't know how to compare it and the problem i see is weekly schedule only contain 8:00-10:00 (for example) but not 8:00, 8:30,9:00.9:30 and so on. How do i compare it?

I'm not sure that I explain it clear enough. If you have any suggestion or advice or even a link direct to where i can read about how to code this. Please let me know, I am greatly appreciate your help.

 

Thank you again.

[Wildbug]

Does this help?

 

SELECT times,
GROUP_CONCAT(DISTINCT IF(DAYNAME(schedule.date)="Monday",students.FirstName,NULL) ORDER BY FirstName SEPARATOR ", ") AS Monday,
GROUP_CONCAT(DISTINCT IF(DAYNAME(schedule.date)="Tuesday",students.FirstName,NULL) ORDER BY FirstName SEPARATOR ", ") AS Tuesday,
GROUP_CONCAT(DISTINCT IF(DAYNAME(schedule.date)="Wednesday",students.FirstName,NULL) ORDER BY FirstName SEPARATOR ", ") AS Wednesday,
GROUP_CONCAT(DISTINCT IF(DAYNAME(schedule.date)="Thursday",students.FirstName,NULL) ORDER BY FirstName SEPARATOR ", ") AS Thursday,
GROUP_CONCAT(DISTINCT IF(DAYNAME(schedule.date)="Friday",students.FirstName,NULL) ORDER BY FirstName SEPARATOR ", ") AS Friday,
GROUP_CONCAT(DISTINCT IF(DAYNAME(schedule.date)="Saturday",students.FirstName,NULL) ORDER BY FirstName SEPARATOR ", ") AS Saturday,
GROUP_CONCAT(DISTINCT IF(DAYNAME(schedule.date)="Sunday",students.FirstName,NULL) ORDER BY FirstName SEPARATOR ", ") AS Sunday
FROM
template LEFT JOIN schedule ON (template.times BETWEEN schedule.start_time AND schedule.end_time + 1)
-- AND YEARWEEK(date) = YEARWEEK(20070701)
LEFT JOIN students ON schedule.student_id=students.student_id
GROUP BY template.times;

+----------+--------+---------+----------------+----------+--------+----------+-------------------+
| times    | Monday | Tuesday | Wednesday      | Thursday | Friday | Saturday | Sunday            |
+----------+--------+---------+----------------+----------+--------+----------+-------------------+
| 07:30:00 | NULL   | NULL    | NULL           | NULL     | NULL   | NULL     | NULL              |
| 08:00:00 | NULL   | NULL    | Jennifer       | NULL     | NULL   | NULL     | Christopher, Sara |
| 08:30:00 | NULL   | NULL    | Jennifer       | NULL     | NULL   | NULL     | Christopher, Sara |
| 09:00:00 | NULL   | NULL    | Jennifer, Sara | NULL     | NULL   | NULL     | Christopher, Sara |
| 09:30:00 | NULL   | NULL    | Jennifer, Sara | NULL     | NULL   | NULL     | Christopher, Sara |
| 10:00:00 | NULL   | NULL    | Jennifer, Sara | NULL     | NULL   | NULL     | Christopher, Sara |
| 10:30:00 | NULL   | NULL    | Jennifer, Sara | NULL     | NULL   | NULL     | Christopher, Sara |
| 11:00:00 | NULL   | NULL    | Sara           | NULL     | NULL   | NULL     | Christopher, Sara |
| 11:30:00 | NULL   | NULL    | Sara           | NULL     | NULL   | NULL     | Christopher, Sara |
| 12:00:00 | NULL   | NULL    | Sara           | NULL     | NULL   | NULL     | Christopher, Sara |
| 12:30:00 | NULL   | NULL    | NULL           | NULL     | NULL   | NULL     | Sara              |
| 13:00:00 | NULL   | NULL    | NULL           | NULL     | NULL   | NULL     | NULL              |
| 13:30:00 | NULL   | NULL    | Jennifer       | NULL     | NULL   | NULL     | NULL              |
| 14:00:00 | NULL   | NULL    | Jennifer, Sara | NULL     | NULL   | NULL     | Sara              |
| 14:30:00 | NULL   | NULL    | Jennifer, Sara | NULL     | NULL   | NULL     | Sara              |
| 15:00:00 | NULL   | NULL    | Jennifer, Sara | NULL     | NULL   | NULL     | Sara              |
| 15:30:00 | NULL   | NULL    | Jennifer, Sara | NULL     | NULL   | NULL     | Sara              |
| 16:00:00 | NULL   | NULL    | Sara           | NULL     | NULL   | NULL     | Sara              |
+----------+--------+---------+----------------+----------+--------+----------+-------------------+

 

Uncomment the YEARWEEK portion to restrict the query to a particular week containing that date.

[Bigeyes]

Thank you very much Mr.(Ms.)Wildbug. I will give it a shot. Still trying to solve this problem today.

[Bigeyes]

I would like to thank you again. The query works great and that is what I wanted. Now I have a question about compare the database. I'm not 100% sure how to do that and should I do it in MySQL or PHP. If I want to take the result from what you show me and compare it to template table and display the missed or added shifts. What should I look for. I'm still new so some of the code that you typed in is very helpful to me. I really appreciate your help.

[Wildbug]

So you want to know if the student id from the schedule is different from the one in the template?

[Wildbug]

(Note: I'll be away for a week, so I probably won't be reading this thread.)

[Bigeyes]

So you want to know if the student id from the schedule is different from the one in the template?

 

Yes, I also want to display the one that is not follow the template and how are they different compare to the template. Thank you for your time. I will try during the week.

[Bigeyes]

My project is switch the direction so I will say this problem is solved. Thanks again Wildbug