php sql calendar add events

tom7890 · February 3, 2015

what would be next best bit to rewrite so it does not get confusing, i thought if i break it up it will be better but im gettin pickled.

Edited February 3, 2015 by tom7890

Barand · February 3, 2015

I'd do the calendar to show, for the selected staff member,

which days they are available,
which days are part-booked and
which are fully booked

then do the booking form for the day selected

You need to query the booking, staff_sched and holiday tables

tom7890 · February 3, 2015

which days they are available,

Do we use the current getBookings function and add just add to the query to select staff_id from staff table

tom7890 · February 3, 2015

is this correct?

getting booking for each stay according to staff


  
    $dt = $date->format('Y-m-d');
    $sql = "SELECT s.staff_id,
				   s.staff_name,
	          FROM s.staff,
	      SELECT t.timeslot_id
            , TIME_FORMAT(start_time, '%H:%i')
            , TIME_FORMAT(end_time, '%H:%i')
            , booking_id
            , patient_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.patent_id = patient_id
            ORDER BY t.start_time";

Edited February 3, 2015 by tom7890

Barand · February 4, 2015

No, it won't even execute.

tom7890 · February 4, 2015

I cant seem to get the query working, im doing something wrong

Been trying all day but not getting any where.

Is there anything you can suggest that may help me to do this ?

Barand · February 4, 2015

Your syntax is all over the place.

You have "SELECT .... FROM ... SELECT ... FROM ...". There should be only one SELECT and one FROM clause unless you have subqueries.

You are referencing table alias "s" when there is no table in the FROM clause with that alias. You need to include the staff table as well as the patient table.

You have "patent_id" instead of "patient_id".

If you are using the same data as me then

SELECT 
    s.staff_id,
    s.lname,
    s.fname,
    t.timeslot_id,
    TIME_FORMAT(start_time, '%H:%i'),
    TIME_FORMAT(end_time, '%H:%i'),
    booking_id,
    p.lname,
    illness
FROM
    timeslot t
        LEFT JOIN
    booking b ON t.timeslot_id = b.timeslot_id AND b.date = '$dt'
        LEFT JOIN
    patient p ON b.patient_id = p.patient_id
        LEFT JOIN
    staff s ON b.staff_id = s.staff_id
ORDER BY t.start_time

Note that you will require two versions of the booking form.

If a patient is using the form then you would not show the patients' names and illness comments, just show which slots are booked or not and give an option to book a slot or cancel their own slot/s.
If staff are viewing then they would need to see everything and be able to book/cancel for any patient

tom7890 · February 4, 2015

i keep getting the error

Notice: Undefined variable: dt


