Help.. Query problem.

[cyrixware]

Hey guys... i have here the source codes.

('$startTime' BETWEEN startTime AND endTime)

How to include this one into my query... 

This is my query:

$sql   = "SELECT * FROM schedule WHERE Section = '$Section' AND Day = '$Day' AND Room = '$Room' ";

 

Regards

 

[Sulman]

Are you using timestamps for start and end times?

 

If so you could do it like this:

 

<?php
$sql   = "SELECT * FROM schedule WHERE Section = '$Section' AND Day = '$Day' AND Room = '$Room' AND ('$startTime' > startTime AND '$startTime' < endTime)";
?>

 

However the fact that you have '$startTime' in quotes suggests that you are using strings...

[cyrixware]

Im not using Timestamps. i use TIME in my table structure.  

[cyrixware]

Source Codes:

 

<?php
  include("../connDB.php");
  
  if($_REQUEST['Submit'] == "Generate Schedule")
  {
    $Subject	  = $_REQUEST['Subj'];
    $Section 	  = $_REQUEST['Section'];
  	$Room	      = $_REQUEST['Room'];
$Day	      = $_REQUEST['Day'];
$startTime    = $_REQUEST['startTime'];
$endTime      = $_REQUEST['endTime'];
if(($endTime < $startTime) || ($endTime == $startTime))
{
?>
<script language="JavaScript">
	alert("Please select another time.");	
	//win = window.open('addSchedule.php','_self');
</script>
<?php
}
else
{
	/*$sql_search = "SELECT * FROM schedule WHERE Section = '$Section' && Day = '$Day' && Room = '$Room' || (startTime = '$startTime')";
	$q_search   = mysql_query($sql_search);
	$q_num	    = mysql_num_rows($q_search);
	if( $q_num != 0 ){
	*/
?>
		<script language="JavaScript">
			//win = window.open('addSchedule.php','_self');
			//alert("Query result. File already exist!");	
		</script>
<?php

	//}
	//else
	//{
		$sql   = "SELECT * FROM schedule WHERE Section = '$Section' AND Day = '$Day' AND Room = '$Room' ";

		$q_add = mysql_query($sql);
		$num   = mysql_num_rows($q_add);
		if( $num != 0 ){
?>
			<script language="JavaScript">
				//win = window.open('addSchedule.php','_self');
				alert("Query result. Time conflict!");	
			</script>
<?php
		}
		else
		{
			$sql = "INSERT INTO schedule (Section, 
										Subject, 
										Room, 
										Day,
										startTime, 
										endTime) VALUES 
									 ('$Section',
									 '$Subject',
									 '$Room',
									 '$Day',
									 '$startTime',
									 '$endTime')";
			if(!$q = mysql_query($sql))
			{
				echo "<font color=#FF0000 size=2 face=Verdana, Arial, Helvetica, sans-serif>
				<center><b>Please select another time.</b></center></font>";
			}		
			elseif(mysql_affected_rows() == 0)
			{
				echo "<font color=#FF0000 size=2 face=Verdana, Arial, Helvetica, sans-serif>
				<center><b>Cannot add record this time...</b></center></font>";
			}
			else
			{
?>
				<script language="JavaScript">
					alert("Record has been succesfully saved! ");
					//win = window.open('addSchedule.php','_self');
				</script>
<?php
			}
		}
	//}
  	}
}
?>

[fooDigi]

i think you could use strtotime.

 

<?php
$sql   = "SELECT * FROM schedule WHERE Section = '$Section' AND Day = '$Day' AND Room = '$Room' AND startTime > ".strtotime($startTime)." AND endTime < ".strtotime($endTime)." ";
?>

[fooDigi]

or use the TIME_TO_SEC mysql function to compare the time difference...

 

mysql.time

 

<?php
$sql   = "SELECT * FROM schedule WHERE Section = '$Section' AND Day = '$Day' AND Room = '$Room' AND startTime > TIME_TO_SEC($startTime) AND endTime < TIME_TO_SEC($startTime) ";
?>

[fooDigi]

i had that mixed up i think

 

 

<?php
$sql   = "SELECT * FROM schedule WHERE Section = '$Section' AND Day = '$Day' AND Room = '$Room' AND startTime < TIME_TO_SEC($startTime) AND endTime > TIME_TO_SEC($startTime) ";
?>

[cyrixware]

guys stil the same problem. i use this one coz this is correct i think.

 

'$startTime' BETWEEN startTime AND endTime

 

But when i try to include this into the query i can save the time weither the time is conflict or not.

 

$sql  = "SELECT * FROM schedule WHERE Day = '$Day' AND Room = '$Room' AND ('$startTime' BETWEEN startTime AND endTime) ";

 

by the way tnx fooDigi. is there any idea how to fix this one? 

[cyrixware]

hi guys.. got it heres the solution:

 

<?php$sql   = "SELECT * FROM schedule WHERE Day = '$Day' AND Room = '$Room' AND ('$startTime' BETWEEN startTime AND endTime || startTime = '$startTime') ";?>

 

Regards

[Barand]

$starttime BETWEEN starttime and endtime isn't the only conflicting condition

[pre]

                              DB          DB

                            starttime    endtime

=========================================================

conflicting bookings            |            |

                        S----------E        |

                                |            |

                                |      S---------E

                                |            |

                          S--------------------E

                                |            |

                                |  S----E    |

                                |            |

=========================================================

OK bookings                    |            |

                      S------E  |            |

                                |            |  S-----E

[/pre]

 

conflicts if (E > starttime) AND (S < endtime)

[cyrixware]

Thanks Barand. Got it.