[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!

[portcitysoftwar]

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!

[BitZoid]

I don't know if this works because I have not tried it but it's a start.

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

[portcitysoftwar]

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.

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

[requinix]

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.

[bads]

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?

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

[Jacques1]

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.

[bads]

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

$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')"); 

[requinix]

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.

[portcitysoftwar]

What are you doing? there should be 2 conditions set.

pseudo

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

[requinix]

Originally Posted by portcitysoftwar

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.

[Jacques1]

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:

WHERE
	date_end > $data_start
	AND date_start < $date_end

And here's an updated diagram:

[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.

                      $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 <.

[bads]

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.

                      $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.

[portcitysoftwar]

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.