Jump to content

php sql calendar add events


tom7890

Recommended Posts

  • Replies 131
  • Created
  • Last Reply

Top Posters In This Topic

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

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

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

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

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

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

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

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

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

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

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. 

 

 

Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • 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.