Jump to content

php sql calendar add events


tom7890

Recommended Posts

  • Replies 131
  • Created
  • Last Reply

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 

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

 

Link to comment
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

Link to comment
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

 
Link to comment
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

Link to comment
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

Link to comment
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     |
+-----+--------+
Link to comment
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. 

Link to comment
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?

Link to comment
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.

Link to comment
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?

Link to comment
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";
}
Link to comment
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> 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.


×
×
  • Create New...

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.