SELECT problem or is it my logic?

[aussieguy]

I am trying to produce an events planer where people can select different events and place them into a temporary table called cart.

before adding a new selection to the cart I want to check that the new event is not in conflict with already selected events that are in the database.

cart table stucture ( user, date, starttime and endtime )
using form variables $user, $date, $starttime and $endtime

so if the new event starts before a previosly selected event ends that is an error

OR if the new event ends after a previously selected event starts that is also an error.

is it possible to create a select statement that will find any row that fits the above criteria

this example of what I tried doesn't do the job but it will show what I am trying to do

$query = "SELECT * FROM cart
WHERE
user = '$sess'

AND dat = '$date'
AND ('$endtime' BETWEEN starttime AND endtime)
OR $endtime >= starttime
";
$result = mysql_query($query) or die ("date and time query can not do query");
$num_rows = mysql_num_rows($result);
if ($num_rows > 0)
{
$eop = 1; // used to denote there is an error
$start =1; // used to display the correct error message
}

So my question is
is it possible to create a single select query to do the job

OR do I need to create multiple select queries

OR do I need to just select the rows with the same user and date put the results into a array and then process the array with some sort of logic data seek?

I am totaly at a loss I get it to work correctly (i think) but then it stops working because a event seems to fall between the criteria

in the starttime and endtime is just the hour in millitary time
ie 11 or 12 or 13(1pm) or 14(2pm)

sample data
--------start--- end-------------------------
event 1 | 6 | 12 | (6am till Noon)
event 2 | 8 | 14 | (8 am till 2 pm)
event 3 | 12 | 16 | (noon till 4 pm)
event 4 | 19 | 24 | (7 pm till midnight)

event 4 seams to kill the logic? argghhhhhhhhhhhh

please post any suggestions along with sample code because Ive written and rewitten select statements until the cows came home and still can't work out what appears to be a simple query.

Thanx in advance this forum has been a godsend for other questions I've had :-)

[ultraslacker]

SELECT *
FROM cart
WHERE user = '$sess'
AND dat = '$date'
AND (('$endtime' BETWEEN starttime AND endtime) OR ('$starttime' >= starttime AND '$starttime' <= endtime)

I assume that is what you meant.

There is no performance benefit to using BETWEEN - 3.23.x can have a performance loss from it.

[aussieguy]

thanx ultraslacker

I tried your select query and got an error could not do query.
but I did learn something from your code which was the placement of brackets correctly...

Is there a better way than using BETWEEN ?

I fiddled with my code this morning and broke it into two select statemets this works but is there a better cleaner way to write the following.

// first check if start time is between values already stored in DB

$query = "SELECT * FROM cart
WHERE
user = '$sess'

AND dat = '$date'
and '$starttime' > starttime and '$starttime' < endtime
";
$result = mysql_query($query) or die ("date and time query can not do query");

$num_rows = mysql_num_rows($result);

if ($num_rows > 0)
{$yes1 =1; }

// print "1number of rows = $num_rows"; // for debugging

// check if new event ends after an DB event has started
$query = "SELECT * FROM cart
WHERE
user = '$sess'

AND dat = '$date'
AND '$endtime' > starttime AND '$starttime' < starttime
";
$result = mysql_query($query) or die ("<br>2 date and time query can not do query");

$num_rows = mysql_num_rows($result);

if ($num_rows > 0)
{$yes2 = 1; }

// print "<br>2number of rows = $num_rows"; // for debugging

// print "<br> yes1 = $yes1 yes2 = $yes2 "; // for debugging

// check if either of the above are 1
if ( ($yes1 > 0 ) || ($yes2 > 0))
{
$yes =1; // values are used to generate the error messages
$eop = 1;
$start =1;

}

[ultraslacker]

There should be another ')' at the end of my query.