function getBookings($db, DateTime $d)
{
    $currY = $d->format('Y');
    $currM = $d->format('n');
	
    $sql = " SELECT s.staff_id,
    s.lname,
    s.fname,
    t.timeslot_id,
    TIME_FORMAT(start_time, '%H:%i'),
    TIME_FORMAT(end_time, '%H:%i'),
    booking_id,
    p.lname,
    illness
	FROM
    timeslot t
        LEFT JOIN
    booking b ON t.timeslot_id = b.timeslot_id AND 
	b.date = '$dt'
        LEFT JOIN
    patient p ON b.patient_id = p.patient_id
        LEFT JOIN
    staff s ON b.staff_id = s.staff_id
ORDER BY t.start_time";

Barand · February 4, 2015

You need

$dt = $d->format('Y-m-d');

at the beginning of the function

tom7890 · February 4, 2015

I deleted a file earlier, where i was able to retrieve all the timeslots from the database. I will fix that and then move on the next query.

Edited February 4, 2015 by tom7890

tom7890 · February 4, 2015

if am correct is this the query which needs to rewritten so we can get the available time slots from the database?

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

tom7890 · February 4, 2015

barand,

this is my full current code for the calendar and bookings, (most of which is taken from your example)

am i on the right track?

<?php

$db = new mysqli('localhost','root','','booking');


function getBookings($db, DateTime $d)
{
	$dt = $d->format('Y-m-d');

    $currY = $d->format('Y');
    $currM = $d->format('n');
	
    $sql = " SELECT s.staff_id,
    s.lname,
    s.fname,
    t.timeslot_id,
    TIME_FORMAT(start_time, '%H:%i'),
    TIME_FORMAT(end_time, '%H:%i'),
    booking_id,
    p.lname,
    illness
	FROM
    timeslot t
        LEFT JOIN
    booking b ON t.timeslot_id = b.timeslot_id AND 
	b.date = '$dt'
        LEFT JOIN
    patient p ON b.patient_id = p.patient_id
        LEFT JOIN
    staff s ON b.staff_id = s.staff_id
ORDER BY t.start_time";
			
}


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

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

$sql = "INSERT INTO timeslot(start_time, end_time) VALUES (?,?)";
$smt = $db->prepare($sql);
$smt->bind_param('ss', $st,$et);

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

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

    /*******************************************
    * 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
            , p.lname
            , comments
            FROM timeslot t
                LEFT JOIN booking b 
                    ON t.timeslot_id = b.timeslot_id
                    AND b.date = '$dt'
                  patient p ON b.patient_id = p.patient_id
        LEFT JOIN
    staff s ON b.staff_id = s.staff_id
ORDER BY t.start_time";
    }


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


?>
<html>

<title>Bookings Calendar</title>

<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">
          Appointment with:
        <select name="sid" id="sid">
            <?=staffOpts($db, $staffID)?>
        </select><br><br> 
        
        <table border="1">
            <tr>
                <th>Start<br>Time</th>
                <th>End<br>Time</th>
                <th>User</th>
                <th>Comments</th>
                <th>Cancel</th>
            </tr>
           
        </table><br>
        <input type="submit" name="btnSubmit" value="Update">
        </form>
    </div>
</body>
</html>

Edited February 4, 2015 by tom7890

Barand · February 4, 2015

What is this doing in there?

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

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

$sql = "INSERT INTO timeslot(start_time, end_time) VALUES (?,?)";
$smt = $db->prepare($sql);
$smt->bind_param('ss', $st,$et);

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

It generates the timeslot master table which is something that needs doing once (or if you decide to change the slots). Do you also intend to key in all the staff data every time you want to produce a calendar ::) .

IBM have a good motto - THINK!

[edit] PS When you get current bookings you are now only interested in those bookings for the staff member with whom they want the appointment

Edited February 4, 2015 by Barand

tom7890 · February 4, 2015

no your right, i didn't think of it like that. ill take that out as it is not needed

does the rest look like okay?

i need to retrieve the available timeslots from the database.

bdw when i got your code working i really like the hover over day day feature which shows the available times, at first i didnt understand what you meant but i think that is a good idea

Edited February 4, 2015 by tom7890

tom7890 · February 4, 2015

barand

is this correct for getting the bookings for each staff member they want a appointment with?

 /*******************************************
    * 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
            , s.staff_id
            FROM timeslot t
                LEFT JOIN booking b 
                    ON t.timeslot_id = b.timeslot_id
                    AND b.date = '$dt'
        LEFT JOIN
    staff s ON b.staff_id = s.staff_id
ORDER BY t.start_time";
    }

Barand · February 4, 2015

I don't see anything in there that would restrict selection to only the chosen member of staff

tom7890 · February 4, 2015

would i need to add staff type id like so?

   $sql = "SELECT t.timeslot_id
	
            , TIME_FORMAT(start_time, '%H:%i')
            , TIME_FORMAT(end_time, '%H:%i')
            , booking_id
            , s.staff_id
			, s.staff_type_id
            FROM timeslot t
                LEFT JOIN booking b 
                    ON t.timeslot_id = b.timeslot_id
      INNER JOIN staff_type t ON s.staff_type = t.staff_type_id
          LEFT JOIN staff s 
			ON b.staff_id = s.staff_id
			ORDER BY t.start_time";

Edited February 4, 2015 by tom7890

tom7890 · February 4, 2015

Barand,

I am feeling really frustrated because i cant test these queries to see what is happening nor am i getting an error or anything.

My calendar and booking code is incomplete

i have not even been able to retrieve the timeslots yet

what can you suggest to help me?

Edited February 4, 2015 by tom7890

Barand · February 4, 2015

Install MySQL Workbench or PHPMyAdmin so you can test the queries.

tom7890 · February 4, 2015

this is what i am working with,

atm i am trying to get the time slots what i am working with is the following..

am i missing something?

$sql = "SELECT t.timeslot_id
	
            , TIME_FORMAT(start_time, '%H:%i')
            , TIME_FORMAT(end_time, '%H:%i')
            , booking_id
            , s.staff_id
			, s.staff_type_id
            FROM timeslot t
                LEFT JOIN booking b 
                    ON t.timeslot_id = b.timeslot_id
      INNER JOIN staff_type t ON s.staff_type = t.staff_type_id
          LEFT JOIN staff s 
			ON b.staff_id = s.staff_id
			ORDER BY t.start_time";

you mentioned before that

"I don't see anything in there that would restrict selection to only the chosen member of staff"

i have added staff_type_id.

Barand · February 4, 2015

And what has staff_type got to do with which member of staff they want to see? It's the staff_id that will identify a staff member.

Your programming style like someone playing "Pin the tail on the donkey" - copy a line of code, close your eyes, paste it and hope for the best.

tom7890 · February 4, 2015

no i am trying to understand, i oready have staff_id in there maybe i misunderstood what you said.

you seem to be getting annoyed.

sorry

Edited February 4, 2015 by tom7890

tom7890 · February 5, 2015

To get the timeslots i have the following

 $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
            , s.staff_id
			, s.staff_name
            FROM timeslot t
                LEFT JOIN booking b 
                    ON t.timeslot_id = b.timeslot_id
          LEFT JOIN staff s 
			ON b.staff_id = s.staff_id
			ORDER BY t.start_time";    
		 $res = $db->query($sql);
    while (list($tid, $st, $et, $bid, $si, $sn) = $res->fetch_row()) {
        $checkbox = $bid ? "<input type='checkbox' name='cancel' value='$bid'>":'';
       
	    $staff = $bid ? $staff_id : "<select name='staff[$tid]'>$userOpts</select>";
        $dis  = $bid ? 'disabled' : '';
        echo "<tr>
            <td>$tid</td>
            <td>$st</td>
            <td>$et</td>
            <td>$bid</td>
            <td >$si</td>
            </tr>\n";
    }

To get the bookings i have the following



function getBookings($db, DateTime $d)
{
	$dt = $d->format('Y-m-d');

    $currY = $d->format('Y');
    $currM = $d->format('n');
	
    $sql = " SELECT s.staff_id,
    s.lname,
    s.fname,
    t.timeslot_id,
    TIME_FORMAT(start_time, '%H:%i'),
    TIME_FORMAT(end_time, '%H:%i'),
    booking_id,
    p.lname,
    illness
	FROM
    timeslot t
        LEFT JOIN
    booking b ON t.timeslot_id = b.timeslot_id AND 
	b.date = '$dt'
        LEFT JOIN
    patient p ON b.patient_id = p.patient_id
        LEFT JOIN
    staff s ON b.staff_id = s.staff_id
ORDER BY t.start_time";
 $books = array();
    $res = $db->query($sql);
    while (list($dt,$pln,$ill,$pi,$si,$st,$et) = $res->fetch_row()) {
        $books[$dt] = array($dt,$pln,$ill,$pi,$si,$st,$et);
    }
    return $books;

}

There is something not right and i cannot figure it out, as these queries do not give any results.

Barand thank you for the help u have u have given me, i really appreciate ur patience.

hope you can still me and i have not annoyed you too much.

tom7890 · February 5, 2015

I am so stuck

CroNiX · February 5, 2015

I'm sorry to be blunt and at the risk of offending you, the problem is this project is way over your head right now and you don't seem to want to accept that. You don't understand what the code is doing. You're just copy/pasting code that Barand has been more than generous in writing FOR YOU, but then you just post back that it doesn't work when you try and expect him to come to the rescue. Again, that's because you don't understand the underlying code. Most of these problems you should be able to sort out yourself, or take the code Barand has supplied and alter it to your needs. This is a fairly complex project for someone just starting out. Most wouldn't be able to do it with your level of PHP knowledge. I'm not trying to put you down. I just don't think your coding skills are to a level where you could do this fairly complex project. What would you do if Barand wasn't here to write it for you? I haven't seen much of your own code in here...just what Barand is writing.

Sign In

php sql calendar add events

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Posted Images

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information