Mysql Date Query

[thecase]

Hi,

 

I'm creating a booking form but am having problems. In the table I have two fields set to Time: starttime and endtime. I am trying to create a query that checks in between these times to stop double bookings so something like this

 

mysql_query("SELECT TRUE FROM studiorota WHERE date='$_POST[date]' AND starttime <= '$_POST[timestart]' AND endtime > '$_POST[timeend]' ");

 

 

Although it doesn't work as expecting resulting FALSE when it should be TRUE so how do I set the query up correctly to compare the two time fields.

 

 

 

Thanks

[Christian F.]

First of all you need to escape the output, as your script is completely open to SQL injections. You should also validate your input, to ensure that you don't get erroneous data from the user. If so, give them a message and ask them to fill the form out correctly.

 

Secondly, you need to verify what the variables actually contains, and that it adheres to the format that the times are saved in the database. Since you haven't posted any further details about your system, such as what the dates and times are saved as in the DB, what the $_POST array contains, etc, we cannot help you further.

[thecase]

This is how the database is set up

 

 

 `starttime` time NOT NULL,
 `endtime` time NOT NULL

 

All the $_POST data comes from a drop down (as I limit the user to only certain times) so I know that I am going to be getting the exact format I need.

 

Thanks

[Christian F.]

I'm sorry, but you don't know, you assume. Even with just my browser (Opera) it's trivial to edit that form, and have it send whatever data I want. All I need to do is to view source, edit the HTML code as I see fit, and hit F5.

 

Also, you haven't fully complied with my request for more information.

If you expect us to spend out free time to help you (for free to boot), it is common courtesy to make sure you post as many details as possible right away. So that we don't have to waste time trying to pull the details from you one question at the time.

Even when you think you know where the problem lies, chances are that it is actually located somewhere else. Especially since if you'd know where it was you'd be able to fix it yourself. So just posting one line of code, and an incomplete description of what you want + what you're getting, will in most cases be insufficient for anyone to identify the actual problem.

 

Please do keep that in mind for future posts, thanks.

[thecase]

Sorry I didn't know how much info was necessary is this any better?

 

The Code:

--
-- Table structure for table `studiorota`
--

CREATE TABLE IF NOT EXISTS `studiorota` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `show` varchar(100) NOT NULL,
 `date` date NOT NULL,
 `starttime` time NOT NULL,
 `endtime` time NOT NULL,
 `volunteer1` int(11) NOT NULL,
 `volunteer2` int(11) NOT NULL,
 `volunteer3` int(11) NOT NULL,
 `volunteer4` int(11) NOT NULL,
 `removedate` int(10) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `volunteer2` (`volunteer2`),
 KEY `volunteer3` (`volunteer3`),
 KEY `volunteer4` (`volunteer4`),
 KEY `volunteer1` (`volunteer1`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `studiorota`
--

INSERT INTO `studiorota` (`id`, `show`, `date`, `starttime`, `endtime`, `volunteer1`, `volunteer2`, `volunteer3`, `volunteer4`, `removedate`) VALUES
(1, 'An Hour Of Fun', '2012-09-05', '17:00:00', '18:00:00', 1, 38, 48, 16, 1348763388),
(2, 'Talk', '2012-09-19', '10:00:00', '12:00:00', 19, 8, 53, 29, 1348763532);

mysql_query("SELECT TRUE FROM studiorota WHERE date='$_POST[date]' AND starttime <= '$_POST[timestart]' AND endtime > '$_POST[timeend]' ");

 

The problem is for example ID 1 the system must come back as TRUE when the user selects a start/end time between 1700 and 1800 although the query I already tried lets the user start at 1730 or end at 1730 clashing with ID 1.

 

As for SQL Injections and Data verification I can implement this when the query is fully working but any pointers? Hope this is clearer

[Christian F.]

It does help a bit, yes, but I still don't know what the $_POST array contains. As requested in my first post.

 

Also, you need to make sure the query is, in fact, constructed correctly. Do that by replacing the call to mysql_query () by this:

$query = "SELECT TRUE FROM studiorota WHERE date='$_POST[date]' AND starttime <= '$_POST[timestart]' AND endtime > '$_POST[timeend]' ";
var_dump ($query);
if (!$res = mysql_query($query)) {
   // Failed querying the database, add error message.
   echo mysql_error ();
}

Now, I'm fairly certain I know what the problem could be. There are two alternatives that I see as most likely from the code you've posted so far, but I cannot confirm either of them without the error message and/or the actual content if the variables you're using.

[thecase]

It comes back with

 

string 'SELECT TRUE FROM studiorota WHERE date='2012-09-19' AND starttime <= '17:30' AND endtime > '16:30' ' (length=99)

 

There is no error just coming back with the wrong results. The $_POST data is from a dropdown like this

 

echo '<p>Start: <select name="timestart">';
for ($i = $start; $i <= $end; $i += 1800)
{
echo '<option>' . date('G:i', $i);
}
echo "</select></p>";

[kicken]

WHERE date='2012-09-19' AND starttime <= '17:30' AND endtime > '16:30'

 

That condition does not make any sense. You need to know if the selected time frame overlaps with any existing time frame. In order to do that you need to check that none of the stored start or end times occur between the entered start and end time frame.

WHERE 
  date='2012-9-19'
  AND (
     starttime BETWEEN '16:30' AND '17:30'
     OR endtime BETWEEN '16:30' AND '17:30'
  )

 

If 0 rows match that condition then the time frame is ok. Otherwise you have a conflict.

Edited September 24, 2012 by kicken

[thecase]

Thank you works great and learnt some new conditions

[Barand]

That won't pick up all the conditions - see the 4th one in the image in this post

 

http://forums.phpfreaks.com/topic/268665-filtering-dumped-data-based-on-rown-conditions/?do=findComment&comment=1380131