php sql calendar add events

[Barand]

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

[tom7890]

no, i can do this i know i can,

 

my head is a bit all over the place at the moment. sorry got some bad news today and not being able to concentrate. 

 

maybe i should take some rest and then do the work in a lil while. 

 

i will post it wen i have it workin in a short while, i can do it i know 

Edited January 27, 2015 by tom7890

[Barand]

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.

[tom7890]

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)

 

I have the "ADD" for all master files except booking and patient_note.

 

An example of the code is below, is this the correct way of doing is? 

I have tested it and it is working as expected. 

<?php

if ( isset($_POST['staff_id']) && isset($_POST['date_from']) && isset($_POST['date_to'])) {
	
  $_POST = array_map("strip_tags", $_POST);
  $_POST = array_map("trim", $_POST);
$conn = new mysqli("localhost", "root", "", "system");

    // check connection
    if (mysqli_connect_errno()) {
      exit('Connect failed: '. mysqli_connect_error());
    }

  
    $adds['staff_id'] = $conn->real_escape_string($_POST['staff_id']);
    $adds['date_from'] = $conn->real_escape_string($_POST['date_from']);
	 $adds['date_to'] = $conn->real_escape_string($_POST['date_to']);

    // sql query for INSERT INTO users
    $sql = "INSERT INTO `holiday` (`staff_id`, `date_from`,`date_to`) VALUES ('". $adds['staff_id']. "', '". $adds['date_from']. "','". $adds['date_to']. "')"; 

    // Performs the $sql query on the server to insert the values
	
	
    if ($conn->query($sql) === TRUE) {
      echo 'users entry saved successfully';
    }
    else {
      echo 'Error: '. $conn->error;
    }

    $conn->close();
}
  ?>

 

[tom7890]

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.

 

I have created the table as your example and have got the dates into the table, but how do i add the get each day of the week to have timeslots? 

 

so  

 

mon - 09:00 - 09:15 etc..

tue- 09:00 - 09:15  etc.. 

 

atm i just have 

 

start_time 09:00 - 09:15

end_time 09:00 - 09:15 etc..

 

the weekdays have not been set to contain the timeslots from 09:00 till 18:00

[tom7890]

How do i get this so each day of the week can has time slots 09:00 til 18:00, inserted in to the database?

<?php
$db = new mysqli('localhost', 'root', '', 'system'); //  use your credentials

$sql = "TRUNCATE TABLE schedhours";
$db->query($sql);

// Prints the day
echo date("l") . "<br>";

$dt1 = new DateTime("08:00:00");
$dt2 = new DateTime("08:10:00");
$di = new DateInterval('PT10M');
$dp = new DatePeriod($dt1, $di, new DateTime('18:00:00'));
$Day = "1";

$sql = "INSERT INTO schedhours(week_day, start_time, end_time) VALUES (?,?,?)";
$smt = $db->prepare($sql);
$smt->bind_param('iss', $Day, $st,$et);

foreach ($dp as $d) {
    $st = $d->format('H:i');
    $et = $dt2->format('H:i');
    $dt2->add($di);
    $smt->execute();
}
?>

At the moment i have: 

 

weekday starttime   endtime

     1            09:00     09:15 

     1           09:15      09:30 

through till 6pm... 

     

 

how do i get so each day has timeslots within the same table?

 

weekday 2 timeslots 9 til 6

weekday 3 timeslots 9 til 6

weekday 4 timeslots 9 til 6

weekday 5 timeslots 9 til 6

weekday 6 timeslots 9 til 6

weekday 7 timeslots 9 til 6

 

Edited February 2, 2015 by tom7890

[tom7890]

ignore above posts as i cant delete them 

[Barand]

If you use the revised model that I gave you (attached) then you can use this form and update script to populate with the timeslots that each staff is normally available each day. To produce the calendar and booking form you would also take holidays into account.

 

cal_sched_form.php

cal_sched_updt.php

[tom7890]

i keep getting this error on this row

Fatal error: Call to a member function fetch_row() on a non-object 

this line of code

    while (list($sid,$ttl,$ln,$week_day,$abb,$tid) = $res->fetch_row()) {

i changed the variable names to match what i have, not sure what is incorrect

[Barand]

Neither am I. It worked when it left the shop but I don't know what you have changed.

 

I wonder? I use a weekday table to define all the days of the week

CREATE TABLE `weekday` (
  `dow` int(11) NOT NULL,
  `abbrev` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`dow`)
);

+-----+--------+
| dow | abbrev |
+-----+--------+
|   1 | Su     |
|   2 | Mo     |
|   3 | Tu     |
|   4 | We     |
|   5 | Th     |
|   6 | Fr     |
|   7 | Sa     |
+-----+--------+

