I have a time stored in table 8:00AM-9:30AM TTH . I want to check time range, if user selected 9:00am-10:30am Thursday comes in between time 8:00AM -9:30AM TTH? How to trap it out in php.
Time is stored in table as below
Time Day
8:00AM -9:30AM TTH
10:30AM -12:00PM MW
1:00PM -2:30PM MW
10:30AM -12:00PM FW
help me please this is my one of my error trap in my thesis project
thank you in advance
soryy - my mistake will come back.
ok sir i'll wait
I don't quite understand what you're trying to achieve. Is it split the string into parts, to get the day? IF so, you have a number of ways, SOME of which follow:
$str = "9:30PM -10:30PM TTH";
$r = preg_split('/ /',$str);
$day1 = $r[count($r)-1];
echo "PREG DAY: $day1 <br />";
$pos = strrpos($str, " ");
if ($pos !== false) {
echo "STRRPOS/SUBSTR DAY: " . substr($str,$pos) . "<br />";
}
$r = explode(" ",$str);
$day3 = $r[count($r)-1];
echo "EXPLODE DAY: $day3 <br />";no sir. sorry for my understandable question!
what i mean sir is how to get the conflict time between
TIME DAY
8:00am-9:30am | TTH
and
9:00am-10:30am | THURSDAY
what i've been trying to do sir si to make an student enlistment or student subject schedule
hope you can help me
OK, I have some code (24-ish lines) that solves your problem if all formats are consistent. You have provided 2 different formats in your posts. Where is the data held in a db?
is the format:
8:00AM -9:30AM TTH
or
8:00am-9:30am | TTH
or
9:00am-10:30am | THURSDAY
yes sir in mysql tble.
i use two table which is the tableschedule is the time to be selected
example
fields:sub , time , day
data:a ,9:00AM -10:30AM, TTH <---- if user select this it will get the range time from another table which
tablereserve
fields:sub, time , day
data:b, 8:00AM -9:30AM, THURSDAY
my problem is i dont know how to get the range time with same day and sir the time is string type
can you give me some advice how to do it sir?
hope you understand
thank's in advance
You'd be better off storing data as:
sub | start_time | end_time | day
=================================
a | 900 | 1030 | 4
b | 1200 | 1345 | 5The days relate to the common format 0 = Sun ... 6 = Sat
Times are set in integers (4 digit max: 0 - 2359)
You can now use simple arithmetic comparisons (<, >, >=, <=) to check collisions.
To check if a new period collides with any existing periods, try this logic for a loop:
IF
(newClassStartTime >= storedStartTime AND newClassStartTime < storedEndTime)
OR
(newClassEndTime > storedStartTime AND newClassEndTime <= storedEndTime)
OR
(newClassStartTime <= storedStartTime AND newClassEndTime >= storedEndTime)
THEN
PRINT "It's a clash!"Note the above is in pseudo-code (not some freaky SQL)
im so sorry sir for delay reply
and posting a question that also related to this one
please forgave me for not replying you.
Delay is no problem - just spreading the problem over a few threads - that's confusing. So, where are you going to proceed with this here or in this thread : http://www.daniweb.com/web-development/php/threads/411262 ?
yes sir if its ok to you if we continue the 2nd thread here
yes sir if its ok to you if we continue the 2nd thread here
this queries is sample
SELECT * FROM table_name
WHERE DATE(TIME_ROW_START) BETWEEN '".$timestart."' AND DATE(TIME_ROW_END) BETWEEN '".$timeend."';
// this doesn't appear to be working either
SELECT * FROM table_name
WHERE DATE(TIME_ROW_START) >= '".$timestart."'
AND DATE(TIME_ROW_END) <= '".$timeend."';Is there a way to do this?
In what format is the TIME_ROW_START and the TIME_ROW_END ? ANd the $timestart and $timeend
Delay is no problem - just spreading the problem over a few threads - that's confusing. So, where are you going to proceed with this here or in this thread : http://www.daniweb.com/web-development/php/threads/411262 ?
yes sir
sir i have question is there a way to do this?
// this doesn't appear to be working
SELECT * FROM table_name
WHERE DATE(TIME_ROW_START) BETWEEN '".$timestart."' AND DATE(TIME_ROW_END) BETWEEN '".$timeend."';
// this doesn't appear to be working either
SELECT * FROM table_name
WHERE DATE(TIME_ROW_START) >= '".$timestart."'
AND DATE(TIME_ROW_END) <= '".$timeend."';In what format is the TIME_ROW_START and the TIME_ROW_END ? ANd the $timestart and $timeend
TIME sir not timestamp
00:00:00
"SELECT * FROM table_name
WHERE TIME_ROW_START >= '$timestart' AND TIME_ROW_END =< '$timeend'";That example will check whether the new times ($timestart and $timeend) are found AT or WITHIN any stored periods. For further collisions you'll need to check whether $timestart is WITHIN TIME_ROW_START and TIME_ROW_END and $timeend is WITHIN TIME_ROW_START and TIME_ROW_END.
"SELECT * FROM table_name WHERE TIME_ROW_START >= '$timestart' AND TIME_ROW_END =< '$timeend'";That example will check whether the new times ($timestart and $timeend) are found AT or WITHIN any stored periods. For further collisions you'll need to check whether $timestart is WITHIN TIME_ROW_START and TIME_ROW_END and $timeend is WITHIN TIME_ROW_START and TIME_ROW_END.
ok sir i will try this one
how about like this sir
"SELECT * FROM table_name WHERE TIME_ROW_START >= '$timestart' AND TIME_ROW_END >= '$timestart'" and LABTIME>= '$timestart' AND LABTIMEEND>= '$timestart'";is this going to work?
Tried it? I don't quite follow the logic. Where did LABTIME come from??
Can you post your MySQL table structure and datatypes? Also describe what the time-related fields are for. Then perhaps we'll be able to make sense of what you're trying to do.
I assume that you're getting a start and end time from a form and then want to check that this new 'period' doesn't collide with exisitng DB-stored 'periods'.
Tried it? I don't quite follow the logic. Where did LABTIME come from??
Can you post your MySQL table structure and datatypes? Also describe what the time-related fields are for. Then perhaps we'll be able to make sense of what you're trying to do.
I assume that you're getting a start and end time from a form and then want to check that this new 'period' doesn't collide with exisitng DB-stored 'periods'.
OK Sir this is my mysql tblsubjectschedule structure
tblsubjecschedule
sub_id int pk
Subject
section
Lec
Lab
Descriptive
LecTimestart (TIME)
LecTimeend (TIME)
LabTimestart (TIME)
LabTimeend (TIME)
Day
Labday
Room
Labroom
Instructor
ins_id
Sem
Schoolyear
capacity
Slot
status
the other fields are all text
AND ALL I WANT TO DO SIR IS TO TRAP THE RANGE IF TIME WITH SAME ROOM OR LABROOM AND WITH SAME DAY
and this is the code i try sir but this doesn't work it keep inserting the data if i refresh the page and it desnt trap the range time. or did i make it wrong?
$sql3=mysql_query("Select * from tblsubjecschedule where Room='".$room."' and Day='".$dlec ."' and Sem='".$sem."'and Schoolyear='".$syear."' and LecTimestart <= '".$lectimestart."' and LecTimeend > '".$lectimestart."' and LecTimeend = '".$lectimestart."'") or die(mysql_error(mysql_error()));
$r=mysql_num_rows($sql3);
if($r!==0){
echo '<script language="javascript">alert("Time conflict");</script>';
}else{
$inser=mysql_query("Insert into tblsubjecschedule (Subject,section,Lec,Lab,Descriptive,LecTimestart,LecTimeend,LabTimestart,LabTimeend,Day,Room,Instructor,ins_id,Sem,Schoolyear,capacity,Slot,status)values('".$subj."','".$sec."','".$lec."','".$lab."','".$desc."','".$lectimestart."','".$lectimeend."','".$labtimestart."','".$labtimeend."','".$day."','".$room."','".$uid22."','".$gid333."','".$sem."','".$syear."','".$cap."','".$slot."','".$stat."')")or die(mysql_error());
echo '<script language="javascript">alert("'.$subj.'Successfully Added23");</script>';
$lectimestart="";
$lectimeend="";
$labtimestart="";
$labtimeend="";
$day="";
$room="";
$subj="";
$lec="";
$lab="";
$desc="";
$ins="";
$cap="";
$sec="";
$stat="";
}Just an idea:
LecTimestart (TIME)
LecTimeend (TIME)
LabTimestart (TIME)
LabTimeend (TIME)
You seem to be duplicating lab and lecture fields all the way through. In order to make this a better structure, I'd do this:
SUBJECT
sub_id
Subject
section
Descriptive
Instructor
ins_id
Sem
Schoolyear
capacity
Slot
status
SUBJECT_PERIODS
period_id (PK)
sub_id (FK)
period_type (e.g. 1=lab, 2=lec)
Timestart (TIME)
Timeend (TIME)
Day
Room
Also is Instuctor and ins_id pertaining to the same thing in another table?
This means you now only need to check times against 2 fields.
"SELECT period_id FROM subject_periods WHERE ($starttime BETWEEN Timestart AND Timeend) OR ($endtime BETWEEN Timestart AND Timeend) OR ($starttime < Timestart AND $endtime > Timeend)"Just an idea:
LecTimestart (TIME)
LecTimeend (TIME)
LabTimestart (TIME)
LabTimeend (TIME)You seem to be duplicating lab and lecture fields all the way through. In order to make this a better structure, I'd do this:
SUBJECT
sub_id
Subject
section
Descriptive
Instructor
ins_id
Sem
Schoolyear
capacity
Slot
statusSUBJECT_PERIODS
period_id (PK)
sub_id (FK)
period_type (e.g. 1=lab, 2=lec)
Timestart (TIME)
Timeend (TIME)
Day
RoomAlso is Instuctor and ins_id pertaining to the same thing in another table?
This means you now only need to check times against 2 fields.
"SELECT period_id FROM subject_periods WHERE ($starttime BETWEEN Timestart AND Timeend) OR ($endtime BETWEEN Timestart AND Timeend) OR ($starttime < Timestart AND $endtime > Timeend)"
the instructor fields is to get the value of instructor name if user put some instructor or defult instructor and the ins_id would be the key to view if the instructor wants to view their subject.
sir if i use the tblsubject and tblsubject_periods how to join them in to 1 query?
for display and inserting
by the way sir its good idea
For display:
SELECT subject.*, subject_periods.* FROM subject INNER JOIN subject_periods ON subject.sub_id = subject_periods.sub_id ORDER BY subject.sub_id, subject_period.period_typeThis may give you 2 rows for each subject (lectures and labsessions). Even more if the subject has many lectures or labsessions in the week. BTW it's not advisable to use tablename.* in a query as it can be quite wasteful - I've just used it for brevity.
For inserting - do you need to insert a lecture/labsession period at the same time as setting up a subject from scratch? I think it can be done, but I'd do 2 separate queries (two diff forms) for this. The period form could have a dropdown:
<select name="period_type">
<option value="1">Lecture</option>
<option value="2">Lab</option>
</select>If I was going at this, I'd make a have dynamic additions on the page. E.g. 'add another period' button - when clicked, shows another set of inputs for entering another period (with details). Ajax may help you out here.
Anyway. Just a few thoughts.
For inserting - do you need to insert a lecture/labsession period at the same time as setting up a subject from scratch? yes is if the subject has a lecture and labsession time.
how about this sir i use this as example
("Select * from subjecschedule where Room='".$room."' and Day='".$day."' and Sem='".$sem."'and Schoolyear='".$syear."' and HOUR(LecTimestart )< '".$lectimestart."' or HOUR(LecTimeend) > '".$lectimestart."'")but it dsnt work
or i did't wrong?
it just an idea.
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')"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 how about
Lectimestart Lectimeend
00:00:00 00:00:00
13:30:00 15:00:00
09:30:00 11:00:00and
$lectimestart is equal to 10:00:00
how to prevent that in MySQL instead getting the range of 00:00:00 i want to get the next value instead of 00:00:00 until 09:30:00 it well escape the row if the query detects the 00:00:00
and if the query detects the range it will return true if not false so i can insert the time slot with no range or equal time
is there a way to do this?
Sorrry you're post makes no sense to me.
Sorrry you're post makes no sense to me.
sorry sir for my bad post.
Sorrry you're post makes no sense to me.
So Sorry sir my bad
im so sorry i post a wrong thread
instead of how to trap time conflict in mysql table with same day
i put How to trap time range in mysql table with same day
im so so sorry sir im so so stupid
Ok, so rephrase it and explain again. BUT on re-reading, maybe something like this:
Lectimestart Lectimeend
13:30:00 15:00:00
09:30:00 11:00:00
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
statusWith 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 :)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.