Jump to content
tom7890

php sql calendar add events

Recommended Posts

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

Share this post


Link to post
Share on other sites

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 by tom7890

Share this post


Link to post
Share on other sites

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.

post-3105-0-82997300-1422361428_thumb.png

Share this post


Link to post
Share on other sites

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();
}
  ?>

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by tom7890

Share this post


Link to post
Share on other sites

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.

 

post-3105-0-22746000-1422900523_thumb.png

cal_sched_form.php

cal_sched_updt.php

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by tom7890

Share this post


Link to post
Share on other sites

change line 44

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

to

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

Share this post


Link to post
Share on other sites

it says 

 

Unknown column 'dow' in 'from clause'

 

fixed the error :) 

 

it works now :) 

Edited by tom7890

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Do i need a users table?

 

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

Share this post


Link to post
Share on other sites

 

 

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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       |
+---------------+

post-3105-0-89896200-1422964727_thumb.png

Share this post


Link to post
Share on other sites

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";
}

Share this post


Link to post
Share on other sites

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> 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.