Jump to content

tom7890

Members
 View Profile  See their activity

  • Posts

    104

  • Joined

  • Last visited

tom7890's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. tom7890
    yes i do like so <html> <body> <select> <option value="tom">tom</option> <option value="gabi">gabi</option> <option value="php">php</option> </select> </body> </html> But the options in my code need to be retrieved from the database
  2. tom7890
    sorry i forgot the add the error, the error was $staffOpts was not defined
  3. tom7890
    I have taken on board what you suggested. I have been working on the current code and i have used if (!$res) die($db->error . "<pre>$sql</pre>"); to check all the queries are working properly. I have managed to retrieve the time slots from the database but i have an error, The full code is function bookingForm($db, DateTime $date) { $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 LEFT JOIN staff s ON b.staff_id = s.staff_id ORDER BY t.start_time"; $res = $db->query($sql); if (!$res) die($db->error . "<pre>$sql</pre>"); while (list($timeslot_id, $start_time, $end_time, $booking_id, $staff_id) = $res->fetch_row()) { $cbox = $booking_id ? "<input type='checkbox' name='cancel' value='$booking_id'>":''; $cls = $booking_id ? '' : "class='we'"; $staff = $booking_id ? $staff_id : "<select name='staff[$timeslot_id]'>$staffOpts</select>"; $dis = $booking_id ? 'disabled' : ''; echo "<tr> <td $cls>$timeslot_id</td> <td $cls>$start_time</td> <td $cls>$end_time</td> <td $cls>$booking_id</td> <td $cls>$staff_id</td> </tr>\n"; }} The code line in question is: $staff = $booking_id ? $staff_id : "<select name='staff[$timeslot_id]'>$staffOpts</select>"; I have changed it and added in the staff last name as it needs name not id like so, function bookingForm($db, DateTime $date) { $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.lname 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); if (!$res) die($db->error . "<pre>$sql</pre>"); // ADD THIS LINE HERE while (list($timeslot_id, $start_time, $end_time, $booking_id, $staff_id, $lname) = $res->fetch_row()) { $cbox = $booking_id ? "<input type='checkbox' name='cancel' value='$booking_id'>":''; $cls = $booking_id ? '' : "class='we'"; $staff = $booking_id ? $lname: "<select name='lname[$timeslot_id]'>$lname</select>"; $dis = $booking_id ? 'disabled' : ''; echo "<tr> <td $cls>$timeslot_id</td> <td $cls>$start_time</td> <td $cls>$end_time</td> <td $cls>$booking_id</td> <td $cls>$staff_id</td> </tr>\n"; }} The error has disappeared. I thought id update the post as i cannot delete it. Just for testing purposes i added in a staff member in the database but it is not being retrieved.
  4. tom7890
    I was doing it myself but i was going round in circles, barands example was better than what i was doing, so i decided to use that anyways no worries. sorry
  5. tom7890
    I am so stuck
  6. tom7890
    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.
  7. tom7890
    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
  8. tom7890
    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.
  9. tom7890
    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?
  10. tom7890
    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";
  11. tom7890
    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"; }
  12. tom7890
    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
  13. tom7890
    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>
  14. tom7890
    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"; } }
  15. tom7890
    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.
×
×
  • 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.