Jump to content

tom7890

Members
 View Profile  See their activity

  • Posts

    104

  • Joined

  • Last visited

 Content Type 

Posts posted by tom7890

    php sql calendar add events

    in PHP Coding Help

    Posted

    Do you know how to make a <select> element, with <option>'s? You have no <options>. You're creating <select>$lname</select> over and over in a loop....but no options

    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 

    php sql calendar add events

    in PHP Coding Help

    Posted · Edited by 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. 

    php sql calendar add events

    in PHP Coding Help

    Posted

    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.  

    php sql calendar add events

    in PHP Coding Help

    Posted

    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. 

     

     

    php sql calendar add events

    in PHP Coding Help

    Posted · Edited by 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?

    php sql calendar add events

    in PHP Coding Help

    Posted · Edited by 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";

    php sql calendar add events

    in PHP Coding Help

    Posted

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

    php sql calendar add events

    in PHP Coding Help

    Posted · Edited by 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 :)

    php sql calendar add events

    in PHP Coding Help

    Posted · Edited by 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>

    php sql calendar add events

    in PHP Coding Help

    Posted

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

    php sql calendar add events

    in PHP Coding Help

    Posted

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

    php sql calendar add events

    in PHP Coding Help

    Posted · Edited by 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";

    php sql calendar add events

    in PHP Coding Help

    Posted

    The calendar code and booking form will need rewriting to take into account which member of staff the appointment is to be with and when they are available in terms of days and timeslots (from the holiday and schedhours tables)

     

     

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

    php sql calendar add events

    in PHP Coding Help

    Posted

    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?

    php sql calendar add events

    in PHP Coding Help

    Posted

    Barand, 

     

    You know the code which is linked with the calendar, when a date is selected you choose a user select a time slot and do the booking, is this all linked with the current database we are using?

    I have noticed the booking tables it slightly different from the original or maybe i have done it differently, as its not in the new database tables diagrams so im not sure if it is or not. 

     

    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.