How to trap time range in mysql table with same day - Page 2

[israillaky]

Why are you using HOUR()? Your formats are in hh:mm:ss.
Also you don't need to concatenate simple variables:

"SELECT * FROM subjecschedule WHERE Room='$room'  AND  `Day`='$day'   AND Sem='$sem' AND Schoolyear='$syear' AND LecTimestart < '$lectimestart' OR LecTimeend > '$lectimestart'"

You'll probably need a few brackets at the end of the time comparisons :

"... AND (LecTimestart < '$lectimestart' OR LecTimeend > '$lectimestart')"

sir i think i get it

by using your code
i can trap the time conflict

thank you sir much2x appreciated your kind

[israillaky]

Ok, so rephrase it and explain again. BUT on re-reading, maybe something like this:

If you wish to check let's say a time 08:00:00 to 09:30:00 - this should be allowed, if you check 09:00:00 to 10:30:00, it should be rejected - OF COURSE, only if on the same DAY.

I assume that you want to add a course/subject regardless of when the lectures/lab sessions are to be timetabled - so you should separate them (subject and timetable) as I suggested earlier.

If you DON'T, what you'll find is that you fill in all the subject details in a form along with timetable data (lecture or lab times) and then get an error message because there is a clash with an existing lecture or lab session, which ensures that your subject data IS NOT inserted into the DB. This is pointless. So, my STRONG advice would be to separate them.

USERS                    SUBJECT            TIMETABLE         ROOMS
user_id (PK, int)--      sub_id (PK)---|    tt_id  (PK)          ---room_id (PK)
(etc)              |     Subject       |--- sub_id (FK)         |   room_label
                   |     section            period_type (enum?) |   (etc)  
                   |     Descriptive        timestart           |
                   |     Instructor         timeend             | 
                   ----  ins_id (FK)        day                 | 
                         Sem                room_id (FK)--------
                         Schoolyear
                         capacity
                         Slot
                         status

With this you could further normalize your data, such as a buildings table, so relate rooms.buildings_id to it. Etc, I could go on, but let's stick to the point.

Add a subject from its own form.

Then in a timetable page, select the subject from a select dropdown and enter the details you would like for the lecture/lab session:

<form...>
<!-- get this info from the DB-->
<label for="subject">Subject:</label>
<select id="subject" name="subject">
  <option value="4">Biochemistry</option>
  (etc)
</select> 

<label for="ptype">Period Type:</label>
<select id="ptype" name="ptype">
  <option value="1">Lecture</option>
  <option value="2">Lab</option>
</select> 

<label for="day">Day:</label>
<select id="day" name="day">
  <option value="1">Mon</option>
  <option value="2">Tue</option>
 (etc until 5 = Fri - or change to your weekly cycle 0 =Sun, 6 = Sat)
</select> 

<!-- you could build this with PHP (for loop)-->
Timestart: 
<label for="timestarthr">hh</label>
<select id="timestarthr" name="timestarthr">
  <option value="0">00</option>
  <option value="1">01</option>
 (etc until 23)
</select> 
:
<select id="timestartmin" name="timestartmin">
  <option value="0">00</option>
  <option value="5">05</option>
 (etc until 55)
</select> 
<label for="timestartmin">mm</label>

<!-- you could build this with PHP (for loop)-->
Time end: 
<label for="timeendthr">hh</label>
<select id="timeendhr" name="timeendhr">
  <option value="0">00</option>
  <option value="1">01</option>
 (etc until 23)
</select> 
:
<select id="timeendmin" name="timeendmin">
  <option value="0">00</option>
  <option value="5">05</option>
 (etc until 55)
</select> 
<label for="timeendmin">mm</label>

<input ... your submit button />

</form>

You just accept the times from the form, build them into a format that is acceptable in your DB (hh:mm:ss):

$starttime = str_pad($_POST['timestarthr'], 2, "0", STR_PAD_LEFT) . ":" . str_pad($_POST['timestartmin']), 2, "0", STR_PAD_LEFT) . ":00";
$endtime = str_pad($_POST['timeendhr'], 2, "0", STR_PAD_LEFT) . ":" . str_pad($_POST['timeendmin'], 2, "0", STR_PAD_LEFT . ":00";
$day = intval($_POST['day']);

//you should check the end time is > start time
//a check should also be done to ensure that incoming data was actually integer and within the right bounds (0-23 or 0-55).

$q = mysql_query("SELECT tt_id, timestart, timeend FROM timetable WHERE (timestart <= '$starttime' AND timeend > '$starttime') OR (timestart < '$endtime' AND timeend >= '$endtime') OR  (timestart > '$startime' AND timeend < '$endtime') AND `day`=$day");
if(mysql_num_rows($q)){
  
  echo "Collision found with the following:<br />"
  while($r = mysql_fetch_array($q)){
    echo $r['timestart'] . " - " . $r['timeend'] . "<br />";
  } 
}else{
    //do insert into timetable here
    $q = mysql_query("INSERT INTO timetable SET ....");
}

THis won't be perfect for you, but it gives you an idea. This was off the top of my head, so it's not tested. As you can see, I was a little bored this morning :)

wow it was great sir

i will use this one :icon_smile:

thank you sir your a life saver this is great idea much2x appreciated
i owe you sir i will try this

OK, glad to be of help. HOWEVER, this setup is just a very basic snippet. A better solution may be to display a timetable (like an agenda view of Google Calendar), then you can see when all sessions are booked before querying your lecture/lab session data. As at the moment this solution will query 'blindly' - that is you have no way of knowing beforehand if the data you enter will be accepted - as the timetable fills up with periods, this could become extremely frustrating as you get 'collision' after 'collision'.

[israillaky]

OK, glad to be of help. HOWEVER, this setup is just a very basic snippet. A better solution may be to display a timetable (like an agenda view of Google Calendar), then you can see when all sessions are booked before querying your lecture/lab session data. As at the moment this solution will query 'blindly' - that is you have no way of knowing beforehand if the data you enter will be accepted - as the timetable fills up with periods, this could become extremely frustrating as you get 'collision' after 'collision'.

ok sir i will try

after i Finnish i will try my best to create a timetable display if for a room so the user will notice if the room has fully booked

i will ask later if i got confuse again

thank you for your kind

OH, I forgot about ROOM! Of course! I was so wrapped up in the time thing, it totally flew by me. OK, that shouldn't be too difficult to factor into the example I gave.

[israillaky]

OH, I forgot about ROOM! Of course! I was so wrapped up in the time thing, it totally flew by me. OK, that shouldn't be too difficult to factor into the example I gave.

ok sir

thank you :)

[israillaky]

sir can i add you as a friend here or in the facebook?

so i can ask directly

this thread has been answered by you if i have a follow up question i can ask directly to you? if its ok?

Thanks :)

With regard to questions, you should always post to the forum. The reason for this is that if can't help you, somebody else could and you wouldn't have to repeat yourself. It's also good for others when they browse for a solution. In addition to that, I don't accept messages pertaining to help (at all).

You're welcome to add me as a friend on here if you like. I don't like FB, so I never use it, or very rarely.

[israillaky]

Thanks :)

With regard to questions, you should always post to the forum. The reason for this is that if can't help you, somebody else could and you wouldn't have to repeat yourself. It's also good for others when they browse for a solution. In addition to that, I don't accept messages pertaining to help (at all).

You're welcome to add me as a friend on here if you like. I don't like FB, so I never use it, or very rarely.

ok sir :) thank you so much for your help