tom7890

I have created a table for the staff (id, name, staff type). Shall i create another table names staff availability (id, available, not_available)?? In the current booking table shall i add (doctor_id, nurse_id)??

The aim is to do the following: There is admin, doctors and patients login system Patients will book their own appointments online The patient logs in to the system where they will have a choice of 3 doctors and 1 nurse to book an appointment from The patients select a doctor or nurse, when the patient clicks on a date a list of available time slots are shown, the patients can select one time slot or two but they have to be consecutive ie. 09:00-09:15 - 09:15-09:30, they will then fill in a little form in regards to what the appointment is for and then confirm booking, The doctors will have their own calendars where they can view their own calendars to see what booking they have The admin will be able to all doctors and nurse calendars and there bookings I also want to add in a delete, amend and search feature for the bookings.

yes please i would appreciate that, please could you explain how this works your works also if you dont mind

I have set up the tables correctly now, when i select the date i click update and it says object not found: cal_update.php does the form need to be created in a separate file

yes it shows that: Unknown column 't.timeslot_id' in 'field list' SELECT b.date , SUM(IF(t.timeslot_id IS NULL,0,1)) as bookedcount , free.freeslots FROM booking b LEFT JOIN ( SELECT date , GROUP_CONCAT(DISTINCT TIME_FORMAT(x.start_time, '%H:%i') ORDER BY start_time SEPARATOR ', ') as freeslots FROM ( SELECT DISTINCT b.date , t.timeslot_id , t.start_time FROM booking b CROSS JOIN timeslot t WHERE YEAR (b.date) = 2015 AND MONTH(b.date) = 1 ) as x LEFT JOIN booking b USING (date,timeslot_id) WHERE b.timeslot_id IS NULL GROUP BY date ) as free USING (date) LEFT JOIN timeslot t USING (timeslot_id) WHERE YEAR (b.date) = 2015 AND MONTH(b.date) = 1 GROUP BY b.date

