January 12th, 2013, 09:14 AM
-
Scheduling help!
hello everyone! i am currently working on scheduling application. I have an input of starttime and endtime. can anyone tell me what are the conditions to check if my inputs are conflict with schedules on my database. thanks in advance!
January 12th, 2013, 09:40 AM
-
You will have to be more specific then that. How do you define in conflict? What is your data structure? etc.... please be as specific as possible and provide code when available.
Originally Posted by bads
hello everyone! i am currently working on scheduling application. I have an input of starttime and endtime. can anyone tell me what are the conditions to check if my inputs are conflict with schedules on my database. thanks in advance!
January 12th, 2013, 06:42 PM
-
I don't know if this works because I have not tried it but it's a start.
PHP Code:
$newStartTime = $_POST['newStartTime'];
$newEndTime = $_POST['newEndTime'];
$query = "SELECT COUNT(*) FROM <tablename> WHERE '$newStartTime' BETWEEN originalStartTime AND originalEndTime";
$result = mysql_query ($query);
if ($result > 0) {
echo "This time slot is taken";
} else {
echo "Ok, good to go, etc. etc.";
}
-- Success achieved from tribulation --
January 12th, 2013, 11:19 PM
-
So are you just allowing users to select an appointment slot or would you like them to enter begin time and end time?
Originally Posted by BitZoid
I don't know if this works because I have not tried it but it's a start.
PHP Code:
$newStartTime = $_POST['newStartTime'];
$newEndTime = $_POST['newEndTime'];
$query = "SELECT COUNT(*) FROM <tablename> WHERE '$newStartTime' BETWEEN originalStartTime AND originalEndTime";
$result = mysql_query ($query);
if ($result > 0) {
echo "This time slot is taken";
} else {
echo "Ok, good to go, etc. etc.";
}
January 12th, 2013, 11:47 PM
-
If you define "conflict" to be "the time spans overlap" then check for anything scheduled where its start time is before the new end time and where its end time is after the new start time.
For the explanation see this thread, which is the same problem in a slightly different circumstance.
January 14th, 2013, 08:28 AM
-
hello! here is my code.
here is the scenario. I have scheduled 8:00 AM - 9:30 AM and 1:30 PM - 3:00 PM in my database. now the problem is when I choose 10:00 AM - 5:00 PM there is conflict because there is this 1:30 PM - 3:00 PM scheduled. but when I choose 9:30 AM -5:00 AM. this returns ok. What do you think I've missed?
PHP Code:
if(($starttime == $dbstarttime and $endtime == $dbendtime) //equal
or ($starttime >= $dbstarttime and $starttime < $dbendtime) //start time is in between existing schedules
or ($endtime >=$dbstarttime and $endtime <= $dbendtime) //end time is in between existing schedules
or ($dbstarttime > $starttime and $dbendtime < $endtime and $dbendtime == $starttime)){ // existing schedules is in between input schedules
echo "conflict";
}else{
echo "ok";
}
January 14th, 2013, 09:41 AM
-
Originally Posted by bads
What do you think I've missed?
The reply directly above your post. This is the solution.
Your conditions are much too complicated and redundant, and the last one doesn't even make sense to me. I'm also not sure what you're doing there. Are you reading every single row and comparing them via PHP? That's enormously inefficient and basically counteracts the purpose of a database system. You do know that you can have a WHERE clause to filter the rows?
And why are you using "and" and "or" instead of "&&" and "||"? The latter are the standard operators for logical expressions, while the former have a lower precendence. Only use them if you know what you're doing.
January 14th, 2013, 11:49 AM
-
Originally Posted by Jacques1
The reply directly above your post. This is the solution.
Your conditions are much too complicated and redundant, and the last one doesn't even make sense to me. I'm also not sure what you're doing there. Are you reading every single row and comparing them via PHP? That's enormously inefficient and basically counteracts the purpose of a database system. You do know that you can have a WHERE clause to filter the rows?
And why are you using "and" and "or" instead of "&&" and "||"? The latter are the standard operators for logical expressions, while the former have a lower precendence. Only use them if you know what you're doing.
thank you for your reply.
what about this code below?
and about the last one. how can you check if the schedules in the database is inside the input schedule. cause thats a conflict obviously. thanks
PHP Code:
$query1 = $this->db->query("SELECT * FROM schedules WHERE
('$starttime' = starttime AND '$endtime' = endtime)
or ('$starttime' >= starttime AND '$starttime' < endtime)
or ('$endtime' > starttime AND '$endtime' <= endtime)
or (starttime > '$starttime' AND endtime < '$endtime')");
January 14th, 2013, 12:00 PM
-
Look in the top-left corner of this post. You see my avatar? It's a zombie. Now scroll up a bit until you see it again. That'll be the other post I made earlier. Now read the post.
Comments on this post
January 14th, 2013, 02:39 PM
-
What are you doing? there should be 2 conditions set.
pseudo
Code:
WHERE (StartTime < '$newstart' AND EndTime >'$newstart') OR (StartTime < '$newend' AND EndTime >'$newend')
This will check if either newstart or newend is between any start and end times in your table. Then you can just check if there are any returned rows. If returned rows is greater than 0 then you have a conflict
Comments on this post
January 14th, 2013, 03:56 PM
-
Originally Posted by portcitysoftwar
Code:
WHERE (StartTime < '$newstart' AND EndTime >'$newstart') OR (StartTime < '$newend' AND EndTime >'$newend')
Close but doesn't account for if the existing schedule runs entirely within the span of the new schedule, ie, StartTime between $newstart and $newend and EndTime between $newstart and $newend.
Take a look at r937's analysis of the different possibilities in that thread I mentioned. He shows the six cases and how to arrive at the short-n-sweet logic.
January 14th, 2013, 04:50 PM
-
Um, I just realized that r937's query and diagram are wrong, because they don't allow schedules to directly follow each other.
The ">=" and "<=" should be ">" and "<" like you said in your text:
Code:
WHERE
date_end > $data_start
AND date_start < $date_end
And here's an updated diagram:
Comments on this post
Last edited by Jacques1; January 14th, 2013 at 05:00 PM.
January 14th, 2013, 05:33 PM
-
Actually the diagram and SQL is valid but it uses a slightly different definition of "overlap": the ranges overlap even if they connect at the endpoints. Like 9:00-10:00 overlaps with 10:00-11:00. That may be true for some cases but I think for most that's not what's intended.
Code:
$month_start $month_end
| |
1a date_start---date_end | |
| |
1b date_start------date_end |
| |
2 date_start--|--date_end |
| |
3a | date_start---date_end |
| |
3b date_start------date_end |
| |
3c | date_start------date_end
| |
3d date_start---------date_end
| |
4a date_start--|-------------------------|--date_end
| |
4b date_start----------------|--date_end
| |
4c date_start--|------------------date_end
| |
5 | date_start--|--date_end
| |
6a | | date_start---date_end
| |
6b | date_start------date_end
So yes, > and <.
January 15th, 2013, 10:38 AM
-
Originally Posted by requinix
Actually the diagram and SQL is valid but it uses a slightly different definition of "overlap": the ranges overlap even if they connect at the endpoints. Like 9:00-10:00 overlaps with 10:00-11:00. That may be true for some cases but I think for most that's not what's intended.
Code:
$month_start $month_end
| |
1a date_start---date_end | |
| |
1b date_start------date_end |
| |
2 date_start--|--date_end |
| |
3a | date_start---date_end |
| |
3b date_start------date_end |
| |
3c | date_start------date_end
| |
3d date_start---------date_end
| |
4a date_start--|-------------------------|--date_end
| |
4b date_start----------------|--date_end
| |
4c date_start--|------------------date_end
| |
5 | date_start--|--date_end
| |
6a | | date_start---date_end
| |
6b | date_start------date_end
So yes, > and <.
hello! thanks that works! but still. this one
here is the scenario. I have scheduled 8:00 AM - 9:30 AM and 1:30 PM - 3:00 PM in my database. now the problem is when I choose 10:00 AM - 5:00 PM there is conflict because there is this 1:30 PM - 3:00 PM scheduled. but when I choose 9:30 AM -5:00 AM. this returns ok.
that 9:30 AM pisses me off.
January 15th, 2013, 10:40 AM
-
I believe there should be a condition for if it starts before an event AND ends after the event
Originally Posted by bads
hello! thanks that works! but still. this one
here is the scenario. I have scheduled 8:00 AM - 9:30 AM and 1:30 PM - 3:00 PM in my database. now the problem is when I choose 10:00 AM - 5:00 PM there is conflict because there is this 1:30 PM - 3:00 PM scheduled. but when I choose 9:30 AM -5:00 AM. this returns ok.
that 9:30 AM pisses me off.