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!
Thread: Scheduling help!
-
No Profile PictureContributing UserDevshed Newbie (0 - 499 posts)
- Join Date
- Dec 2012
- Posts
- 33
- Rep Power
- 2
Scheduling help!
-
No Profile PictureContributing UserDevshed Newbie (0 - 499 posts)
- Join Date
- Dec 2012
- Posts
- 165
- Rep Power
- 18
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 -
Contributing UserDevshed Newbie (0 - 499 posts)
- Join Date
- Nov 2012
- Posts
- 98
- Rep Power
- 2
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 -- -
No Profile PictureContributing UserDevshed Newbie (0 - 499 posts)
- Join Date
- Dec 2012
- Posts
- 165
- Rep Power
- 18
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 -
Come play with me!Devshed Supreme Being (6500+ posts)
- Join Date
- Mar 2007
- Location
- Washington, USA
- Posts
- 13,681
- Rep Power
- 9396
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. -
No Profile PictureContributing UserDevshed Newbie (0 - 499 posts)
- Join Date
- Dec 2012
- Posts
- 33
- Rep Power
- 2
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";
}
-
--Devshed Expert (3500 - 3999 posts)
- Join Date
- Jul 2012
- Posts
- 3,705
- Rep Power
- 1055
The reply directly above your post. This is the solution.Originally Posted by bads
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. -
No Profile PictureContributing UserDevshed Newbie (0 - 499 posts)
- Join Date
- Dec 2012
- Posts
- 33
- Rep Power
- 2
thank you for your reply.Originally Posted by Jacques1
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')");
-
Come play with me!Devshed Supreme Being (6500+ posts)
- Join Date
- Mar 2007
- Location
- Washington, USA
- Posts
- 13,681
- Rep Power
- 9396
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
- Jyncka agrees
-
No Profile PictureContributing UserDevshed Newbie (0 - 499 posts)
- Join Date
- Dec 2012
- Posts
- 165
- Rep Power
- 18
What are you doing? there should be 2 conditions set.
pseudo
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 conflictCode:WHERE (StartTime < '$newstart' AND EndTime >'$newstart') OR (StartTime < '$newend' AND EndTime >'$newend')
Comments on this post
- Jacques1 disagrees : We just told him to use the solution linked by requinix. Why do you come up with yet another approach, which doesn't even work?
-
Come play with me!Devshed Supreme Being (6500+ posts)
- Join Date
- Mar 2007
- Location
- Washington, USA
- Posts
- 13,681
- Rep Power
- 9396
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.Originally Posted by portcitysoftwar
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. -
--Devshed Expert (3500 - 3999 posts)
- Join Date
- Jul 2012
- Posts
- 3,705
- Rep Power
- 1055
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:
And here's an updated diagram:Code:WHERE date_end > $data_start AND date_start < $date_end
Comments on this post
- requinix agrees : well caught
Last edited by Jacques1; January 14th, 2013 at 06:00 PM.
-
Come play with me!Devshed Supreme Being (6500+ posts)
- Join Date
- Mar 2007
- Location
- Washington, USA
- Posts
- 13,681
- Rep Power
- 9396
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.
So yes, > and <.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 -
No Profile PictureContributing UserDevshed Newbie (0 - 499 posts)
- Join Date
- Dec 2012
- Posts
- 33
- Rep Power
- 2
hello! thanks that works! but still. this oneOriginally Posted by requinix
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.
-
No Profile PictureContributing UserDevshed Newbie (0 - 499 posts)
- Join Date
- Dec 2012
- Posts
- 165
- Rep Power
- 18
I believe there should be a condition for if it starts before an event AND ends after the event
Originally Posted by badshello! 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 12th, 2013, 10:14 AM
Reply With Quote
January 12th, 2013, 10:40 AM
January 12th, 2013, 07:42 PM
January 13th, 2013, 12:19 AM
January 13th, 2013, 12:47 AM
January 14th, 2013, 09:28 AM
January 14th, 2013, 10:41 AM
January 14th, 2013, 12:49 PM
January 14th, 2013, 01:00 PM
January 14th, 2013, 03:39 PM
January 14th, 2013, 04:56 PM
January 14th, 2013, 05:50 PM
January 14th, 2013, 06:33 PM
January 15th, 2013, 11:38 AM
January 15th, 2013, 11:40 AM