Hey I thought id run your code, its working except i have a error Fatal error: Call to a member function fetch_row() on a non-object in on line 38 which is while (list($dt,$tot,$free) = $res->fetch_row()) { This section GROUP BY b.date" ; $books = array(); $res = $db->query($sql); while (list($dt,$tot,$free) = $res->fetch_row()) { $books[$dt] = array($tot, $free); } return $books; }

@ginerjm thanks for your comments, will take them on board. @Barand, @Cornix As many changes have been made, i think i am on the right the right with you guys valuable comments, so i just want to say thanks As you know i created a timeslots table, when a user clicked on the date the time slot table appeared; now that i am retrieving the time slots from the database how do i change this? So when the user clicks on a date it shows the time slots from the database on the same page The previous code was: <?php $timestamp = mktime(0,0,0,$cMonth,1,$cYear); $maxday = date("t",$timestamp); $thismonth = getdate ($timestamp); $startday = $thismonth['wday']; $today = getdate(); for ($i=0; $i<($maxday+$startday); $i++) { if(($i % 7) == 0 ){ echo "<tr> "; } if($i < $startday){ echo "<td></td> "; } else{ $jsEvent[] = "document.getElementById('trigger" . $i . "').onclick = function() {showForm()};"; echo "<td align='center' valign='middle' height='20px'><a href='#' id='trigger" . $i . "'>". ($i - $startday + 1) . "</a></td>"; } if(($i % 7) == 6 ){ echo "</tr> "; } } ?>

yes fixed it thanks

To retrieve the data from timeslot table in the database i have done the following but i have an error, i am trying to store the data in to a table. The code is: <?php $db = new mysqli('localhost', 'root', '', 'booking'); $sql = "SELECT timeslots_id, start_time, end_time FROM timeslot"; $db->query($sql); if ($db->num_rows > 0) { echo "<table><tr> <th>Time Slot ID</th> <th>Start Time</th> <th>End Time</th></tr>"; while ($row = $result->fetch_assoc()) { echo "<tr><td>" . $row["timeslots_id"]. "</td><td>" . $row["start_time"]. " " . $row["end_time"]. "</td></tr>"; } echo "</table>"; } else { echo "0 results"; } ?> </body> </html> The error is: Notice: Undefined property: mysqli::$num_rows

barand, the next task for me to do is: Retrieve the time slots from the database and show them on the webpage beside the calendar, am i correct? So when the user clicks on any date the time slots will show up.

yes it does barand. Now that the time slot table is set up, do i need to get rid of the html time slot table i created? is this the best time to set key, available, booked, partially booked time slots for the selected day

I don't understand how i link the dates and the time slots together. On the weekends there will be no time slots as these will be closed. Monday to Friday it is open 9am til 6pm.

PLEASE IGNORE ABOVE POST firstly i think i need to set the key for the available, booked, partially booked time slots for the day selected and then i will work on the condition for the consecutive days. Now that the time slot table is set up, do i need to get rid of the html time slot table i created? The calendar that i have created needs to be in the database, how to add this to the database. my current code for the calendar is as follows: <?php $monthNames = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"); ?> <?php if (!isset($_REQUEST["month"])) $_REQUEST["month"] = date("n"); if (!isset($_REQUEST["year"])) $_REQUEST["year"] = date("Y"); ?> <?php $cMonth = $_REQUEST["month"]; $cYear = $_REQUEST["year"]; $prev_year = $cYear; $next_year = $cYear; $prev_month = $cMonth-1; $next_month = $cMonth+1; if ($prev_month == 0 ) { $prev_month = 12; $prev_year = $cYear - 1; } if ($next_month == 13 ) { $next_month = 1; $next_year = $cYear + 1; } ?> <div align="left"> <table width="400" border="5" align="left" id="calendar"> <tr align="center"> <td bgcolor="#999999" style="color:#FFFFFF"><table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="50%" align="left"><a href="<?php echo $_SERVER["PHP_SELF"] . "?month=". $prev_month . "&year=" . $prev_year; ?>" style="color:#FFFFFF">Previous</a></td> <td width="50%" align="right"><a href="<?php echo $_SERVER["PHP_SELF"] . "?month=". $next_month . "&year=" . $next_year; ?>" style="color:#FFFFFF">Next</a></td> </tr> </table></td> </tr> <tr> <td align="center"><table width="100%" border="2" cellpadding="2" cellspacing="2"> <tr align="center"> <td colspan="7" bgcolor="#999999" style="color:#FFFFFF"><strong> <?php echo $monthNames[$cMonth-1].' '.$cYear; ?></strong></td> </tr> <tr> <td align="center" bgcolor="#999999" style="color:#FFFFFF"><strong>S</strong></td> <td align="center" bgcolor="#999999" style="color:#FFFFFF"><strong>M</strong></td> <td align="center" bgcolor="#999999" style="color:#FFFFFF"><strong>T</strong></td> <td align="center" bgcolor="#999999" style="color:#FFFFFF"><strong>W</strong></td> <td align="center" bgcolor="#999999" style="color:#FFFFFF"><strong>T</strong></td> <td align="center" bgcolor="#999999" style="color:#FFFFFF"><strong>F</strong></td> <td align="center" bgcolor="#999999" style="color:#FFFFFF"><strong>S</strong></td> </tr> <?php $timestamp = mktime(0,0,0,$cMonth,1,$cYear); $maxday = date("t",$timestamp); $thismonth = getdate ($timestamp); $startday = $thismonth['wday']; $today = getdate(); for ($i=0; $i<($maxday+$startday); $i++) { if(($i % 7) == 0 ){ echo "<tr> "; } if($i < $startday){ echo "<td></td> "; } else{ $jsEvent[] = "document.getElementById('trigger" . $i . "').onclick = function() {showForm()};"; echo "<td align='center' valign='middle' height='20px'><a href='#' id='trigger" . $i . "'>". ($i - $startday + 1) . "</a></td>"; } if(($i % 7) == 6 ){ echo "</tr> "; } } ?> <script type="text/javascript"> <?php foreach($jsEvent as $event){ echo $event; } ?> function showForm(){ document.getElementById('timeslots').style.display="block"; }; </script> </table></td> </tr> </table>

firstly i think i need to set the key for the available, booked, partially booked time slots for the day selected and then i will work on the condition for the consecutive days. Now that the time slot table is set up, do i need to get rid of the html time slot table i created? The calendar that i have created does that need to be in the database?

yes i would need consecutive time slots as this will make sense, so i will need a condition; if a user selects two slots which are not consecutive there will be an error as they can only select two like so: 09:00-09:15 09:15-09:30

That is a very good suggestion thank you, i did not think about that. The users can only select 2 timeslots after that they cant book any for that day. Would this be better? it is for a doctors center

okay well i have changed it now and it is working thanks barand

ive been using mysql connection not mysqli thats why

i did but it still didnt work, my full code is <?php $username = "root"; $password = ""; $hostname = "localhost"; $dbhandle = mysql_connect($hostname, $username, $password) or die ("no connection to database"); $selected = mysql_select_db("booking", $dbhandle); $sql = "TRUNCATE TABLE timeslots"; $dt1 = new DateTime("09:00:00"); $dt2 = new DateTime("09:15:00"); $di = new DateInterval('PT15M'); $dp = new DatePeriod($dt1, $di, new DateTime('18:00:00')); $query = "INSERT INTO timeslots (start_time, end_time) VALUES (?,?)"; $result = mysql_query($query); $query->bind_param('ss', $start_time,$end_time); foreach ($dp as $d) { $st = $d->format('H:i'); $et = $dt2->format('H:i'); $dt2->add($di); $query->execute(); } ?>

error Call to a member function bind_param() on a non-object what does this mean? my code is like so: $selected = mysql_select_db("booking", $dbhandle); $sql = "TRUNCATE TABLE timeslots"; $dt1 = new DateTime("09:00:00"); $dt2 = new DateTime("09:15:00"); $di = new DateInterval('PT15M'); $dp = new DatePeriod($dt1, $di, new DateTime('18:00:00')); $query = "INSERT INTO timeslots (start_time, end_time) VALUES (?,?)"; $result = mysql_query($query); $query->bind_param('ss', $st,$et); foreach ($dp as $d) { $st = $d->format('H:i'); $et = $dt2->format('H:i'); $dt2->add($di); $query->execute(); }

i get this error Undefined variable: db

ignore the above post please. Can you please tell me when i enter the data in the database for the timeslots table, in the timeslots_id column do i enter a number or is that meant to be auto incremented?

I have now updated my tables. In the database do i need to enter all the start and end times? Then do i take out all the times in the html table? Will the headings in the html table be: timeslots_id, start_time, end_time

Should he timeslots table in sql have the following headings: Date(Unique Key) start_time end_time do i need a column for the check boxes? Do i need to create a table in html like i have done so above shown in previous posts with the headings: Date(Unique Key) start_time end_time but not add any data in to the table?

Barand, i need to get somthing clear. Please can you be patient so i can understand, i know you are trying to help me but it takes time for me to understand, Your patience will be much appreciated. I have created a table in html the code is posted above where i have added all the timings for the timeslots myself and in the database i have added a table with the same headings but not entered any data. Is this incorrect?