php sql calendar add events

[Barand]

I would do as Cronix suggested. Look at all the outputs that you need to produce, as that will dictate what data you need to store. You then need to organize that data into correctly normalized tables. Look at the processes you need to produce those outputs and ensure that your data model will support those processes - by using the relational links in your model can you access the data required for the process to produce working, efficient queries? Remember that as soon as you decide you need a table of staff availabilty, say, then you need a process to maintain that table. Consider what could go wrong. Throw a few "what ifs" at the model.

 

For example, if a doctor goes sick suddenly can the appointments be reallocated easily and patients/staff notified? What would happen if they decided to open on Saturday mornings, would they have to hold off that decision while someone took a few weeks to rewrite the system?

 

When you are happy, start coding. In other words, before you set off on a journey, have a map.

[tom7890]

I have listened to your suggestions and have come up with a whole map of what the system needs to do, i am a learning and  this is just a overview and not a proper language modified diagram. 
If i have missed anything i would appreciate if you could let me know or if i have any error, i need to finish this work asap, ive been doing bits by bits but ive just been going round in circles, trying to one thing and then add another etc.. I totally agree with both cornix and barand, it makes more sense to have it laid out and i appreciate your advice and help. 
 
Please see the attached map below

 

 

Edited January 24, 2015 by tom7890

[Barand]

You should store an item of in formation, like names, in one place only. The only things that should be duplicated are id keys.

 

I have added a duty_roster table which would hold

• staff_id
• date
• day start time
• day end time

to assist in generating the table there is also a holiday table (to show non-available) and, for each staff, a sched_hours table (their normal working week pattern day of week, day start time, day end time).

 

If you limit bookings to, say, a month in advance then the roster table will need to be generated each month in advance. You would also need a facility to edit this table for changes at short notice (which could generate cancellations so the patients will need to be notified)

 

The roster table would define which days, staff and timeslots are available when making bookings.

 

I added a "medical" flag to stafftype table to show which staff would see patients.

An admin flag on the staff file shows who has admin access.

tom7890.html

[tom7890]

You should store an item of in formation, like names, in one place only. The only things that should be duplicated are id keys.

 

I have added a duty_roster table which would hold

• staff_id
• date
• day start time
• day end time

to assist in generating the table there is also a holiday table (to show non-available) and, for each staff, a sched_hours table (their normal working week pattern day of week, day start time, day end time).

 

If you limit bookings to, say, a month in advance then the roster table will need to be generated each month in advance. You would also need a facility to edit this table for changes at short notice (which could generate cancellations so the patients will need to be notified)

 

The roster table would define which days, staff and timeslots are available when making bookings.

 

I added a "medical" flag to stafftype table to show which staff would see patients.

An admin flag on the staff file shows who has admin access.

 

i cant get the html file to open. its not working at all. 

[Barand]

It was created in Excel and exported as html. Now I looked at the file it's apparent M$ don't know what a .html file looks like.

 

Here's a screenshot of the content.

 

[tom7890]

thank you  

 

i have set up all the tables correctly, now i can start programming without going round in circles. 

 

what would be the first step to take considering what we all ready have.

[Barand]

I'd start by creating the code to maintain (add, edit, delete) the tables then you can use those to create the test data to use when coding and testing the main processes (booking and reporting)

[tom7890]

what tables would i need the add edit and delete for?

[Barand]

All master files ie all except booking and patient_note.

[tom7890]

is this correct for add??

 

i have been been using mysql not sqli so want sure

<?php

$db = new mysqli('localhost','root','','booking');

$db->query($sql);

if(isset($_POST['submit']))
{
$rosterid=mysql_real_escape_string($_POST['rosterid']);
$staffid=mysql_real_escape_string($_POST['staffid']);
$date=mysql_real_escape_string($_POST['date']);
$starttime=mysql_real_escape_string($_POST['starttime']);
$endtime=mysql_real_escape_string($_POST['endtime']);


$sql = "INSERT INTO dutyroster(rosterid, staffid, date, starttime, endtime) VALUES (?,?,?,?,?)";

$smt = $db->prepare($sql);
$smt->bind_param('sssss', $rosterid, $staffid, 
$date, $starttime, $endtime);

}
?>

Edited January 26, 2015 by tom7890

[CroNiX]

No. You do NOT want to use mysqli_real_escape_string() WITH bound query parameters as they automatically get escaped in the binding. By manually doing it, it's being escaped 2x which can add unwanted things to your db.

 

Also what's this doing?

$db->query($sql);

 

You don't have $sql at the point when you perform that query. Doesn't look like you want that line at all.

[CroNiX]

Also you are telling your bind that all the data fields are strings ('sssss'):

$smt->bind_param('sssss', $rosterid, $staffid,$date, $starttime, $endtime);

Most likely rosterid and staffid are probably integers.

[tom7890]

<?php

$db = new mysqli('localhost','root','','booking');


