Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    33
    Rep Power
    2

    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!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed 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
    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!
  4. #3
  5. Contributing User
    Devshed 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 --
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed 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
    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.";

  8. #5
  9. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,180
    Rep Power
    9398
    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.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed 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";
                            
                        } 
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    33
    Rep Power
    2
    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')"); 
  16. #9
  17. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,180
    Rep Power
    9398
    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
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    165
    Rep Power
    18
    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

    • 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?
  20. #11
  21. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,180
    Rep Power
    9398
    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.
  22. #12
  23. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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

    • requinix agrees : well caught
    Attached Images
    Last edited by Jacques1; January 14th, 2013 at 05:00 PM.
  24. #13
  25. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,180
    Rep Power
    9398
    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 <.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    33
    Rep Power
    2
    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.
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed 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 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.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo