I want to create a PHP MySQL where I can create the following but i'm not sure how to do it with my little knowledge of programming.

Let me give you abit off back ground on the basic structure of the problem.

We have deliveries on site five days a week almost 24hrs a day.

Monday 06:00 to 06:00 Tuesday
Tuesday 06:00 to Wednesday 06:00
Wednesday 06:00 to Thursday 06:00
Thursday 06:00 to Friday 06:00
Friday 06:00 to Friday 17:00

The plan is done on a weekly basis, i.e week 1, 2, 3, 4, 5, 6, 7 ..... 44, 45

The current plan we have is in excel that is on a shared drive but only in one location and other people cannot view it at other sites.

The dock plan is split into days, delivery schedules from Monday to Friday into work sheets at the bottom.

I want to create a similar type of plan that is split down into days on a PHP and MySQL platform.

I want to have two section to this Admin and User.

Admin will have features to add, edit update and delete tables.

Users will only be able to add to the table the fields like Arrival Time, On Bay Time, Off Bay Time etc. also only to be able to update these fileds if they make a mistake.

MySQL tables I can think off is

Week No: List of week numbers
Arrival Time: - Times that the trailers are expected to be on bay
Bay Number: - Bay number the trailer needs to put onto
Route Number: - The route number will be linked to the supplier on this route
Supplier Name: - Vendors name is related to the route number

Master Table: Displays all the data onto this from the above tables and also gives fileds to add the rest of the details into this table.

Can you please tell me if I will need join tables for the above tables.

The admin Area:
To create the following tables Initailly.

Week No: List of week numbers that will link all tables
table: id (Primary Auto int 11), week (varchar 3)

Arrival Time: List all the times from 06:00 to 05:30
table: id (Primary Auto int 11), time (varchar 10)

Bay Number: From 1 to 4
table: id (Primary Auto int 11), bay (varchar 10)

Route Number:
table: id (Primary Auto int 11), route (varchar 55)

Supplier Name:
table: id (Primary Auto int 11), suppliername (varchar 250)

Master table:
table:
id (Primary Auto int 25)
Plan Arrival Time
Bay no
Route no
Vendor/s
Carrier
Arrival Time
On Bay Time
Off Bay Time
Trailer No
Arrival Status
Bay Turnaround
Comments
Day
start Tip
Finish Tip
Stillages
Pallets
Labels
Modulars
Trailer Type
Shift User

The supplier table should be able to link it to the other tables.
i.e.

Supplier Name: ..........................
Route No: .................................. (Drop Down List)
Time: ........................................ (Drop Down List)
Bay No: ..................................... (Drop Down List)
Week No: .................................. (Drop Down List)

These fileds need to be prefilled for the week day and Week number (users cannot change or amend this data in the master table) which I believe can be done with a query and echo code.

As charts are need to view and measure the information, the master table needs to be exported to an excel file and saved by a file name and week number.

i.e. The admin can view all the information added to the master table by the user and clicks a button (export to ms excel) and all the information is carried to the excel file.


Login Area:
User login

user to login and create and edit dock plan area

When user logs in the record being updated or edit caries the users id so they can be identified who created and edit the records with name, date and time

usual features to include rememeber me and forgot password facility

Admin Login

Admin to create dock plan with certain fields to fill in for user ie like supplier and route number etc

manage users - edit, delete update status etc

Thank you.

Kind Regards

Qaysar