Barand

Are those the actual results that you expect from the SQLFiddle data?

Badgers? You think us insane? Our questions, it seems, are in vain Cos the senior staff Seem to think it's a laugh To look on the rest with disdain.

for ($c=0, $a='a'; $c < $num; $c++, $a++) { //echo "($a) " . $data[$c] . "<br />\n"; }

Are there any plans to offer an explanation for the blackout?

You can use ALTER TABLE to change the auto inc value, or set it on creation of the table but easiest would be to add a dummy invoice with a value 1 less than the required start value - it can later be deleted.

I would suggest a form something like this for editing the "sched_hours" table so part-time working (Dr Doe doesn't work on Wed afternoon and Mrs Malone doesn't work Fridays) and mid-day breaks can be taken into account. Weekend working could also be easily added if required.

Do you think you are biting off more than you can chew with this as your first end-of-week assignment?

And you are trying to use the same statement syntax for update and delete that you used for insert. They all use different statements syntaxes. http://dev.mysql.com/doc/refman/5.6/en/insert.html http://dev.mysql.com/doc/refman/5.6/en/update.html http://dev.mysql.com/doc/refman/5.6/en/delete.html

All master files ie all except booking and patient_note.

I'd start by creating the code to maintain (add, edit, delete) the tables then you can use those to create the test data to use when coding and testing the main processes (booking and reporting)

It was created in Excel and exported as html. Now I looked at the file it's apparent M$ don't know what a .html file looks like. Here's a screenshot of the content.

Like this? SELECT * FROM DayMovie WHERE YEAR(FileDate) = 2015 AND MONTH(FileDate) = 1 ORDER BY FileDate DESC

Sorry, I don't understand what you are asking.

You are using a really inefficient dependent subquery which, for every record, queries the table to get the total miles for the vehicle. It would run quicker if you use a JOIN. EG SELECT id , datepicker , startmiles , endmiles , O.totalMiles , car , section , runningTotal.totalMiles FROM vehicle O INNER JOIN ( select id , sum(totalMiles) as totalMiles FROM vehicle GROUP BY id ) runningTotal USING (id) WHERE YEAR(datepicker) = '2015' ORDER BY O.id, datepicker DESC If, as the name suggests, you want a running total then use user variables in the query to accumulate the total EG SELECT id , datepicker , startmiles , endmiles , O.totalMiles , car , section , @rtot := IF(@id = id, @rtot+totalMiles, totalMiles) as runningTotal , @id := id as dummyId FROM vehicle O JOIN (SELECT @id:+0, @rtot:=0) as init WHERE YEAR(datepicker) = '2015' ORDER BY O.id, datepicker DESC

You should store an item of in formation, like names, in one place only. The only things that should be duplicated are id keys. I have added a duty_roster table which would hold staff_id date day start time day end time to assist in generating the table there is also a holiday table (to show non-available) and, for each staff, a sched_hours table (their normal working week pattern day of week, day start time, day end time). If you limit bookings to, say, a month in advance then the roster table will need to be generated each month in advance. You would also need a facility to edit this table for changes at short notice (which could generate cancellations so the patients will need to be notified) The roster table would define which days, staff and timeslots are available when making bookings. I added a "medical" flag to stafftype table to show which staff would see patients. An admin flag on the staff file shows who has admin access. tom7890.html

I would do as Cronix suggested. Look at all the outputs that you need to produce, as that will dictate what data you need to store. You then need to organize that data into correctly normalized tables. Look at the processes you need to produce those outputs and ensure that your data model will support those processes - by using the relational links in your model can you access the data required for the process to produce working, efficient queries? Remember that as soon as you decide you need a table of staff availabilty, say, then you need a process to maintain that table. Consider what could go wrong. Throw a few "what ifs" at the model. For example, if a doctor goes sick suddenly can the appointments be reallocated easily and patients/staff notified? What would happen if they decided to open on Saturday mornings, would they have to hold off that decision while someone took a few weeks to rewrite the system? When you are happy, start coding. In other words, before you set off on a journey, have a map.

The staff type in the proposed staff table would give the role

Is it then safe to assume that by the time the user reaches this application then it is known who they are and what type of user they are? Would this information be in the $_SESSION variables?

Then you did it wrong, but, without your posting the code you now have, we cannot say what you did wrong

You probably don't need both "available" and "not available" in the same record. You would hold one or the other. You need to decide which to hold, and how to hold that information, so that it will enable you to query your database to get the results you need for the various calendar and form views that your application needs to produce. (As Cronix said - the big picture). As for the doctor and nurse ids - will the patient see both a doctor and a nurse in the same time slot. If the answer is "No" then you only need a single "medstaff" id field. I raised the addition complexity questions to get you to think about them and how you will handle them. There are limits to what I will do in a free help forum. For instance, I am not going to design and write a whole system for free while you get paid for doing it

In that case it adds a whole new level of complexity. The booking table will require doctor/nurse id who is being consulted. The same booking table will provide doctors with their booking commitments. You need a table for the staff (id, name, staff type). The calendar key will depend on who is being seen by the patient. Doctors may not be available on a particular day or part day (sickness, holidays, out of office on patient home visits etc) in which case it would appear to be booked (or another category of "not available") This also applies to booking slots on the form You will therefore require the staff availability (or non-availability) in another table.

Something along these lines $year = date('Y'); $week = 4; $week -= 1; // zero-based wk no $dayno = $week * 7; $dt = DateTime::createFromFormat("Y-z", "{$year}-{$dayno}"); $dt1 = new DateTime("Last Sunday ".$dt->format('Y-m-d')); $dt2 = new DateTime("Next Saturday ".$dt->format('Y-m-d')); echo $dt1->format('D Y-m-d').'<br>'; //--> Sun 2015-01-18 echo $dt2->format('D Y-m-d').'<br>'; //--> Sat 2015-01-24

I had to modify the form - it was passing the wrong format date to cal_update.php. Updated versions attached. Who will be using the form? Is it online for patients to book their own appointments or is it for surgery staff to enter bookings when a patients rings up all calls in at reception to book an appointment?. This would have a bearing on the info shown. If it's staff then the comments can be shown, if it's patients then show only for data entry. You might also have to consider the availability of whoever the patients wants the appointment with. If their own doctor is not available then have a list of alternatives maybe. Perhaps consider storing who the appointment is with in the booking records also. We probably want to change "user" to "patient" Just my additional 0.02 worth. cal_form.php cal_update.php

cal_update.php doesn't exist yet. I have revised my version of the form (attached) to use checkboxes, as that is what you are now using. I can soon knock up an update page to process bookings using checkboxes if you want

There is an easier way echo (new DateTime("Last monday of May"))->format("Y-m-d"); //--> 2015-05-25 echo (new DateTime("Last thursday of November"))->format("Y-m-d"); //--> 2015-11-26