0

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

Edited by israillaky: forgot some word

2
Contributors
38
Replies
39
Views
5 Years
Discussion Span
Last Post by israillaky
Featured Replies
  • 1
    diafol 3,669   5 Years Ago

    You'd be better off storing data as: [CODE]sub | start_time | end_time | day ================================= a | 900 | 1030 | 4 b | 1200 | 1345 | 5[/CODE] The days relate to the common format 0 = Sun ... 6 = Sat Times are set in integers (4 digit … Read More

  • 1
    diafol 3,669   5 Years Ago

    Why are you using HOUR()? Your formats are in hh:mm:ss. Also you don't need to concatenate simple variables: [CODE]"SELECT * FROM subjecschedule WHERE Room='$room' AND `Day`='$day' AND Sem='$sem' AND Schoolyear='$syear' AND LecTimestart < '$lectimestart' OR LecTimeend > '$lectimestart'"[/CODE] You'll probably need a few brackets at the end of the time … Read More

  • 1
    diafol 3,669   5 Years Ago

    Ok, so rephrase it and explain again. BUT on re-reading, maybe something like this: [QUOTE] Lectimestart Lectimeend 13:30:00 15:00:00 09:30:00 11:00:00[/QUOTE] 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 … Read More

  • 1
    diafol 3,669   5 Years Ago

    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 … Read More

  • 1
    diafol 3,669   5 Years Ago

    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. Read More

0

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 />";
0

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

Edited by israillaky: n/a

0

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

Edited by diafol: n/a

0

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

1

You'd be better off storing data as:

sub | start_time | end_time | day
=================================
a   | 900        | 1030     | 4
b   | 1200       | 1345     | 5

The 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)

Edited by diafol: n/a

0

im so sorry sir for delay reply

and posting a question that also related to this one
please forgave me for not replying you.

0

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?

0

In what format is the TIME_ROW_START and the TIME_ROW_END ? ANd the $timestart and $timeend

Edited by diafol: n/a

0

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."';
0

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

Edited by israillaky: n/a

0
"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.

0
"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?

0

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

0

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

Edited by israillaky: n/a

0

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

Edited by israillaky: n/a

0

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)"
0

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)"

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

0

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_type

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

0

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.

Edited by israillaky: n/a

1

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')"
0

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:00

and
$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?

0

Sorrry you're post makes no sense to me.

sorry sir for my bad post.

Edited by israillaky: n/a

0

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

Edited by israillaky: n/a

1

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
                         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 :)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.