php sql calendar add events

[tom7890]

i couldnt get it to work within my code i will have another try thanks

[Barand]

It isn't too difficult. I defined a class to highlight the cell with a 4 pixel border

td.now {
    border: 4px solid #883A66;
    width: 19px;
    height: 11px;
    padding: 0;
}

then applied that class to the cell with selected date.

[tom7890]

how to i change this to show a form not a href link for a new page 

 

    else echo "<td align='center' valign='middle' height='20px'><a href='timeslots.html'>". ($i - $startday + 1) . "</a></td> ";

[tom7890]

I dont understad the logic behind colouring the calendar to show the status of each day.. 

[Barand]

I dont understad the logic behind colouring the calendar to show the status of each day.. 

 

Do you want me to answer that here or in that (yet another) thread you have now raised?

[tom7890]

Here is my solution (uses the tables I suggested earlier in #12)

 

 

 

<?php
include("db_inc.php"); // defines DB credentials (HOST etc)
$db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

function getBookings($db, DateTime $d)
{
    $currY = $d->format('Y');
    $currM = $d->format('n');
    $sql = "SELECT b.date 
            , SUM(IF(t.timeslot_id IS NULL,0,1)) as bookedcount
            , free.freeslots
            FROM booking b
                LEFT JOIN
                (
                SELECT
                 date 
                 , GROUP_CONCAT(DISTINCT TIME_FORMAT(x.start_time, '%H:%i') 
                    ORDER BY start_time SEPARATOR ', ') as freeslots 
                FROM (
                    SELECT DISTINCT b.date
                    , t.timeslot_id
                    , t.start_time
                    FROM booking b
                    CROSS JOIN timeslot t
                    WHERE YEAR (b.date) = $currY 
                        AND MONTH(b.date) = $currM
                    ) as x
                    LEFT JOIN booking b USING (date,timeslot_id)
                WHERE b.timeslot_id IS NULL
                GROUP BY date   
                ) as free USING (date)
                LEFT JOIN timeslot t USING (timeslot_id)
                WHERE YEAR (b.date) = $currY 
                    AND MONTH(b.date) = $currM
            GROUP BY b.date" ;
    $books = array();
    $res = $db->query($sql);
    while (list($dt,$tot,$free) = $res->fetch_row()) {
        $books[$dt] = array($tot, $free);
    }
    return $books;
}

function makeCalendar(mysqli $db, DateTime $currdate)
{
    $dateStart = new DateTime($currdate->format('Y-m-01'));
    $bookings = getBookings($db, $dateStart);
    
    $di = new DateInterval('P1M');
    $prevDate = clone $dateStart;
    $nextDate = clone $dateStart;
    $prev = $prevDate->sub($di)->format('Y-m-d');
    $next = $nextDate->add($di)->format('Y-m-d');
    
    $dateEnd = new DateTime($dateStart->format('Y-m-t'));
    $dateEnd->modify('+1 days');
    $dp = new DatePeriod($dateStart, new DateInterval('P1D'), $dateEnd);
    $calArray = array_fill(0,7,'');    // dates in the calendar row
    $clsArray = array_fill(0,7,'');    // cell classes in the row
    $idarray = array_fill(0,7,'');     // cell ids in the row
    $tit =  array_fill(0,7,'');        // titles (tooltips) for the row
    
    /*******************************************
    * Create the calendar headings
    ********************************************/
    
    echo "<table border='1'>";
    echo "<tr><th><a href='?date=$prev'>«</a></th>
        <th colspan='5'>" . $dateStart->format('F Y') . "</th>
        <th><a href='?date=$next'>»</a></th></tr>";
    $days = array('Su', 'Mo', 'Tu', 'We', 'Th', 'Fr', 'Sa');
    echo "<tr>";
    foreach ($days as $k=>$v) {
        $cls = ($k==0)||($k==6) ? "class=' day we'":"class='day'";
        echo "<th $cls>$v</th>";
    }
    echo "</tr>\n";
    
    /*******************************************
    * Create the calendar rows
    ********************************************/
    foreach ($dp as $d) {
        $now = $d == $currdate ? ' now':'';
        $dow = $d->format('w');
        $tit[$dow] = '';
        $idarray[$dow] = $d->format('Y-m-d');
        $calArray[$dow] = $d->format('j');
        $clsArray[$dow] = ($dow==0)||($dow==6) ? "class='we $now'":"class='day $now'";
        if (isset($bookings[$d->format('Y-m-d')])) {
            $tit[$dow] = wordwrap($bookings[$d->format('Y-m-d')][1],34);
            if ($bookings[$d->format('Y-m-d')][1])
                $clsArray[$dow] = "class='day partial $now'";
            else
                $clsArray[$dow] = "class='day full $now'";
        }
        if ($dow==6) {
            echo '<tr>';
            foreach ($calArray as $k=>$v) {
                $dcls = $v=='' ? "class='blank'" : $clsArray[$k];
                echo "<td title='{$tit[$k]}' id='{$idarray[$k]}' $dcls>$v</td>";
            }
            echo "</tr>\n";
            $calArray = array_fill(0,7,'');
            $clsArray = array_fill(0,7,'');
            $idarray = array_fill(0,7,'');
            $tit =  array_fill(0,7,'');
        }
    }
    if ($dow!= 6) {
        echo '<tr>';
        foreach ($calArray as $k=>$v) {
                $dcls = $v=='' ? "class='day blank'" : $clsArray[$k];
                echo "<td title='{$tit[$k]}' id='{$idarray[$k]}' $dcls>$v</td>";
            }
        echo "</tr>\n";
    }
    
    echo "</table>\n";
}



function bookingForm($db, DateTime $date)
{
    /*******************************************
    * Create the options for the user menu
    ********************************************/
    $userOpts = "<option value=''>- select user -</option>\n";
    $sql = "SELECT user_id, name
            FROM user
            ORDER BY name";
    $res = $db->query($sql);
    while (list($i, $n) = $res->fetch_row()) {
        $userOpts .= "<option value='$i'>$n</option>\n";
    }
    
    /*******************************************
    * Get the bookings for each of the day's 
    * timeslote
    ********************************************/
    $dt = $date->format('Y-m-d');
    $sql = "SELECT t.timeslot_id
            , TIME_FORMAT(start_time, '%H:%i')
            , TIME_FORMAT(end_time, '%H:%i')
            , booking_id
            , u.name
            , comments
            FROM timeslot t
                LEFT JOIN booking b 
                    ON t.timeslot_id = b.timeslot_id
                    AND b.date = '$dt'
                LEFT JOIN user u 
                    ON b.user_id = u.user_id
            ORDER BY t.start_time";
    $res = $db->query($sql);
    while (list($tid, $st, $et, $bid, $nm, $com) = $res->fetch_row()) {
        $cbox = $bid ? "<input type='checkbox' name='cancel' value='$bid'>":'';
        $cls  = $bid ?  '' : "class='we'";
        $user = $bid ? $nm : "<select name='user[$tid]'>$userOpts</select>";
        $dis  = $bid ? 'disabled' : '';
        
        echo "<tr>
            <td $cls>$st</td>
            <td $cls>$et</td>
            <td $cls>$user</td>
            <td $cls><input type='text' name='comment[$tid]' value='$com' size='60' $dis></td>
            <td $cls>$cbox</td>
            </tr>\n";
    }
}


//
// CHECK FOR CHANGE OF DATE
//
$currdate = isset($_GET['date']) ? 
                new DateTime($_GET['date']) : 
                new DateTime();


?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="generator" content="PhpED 14.0 (Build 14039, 64bit)">
<title>Bookings Calendar</title>
<meta name="author" content="Barand">
<meta name="creation-date" content="01/18/2015">
<style type="text/css">
div.month {
    float: left;
}
div.form {
    float: left;
    margin-left: 20px;
    padding-left: 20px;
    border-left: 1px solid gray;
    min-width: 450px;
}
table {
    border-collapse: collapse;
    width: 100%;
}
th,td {
    font-family: sans-serif;
    font-size: 9pt;
    text-align: center;
    width:23px;
    height: 18px;
    border: 1px solid black;
    white-space: nowrap;
}
th {
    background-color: #999;
    color: white;
    padding: 5px;
}
th.we {
    background-color: #3A883A;
    color: white;
}
td.comment {
    background-color: #999;
    text-align: left;
    color: black;
    padding: 5px;
}
td {
    background-color: #C4E5C4;
}
td.day {
    /*width:23px;
    height: 15px;*/
    text-align: center;
    padding: 2px;
    border: 1px solid black;
    cursor: pointer;
}
td.blank {
    background-color: #FFF;
}
td.we {
    background-color: #CCC;
}
td.now {
    border: 4px solid #883A66;
    width: 19px;
    height: 11px;
    padding: 0;
}
td.full {
    background-color: red;
    color: white;
    font-weight: 600;
}
td.partial {
    background-color: yellow;
    color: black;
    font-weight: 600;
}
a {
    font-size: 12pt;
    color: white;
    text-decoration: none;
}
</style>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script type="text/javascript">
    //********************************************
    // Create the on-click function for each
    // clickable cell (class = day)
    // to resubmit the page with clicked date
    //********************************************
    $().ready(function(){
        $(".day").click(function(){
            location.href="?date="+$(this).attr("id");
        })
    })
</script>
</head>
<body>
<h3>Bookings - <?= $currdate->format('F jS, Y')?></h3>
    <div class="month">
    <?php
        makeCalendar($db, $currdate);           // output the calendar
    ?>
    </div>
    <div class="form">
        <form action="cal_update.php" method="post">
        <table border="1">
            <tr>
                <th>Start<br>Time</th>
                <th>End<br>Time</th>
                <th>User</th>
                <th>Comments</th>
                <th>Cancel</th>
            </tr>
            <?php
                bookingForm($db, $currdate);    // output the form
            ?>
        </table><br>
        <input type="submit" name="btnSubmit" value="Update">
        </form>
    </div>
</body>
</html>

 

 

 

Yellow cells are partially booked, red cells fully booked, green have no bookings. Hovering over yellow shows the available slots.

 

Output attached

 

Hey 

 

I thought id run your code, its working except i have a error 

 

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

 

     in on line 38 which is    

 while (list($dt,$tot,$free) = $res->fetch_row()) {

This section

    GROUP BY b.date" ;
    $books = array();
    $res = $db->query($sql);
    while (list($dt,$tot,$free) = $res->fetch_row()) {
        $books[$dt] = array($tot, $free);
    }
    return $books;
}

[Barand]

Let's see if the query failed, and if it did, why

    $books = array();
    $res = $db->query($sql);
    
    if (!$res) die($db->error . "<pre>$sql</pre>");            // ADD THIS LINE HERE
    
    while (list($dt,$tot,$free) = $res->fetch_row()) {
        $books[$dt] = array($tot, $free);
    }
    return $books;

[tom7890]

yes it shows that: 

Unknown column 't.timeslot_id' in 'field list'
SELECT b.date 
            , SUM(IF(t.timeslot_id IS NULL,0,1)) as bookedcount
            , free.freeslots
            FROM booking b
                LEFT JOIN
                (
                SELECT
                 date 
                 , GROUP_CONCAT(DISTINCT TIME_FORMAT(x.start_time, '%H:%i') 
                    ORDER BY start_time SEPARATOR ', ') as freeslots 
                FROM (
                    SELECT DISTINCT b.date
                    , t.timeslot_id
                    , t.start_time
                    FROM booking b
                    CROSS JOIN timeslot t
                    WHERE YEAR (b.date) = 2015 
                        AND MONTH(b.date) = 1
                    ) as x
                    LEFT JOIN booking b USING (date,timeslot_id)
                WHERE b.timeslot_id IS NULL
                GROUP BY date   
                ) as free USING (date)
                LEFT JOIN timeslot t USING (timeslot_id)
                WHERE YEAR (b.date) = 2015 
                    AND MONTH(b.date) = 1
            GROUP BY b.date

[Barand]

What does this output (in PhpMyAdmin for example)?

SHOW CREATE TABLE timeslot;

[Barand]

These are mine:

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `timeslot` (
  `timeslot_id` int(11) NOT NULL AUTO_INCREMENT,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  PRIMARY KEY (`timeslot_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `booking` (
  `booking_id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `timeslot_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `comments` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`booking_id`),
  KEY `IDX_time` (`timeslot_id`),
  KEY `IDX_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[tom7890]

I have set up the tables correctly now, when i select the date i click update and it says

 

object not found: cal_update.php

 

does the form need to be created in a separate file

[Barand]

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

[tom7890]

yes please i would appreciate that, 

 

please could you explain how this works your works also if you dont mind 

[Barand]

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

[tom7890]

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.

 

The aim is to do the following: 

 

There is admin, doctors and patients login system 

 

Patients will book their own appointments online 

 

The patient logs in to the system where they will have a choice of 3 doctors and 1 nurse to book an appointment from 

 

The patients select a doctor or nurse, when the patient clicks on a date a list of available time slots are shown, the patients can select one time slot or two but they have to be consecutive ie. 09:00-09:15 - 09:15-09:30, they will then fill in a little form in regards to what the appointment is for and then confirm booking, 

 

The doctors will have their own calendars where they can view their own calendars to see what booking they have

 

The admin will be able to all doctors and nurse calendars and there bookings

 

I also want to add in a delete, amend and search feature for the bookings. 

[Barand]

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.

[tom7890]

I have created a table for the staff (id, name, staff type).

 

Shall i create another table names staff availability (id, available, not_available)??

 

In the current booking table shall i add (doctor_id, nurse_id)??

[CroNiX]

I think first, you really need to document all of your business rules and come up with a plan to implement them. A diagram of all database tables needed, and of how they relate to each other. Each "page" type and what it needs to do, like "monthly calendar view, daily calendar view, sign up for time slot, doctors calendar view, nurses calendar view, admins calendar view",etc. If you just keep adding part by part without looking at the larger picture, you are probably doing yourself a disservice and will have to constantly be rewriting old parts to make new ones fit. And you might spend a lot of time getting things to work a certain way, but then have to totally redo it because you didn't consider "the next piece". You'll waste a lot of time just rewriting older stuff instead of only new stuff.

 

We don't know your big picture. Only you do. When you ask a specific question, we can give you advice how to solve that specific question with the info given...but it may or may not be totally correct taking into everything you need to do (which we don't know). It might only be good for the generic situation in which it was posed.

[Barand]

 

Shall i create another table names staff availability (id, available, not_available)??

 

In the current booking table shall i add (doctor_id, nurse_id)??

 

 

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

[tom7890]

lol just to let u know i am not getting paid to do anything, im a student

[Barand]

There is admin, doctors and patients login system 

 

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?

[tom7890]

yes, $_SESSION variables. 

 

With the login system i have created i have added the admin details to the database itself and they can login using that information. 

 

The patients will fill out a register form and once data in the database they can use the log in details to log in. 

 

I have not done a separate login for the medical staff yet. 

[CroNiX]

It sounds like your login system needs to have user roles, like "admin", "user", "nurse", "doctor", etc.  One login system for them all (username/password). Once logged in check their user "role" and redirect them to their "home" page depending on what role they are. You'd also check the roles when doing actions, so a "user" can't access an admin page, or perform admin functions, etc.

[Barand]

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

[tom7890]

Barand, if you do not mind will you be able to tell the next step i should take so i dont keep going around in circles.