tom7890 Posted February 3, 2015 Author Share Posted February 3, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted February 3, 2015 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 3, 2015 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 3, 2015 Author Share Posted February 3, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted February 4, 2015 Share Posted February 4, 2015 No, it won't even execute. Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 4, 2015 Author Share Posted 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 ? Quote Link to comment Share on other sites More sharing options...
Barand Posted February 4, 2015 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 4, 2015 Author Share Posted 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"; Quote Link to comment Share on other sites More sharing options...
Barand Posted February 4, 2015 Share Posted February 4, 2015 You need $dt = $d->format('Y-m-d'); at the beginning of the function Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 4, 2015 Author Share Posted February 4, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 4, 2015 Author Share Posted 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"; } } Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 4, 2015 Author Share Posted February 4, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted February 4, 2015 Share Posted February 4, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 4, 2015 Author Share Posted February 4, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 4, 2015 Author Share Posted 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"; } Quote Link to comment Share on other sites More sharing options...
Barand Posted February 4, 2015 Share Posted February 4, 2015 I don't see anything in there that would restrict selection to only the chosen member of staff Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 4, 2015 Author Share Posted February 4, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 4, 2015 Author Share Posted February 4, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted February 4, 2015 Share Posted February 4, 2015 Install MySQL Workbench or PHPMyAdmin so you can test the queries. Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 4, 2015 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted February 4, 2015 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 4, 2015 Author Share Posted February 4, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 5, 2015 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
tom7890 Posted February 5, 2015 Author Share Posted February 5, 2015 I am so stuck Quote Link to comment Share on other sites More sharing options...
CroNiX Posted February 5, 2015 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.