My bad. The table structure posted was incomplete. The actual table structure includes a lot more fields, but they're not relevant to my question so I was trying to keep it simple. However the customer_address is the address table and I forgot to post the google_url field here.
This is the table structure
Code: Select all
job |job_employee |employee |customer |customer_address
---------------+-------------+-------------+--------------+---------------
job_id(PK) |assign_id(PK)|emp_id(PK) |cust_id(PK) |address_id(PK)
customer_id(FK)|job_id(FK) |emp_firstname|cust_firstname|customer_id(FK)
address_id (FK)|employee_id |emp_lastname |cust_lastname |google_url
job_date | | |special |
job_status | | | |
The variables in use are fine. I checked them. The variable $prEmployeeId[$i] is an array of the employee_id's that are to be displayed(basically out of all employees in the db that has any appointments for the current week being displayed. and yes it's the PK value of employee_id). The query gets the data for one employee only for one specific date, but is placed inside two for statements: one going Monday thru Sunday, and the other for all the employees that have scheduled jobs for the week being displayed.
To give you a basic idea what it looks like:
Code: Select all
| Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday
Employee | 05/01/08 | 05/02/08 | 05/03/08 | 05/04/08 | 05/05/08 | 05/06/08 | 05/07/08
---------+----------------+----------------+----------------+----------------+----------+----------+--------
Joe S. |[color=#FF0000]09:00 XYZ client[/color]|09:00 SGY client|[color=#FF0000]09:00 HGT client[/color]| OFF | OFF | OFF | OFF
---------+----------------+----------------+----------------+----------------+----------+----------+-----
Jim M. |[color=#FF0000]09:00 XYZ client[/color]|10:00 HYR client|[color=#FF0000]09:00 HGT client[/color]|10:00 KJH client| OFF | OFF | OFF
Normally employees have more than one jobs a day, but I kept it simple here. It's also possible that more than one employee is assigned to the same job. See appointments in red.
The job table does not include the employee assigned to the job. That's actually stored in the job_employee table with the job_id as a FK and each row with the same job_id would represent one employee that's assigned to the job. One, two or more...
My query is inside the 2 for statements pulling the data for each day for a specific employee, then the next day, then the next day, until you get to Sunday, when you start with the next employee...
- Pull all jobs for Joe on Monday
- Pull all jobs for Joe on Tuesday
- Pull all jobs for Joe on Wednesday
- Pull all jobs for Joe on Thursday
- Pull all jobs for Joe on Friday
- Pull all jobs for Joe on Saturday
- Pull all jobs for Joe on Sunday
then
- Pull all jobs for Jim on Monday
- Pull all jobs for Jim on Tueday
- Pull all jobs for Jim on Wednesday
- Pull all jobs for Jim on Thursday
- Pull all jobs for Jim on Friday
- Pull all jobs for Jim on Saturday
- Pull all jobs for Jim on Sunday
For displaying I need
- job.job_date to create the appointemnt time
- customer.special (long story... it's needed)
- customer.cust_id for a link
- customer.cust_firstname and lastname (duh, displaying the name)
- job.job_status for color coding the appointment for display based on the status
- customer_address.google_url is for a quick link to place customer's address on a google map
Hope this helps.