Jump to content

php sql calendar add events


tom7890

Recommended Posts

  • Replies 131
  • Created
  • Last Reply

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;
}
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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;
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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 :D

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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.

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.