$stmt = $mysqli->prepare("INSERT INTO roster(rosterid, staffid, date, starttime, endtime) VALUES (?,?,?,?,?)");

$stmt->bind_param('iisss', $rosterid, $staffid, 
$date, $starttime, $endtime);
$stmt->execute();
$stmt->close();

?>

Now is this correct? 

Edited January 26, 2015 by tom7890

[CroNiX]

No, you removed 2 of the s's, but didn't replace them. Now it thinks there are only 3 parameters but your sending 5. Should probably be 'iisss' (first 2 fields will be integers, last 3 will be strings)

[tom7890]

No, you removed 2 of the s's, but didn't replace them. Now it thinks there are only 3 parameters but your sending 5. Should probably be 'iisss' (first 2 fields will be integers, last 3 will be strings)

ive updated the above  post  

[tom7890]

<?php
$db = new mysqli('localhost','root','','booking');

$sql = "INSERT INTO roster('rosterid', 'staffid', 'date', 'starttime', 'endtime') VALUES (?,?,?,?,?)";

$stmt = $db->prepare($sql);

$stmt->bind_param("iisss", $rosterid, $staffid, 
$date, $starttime, $endtime);
$stmt->execute();
$stmt->close();
?>

<?php


$sql = "UPDATE roster(rosterid, staffid, date, starttime, endtime) VALUES (?,?,?,?,?)";

$stmt = $db->prepare($sql);

$stmt->bind_param('iisss', '$rosterid', '$staffid', 
'$date', '$starttime', '$endtime');
$stmt->execute();
$stmt->close();
?>

<?php
$sql = "DELETE  FROM roster(rosterid, staffid, date, starttime, endtime) VALUES (?,?,?,?,?)";

$stmt = $db->prepare($sql);

$stmt->bind_param('iisss', '$rosterid', '$staffid', 
'$date', '$starttime', '$endtime');
$stmt->execute();
$stmt->close();
?>

i have updated the code, is this correct? 

 

 

Edited January 26, 2015 by tom7890

[CroNiX]

I really don't understand what you are doing.

 

You are doing an insert, followed by an update, followed by a delete statement. Whats the purpose?

 

You are inserting/updating data, which doesn't seem to exist. Where are your $rosterid, $staffid, $date, $starttime, $endtime variables coming from that you are inserting/updating?

 

Also, when updating or deleting, you usually have a WHERE clause, or else it might update/delete ALL rows in your table.

 

DELETE from table WHERE field = some_value;

 

UPDATE table SET field1 = value1, field2 = value2 WHERE field = some_value;

 

Where I am saying "field = some_value"..."field" is usually an ID field, and "some_value" is the actual ID

Edited January 26, 2015 by CroNiX

[tom7890]

Barand said: 

create the code to maintain (add, edit, delete) the tables then you can use those to create the test data to use when coding and testing the main processes (booking and reporting)

 

i am doing this wrong?

[CroNiX]

Yes, see my comment about the WHERE clause. Also about the missing variables.

[CroNiX]

It would probably be helpful for you to create 3 individual files, create.php, edit.php and delete.php and put the relevant code in each.

Each file would contain the code necessary to perform the indicated function, including displaying the form, submitting the form, processing the form data, and the appropriate database action. You need to be able to actually test this stuff and if all code is in a single file, you can't easily do that, at least the way you are doing it.

[Barand]

And you are trying to use the same statement syntax for update and delete that you used for insert. They all use different statements syntaxes.

 

http://dev.mysql.com/doc/refman/5.6/en/insert.html

http://dev.mysql.com/doc/refman/5.6/en/update.html

http://dev.mysql.com/doc/refman/5.6/en/delete.html

[tom7890]

And you are trying to use the same statement syntax for update and delete that you used for insert. They all use different statements syntaxes.

 

http://dev.mysql.com/doc/refman/5.6/en/insert.html

http://dev.mysql.com/doc/refman/5.6/en/update.html

http://dev.mysql.com/doc/refman/5.6/en/delete.html

 

i know they are seperate syntaxe statements, ive just got into a pickle because of using sqli. :s

Edited January 26, 2015 by tom7890

[tom7890]

create the code to maintain (add, edit, delete) the tables then you can use those to create the test data to use when coding and testing the main processes (booking and reporting)

 

Barand what did u mean? im a little confused as what will i be editing or deleting?

 

i know its the appointments but im confused

Edited January 26, 2015 by tom7890

[CroNiX]

What are you going to do if someone needs to change their appointment? Edit.

What are you going to do if someone cancels an appointment? Delete.

[tom7890]

What are you going to do if someone needs to change their appointment? Edit.

What are you going to do if someone cancels an appointment? Delete.

yea i understand that, but in the code when u write 

 

delete from where ?? 

 

?? is this the id? 

 

i mean would it be like so 

 

$sql = "DELETE FROM (roster_id, staff_id, date, start_time, end_time) WHERE id=1";

 

what if the roster_id is 4? can i leave the 1 blank like so id=?;

Edited January 26, 2015 by tom7890