PHP/MYSQL problem with some time thrown in!

[Shicky]

Here is the whole thing:

http://pastebin.com/LNXXASL6

It's only concerned with the submit section for now. Basically I want to check for conflicts between assigning shows for certain times. Here is my attempt:

$sql = "SELECT * FROM schedule WHERE starttime>='$starttime' AND endtime<='$endtime' AND day='$updateday'";
						$result = mysqli_query($myconnection, $sql);
						// Mysql_num_row is counting table row
						$count = mysqli_num_rows($result);						
										
							if($count >=1)
							{
								$error_msg = '<font color="#FF0000">*A show is already scheduled during that time slot.</font>';
							}
							else
							{

Frankly it isn't doing anything. I know the logic isnt spot on as it should only work for times inside other scheduled shows but this will do for now. The database uses TIME to store the starttime and endtime values. I collect the values from a user entry form like so:

$starttime = mysqli_real_escape_string($myconnection,$_POST['starttime']);
	$endtime = mysqli_real_escape_string($myconnection,$_POST['endtime']);
	
	$starttime = date($starttime, $timestamp);
	$endtime = date($endtime, $timestamp);

Should these not both be in the same format now? Can anyone tell me why this isn't working ?

 

[Eagle-Eye]

You should try using unix_timestamp instead in the MySQL database and PHP, it is a lot easier to perform these comparing functions as you do not need to worry about the formatting of the date.

To troubleshoot the error, echo the starttime and endtime values and see if the formats match with the MySQL Table.

 

[Shicky]

On submit the times are in the format 11:30, 11:45 - in the DB they are 11:30, 11:45 - is this enough to make a difference? I didn't think it would be.

Using unix_timestamp doesnt change anything

*Yup it isn't enough to make a difference, worked once and thought it was sorted but appears not. Entering values as 11:00:00 etc now but no change

 

[Eagle-Eye]

Well when I worked with time etc I used the full timestamp like so YYYY-MM-DD HH:MM:SS, PHP compares these values fine if thats any help, im sure using the unix timestamp would work as its basically an number which is easy to compare, maybe your conditionals are mixed up? I always got confused with them

 

[Shicky]

nevermind was being a tard but getting some really old behaviour from my code :/

 

[Eagle-Eye]

haha, what was wrong with it?

 

[Shicky]

spoke too soon, my conditional statement was wrong. However im sure my logic is spot on now yet its always messing up. Currently lets a user enter anything they want:

http://pastebin.com/M2sw2VeX

before that i was doing a count using mysqli_num_rows, each time it hit an error despite entering valid times.

Literally on the verge of suicide..

 

[Shicky]

solved, dear lord that was grim.

Warning for all, do not use brackets in BETWEEN statements

 

[Eagle-Eye]

haha glad you solved it, always learn more from solving your own problems

 

[bam0]

Not related to the issues you had but personally I prefer to use PDO rather than specific extensions. Also you should really be using prepared statements and parameter binding for your sql; firstly so you get into the habit to protect against sql injection and also because other control characters such as % and _ aren't escaped by mysqli_real_escape_string