How to trap time range in mysql table with same day

[israillaky]

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

[diafol]

soryy - my mistake will come back.

[israillaky]

ok sir i'll wait

[diafol]

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

$pos = strrpos($str, " ");
if ($pos !== false) {
	echo "STRRPOS/SUBSTR DAY: " . substr($str,$pos) . "";
}

$r = explode(" ",$str);
$day3 = $r[count($r)-1];
echo "EXPLODE DAY: $day3 ";

[israillaky]

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

[diafol]

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

[israillaky]

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

[diafol]

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)

[israillaky]

im so sorry sir for delay reply

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

[diafol]

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 ?

[israillaky]

yes sir if its ok to you if we continue the 2nd thread here

[israillaky]

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?

[diafol]

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

[israillaky]

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

[israillaky]

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

[diafol]

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

[israillaky]

"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[QUOTE=ardav;1754730]

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

[diafol]

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

[israillaky]

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

[israillaky]

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