Edited February 2, 2015 by Barand

[tom7890]

oh i added that in to the scheduled hours table 

[Barand]

the model would look like this

 

[Barand]

The sched_hours table will only contain records where the staff member is available whereas the form requires every day of the week for every staff member. As you can see from the query, this is ensured by the CROSS JOIN.

FROM staff
        CROSS JOIN weekday

[tom7890]

i have updated all my tables, but still i have the same error on the same line, im using the code you provided. 

 

is there a way to see what the error may be?

Edited February 2, 2015 by tom7890

[Barand]

change line 44

$res = $db->query($sql);

to

$res = $db->query($sql) or die( $db->error );

Edited February 2, 2015 by Barand

[tom7890]

it says 

 

Unknown column 'dow' in 'from clause'

 

fixed the error  

 

it works now  

Edited February 2, 2015 by tom7890

[tom7890]

Barand, 

 

You know the code which is linked with the calendar, when a date is selected you choose a user select a time slot and do the booking, is this all linked with the current database we are using?

I have noticed the booking tables it slightly different from the original or maybe i have done it differently, as its not in the new database tables diagrams so im not sure if it is or not. 

[tom7890]

Barand, 

 

You know the code which is linked with the calendar, when a date is selected you choose a user select a time slot and do the booking, is this all linked with the current database we are using?

I have noticed the booking tables it slightly different from the original or maybe i have done it differently, as its not in the new database tables diagrams so im not sure if it is or not. 

 

Do i need a users table?

[tom7890]

Do i need a users table?

 

i have deleted the calendar bit, i will need to redo that,  

[Barand]

 

 

You know the code which is linked with the calendar, when a date is selected you choose a user select a time slot and do the booking, is this all linked with the current database we are using?

The calendar code and booking form will need rewriting to take into account which member of staff the appointment is to be with and when they are available in terms of days and timeslots (from the holiday and schedhours tables)

 

 

 

I have noticed the booking tables it slightly different from the original or maybe i have done it differently, as its not in the new database tables diagrams so im not sure if it is or not.

booking table shown below.

 

 

Do i need a users table?

The staff and patients are the users. You have tables for those.

[tom7890]

okay, that does make sense now.

 

 

to be on the safe side so i dont delete anything else, is it correct my database is all set up now and i dont need add any more tables to the database? 

 

rewriting the calendar and booking code is the next task to complete?

[Barand]

These are the tables I have in my test db

mysql> show tables;
+---------------+
| Tables_in_db  |
+---------------+
| booking       |
| holiday       |
| patient       |
| patient_notes |
| sched_hours   |
| staff         |
| staff_type    |
| timeslot      |
| weekday       |
+---------------+

[tom7890]

yes i also have the  same ones 

Edited February 3, 2015 by tom7890

[tom7890]

The calendar code and booking form will need rewriting to take into account which member of staff the appointment is to be with and when they are available in terms of days and timeslots (from the holiday and schedhours tables)

 

 

I am working on calendar code bit by bit. 

 

The first section i have started on is the user menu, 

 

so the patient selects which doctor or nurse they would like to have an appointment with. 

 

is the following correct?

/*******************************************
* Create the options for the user menu
********************************************/
$patientOptions = "<option value=''>- select medical staff member -</option>\n";
$sql = "SELECT staff_id, staff_type, lname
FROM staff
ORDER BY lname";
$res = $db->query($sql);
while (list($i, $n) = $res->fetch_row()) {
$patientOptions .= "<option value='$i'>$n</option>\n";
}

[Barand]

This is the function that I used. You can pass it the currently selected staff member id ($sid) to show the currently selected one

function staffOpts($db, $sid=0) 
{
    /*******************************************
    * Create the options for the staff menu
    ********************************************/
    $staffOpts = "<option value=''>- select doctor/nurse -</option>\n";
    $sql = "SELECT staff_id, CONCAT_WS(' ',title,fname,lname) as name
            FROM staff s
            INNER JOIN staff_type t ON s.staff_type = t.staff_type_id
            WHERE t.medical = 1
            ORDER BY title , lname";
    $res = $db->query($sql);
    while (list($i, $n) = $res->fetch_row()) {
        $sel = $i==$sid ? 'selected="selected"' : '';
        $staffOpts .= "<option $sel value='$i'>$n</option>\n";
    }
    return $staffOpts;    
}

So the query returns

+----------+------------------+
| staff_id | name             |
+----------+------------------+
|        2 | Dr Jane Doe      |
|        1 | Dr Boris Johnson |
|        3 | Mrs Molly Malone |
+----------+------------------+

then

        Appointment with:
        <select name="sid" id="sid">
            <?=staffOpts($db, $staffID)?>
        </select><br><br>