Jump to content

tom7890

Members
 View Profile  See their activity

  • Posts

    104

  • Joined

  • Last visited

 Content Type 

Everything posted by tom7890

  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.
  16. tom7890
    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";
  17. tom7890
    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 ?
  18. tom7890
    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";
  19. tom7890
    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
  20. tom7890
    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.
  21. tom7890
    I am working on calendar code bit by bit. The first section i have started on is the user menu, so the patient selects which doctor or nurse they would like to have an appointment with. is the following correct? /******************************************* * Create the options for the user menu ********************************************/ $patientOptions = "<option value=''>- select medical staff member -</option>\n"; $sql = "SELECT staff_id, staff_type, lname FROM staff ORDER BY lname"; $res = $db->query($sql); while (list($i, $n) = $res->fetch_row()) { $patientOptions .= "<option value='$i'>$n</option>\n"; }
  22. tom7890
    yes i also have the same ones
  23. tom7890
    okay, that does make sense now. to be on the safe side so i dont delete anything else, is it correct my database is all set up now and i dont need add any more tables to the database? rewriting the calendar and booking code is the next task to complete?
  24. tom7890
    i have deleted the calendar bit, i will need to redo that,
  25. tom7890
    Do i need a users table?
×
×
  • 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.