[SOLVED] Deny insert if time is in between a pre existing time

[rondog]

I need help comparing two dates. I am using the UNIX time stamp by the way.

 

Before I insert I want to check to make sure no event is overlapping another. Basically their cant be two events within the same time frame.

 

inserting a date of 'july 21st' to 'july 25th' will come into conflict if someone makes an event on 'july 24th' to 'july 30th' because 'july 24th' is within the event from 'july 21st' to 'july 25th'

 

Right now I have:

<?php
if(isset($_POST['add'])) {
    $begdate_m = $_POST['begdatem'];
    $begdate_d = $_POST['begdated'];
    $begdate_y = $_POST['begdatey'];
    $enddate_m = $_POST['enddatem'];
    $enddate_d = $_POST['enddated'];
    $enddate_y = $_POST['enddatey'];
    $startdate = $begdate_y."-".$begdate_m."-".$begdate_d;
    $enddate = $enddate_y."-".$enddate_m."-".$enddate_d;
    $startdate = strtotime($startdate);
    $enddate = strtotime($enddate);
    
    $add = true;
    $query = mysql_query("SELECT * FROM de_featuredevent") or die(mysql_error());
    while($row = mysql_fetch_array($query))
    {
        $sql = mysql_query("SELECT 1 FROM de_featuredevent WHERE ($row[start_date]>$startdate AND $row[start_date]<$enddate) OR ($row[end_date]>$startdate AND $row[end_date]<$startdate)") or die(mysql_error());
        echo "SELECT * FROM de_featuredevent WHERE '$startdate' OR '$enddate' BETWEEN '$row[start_date]' AND '$row[end_date]'";
        $num = mysql_num_rows($sql);
        if($num == 0)
        {
//If their is none found do this
            echo " = timing ok<br>";
        }
        else
        {
            echo " = time mismatch<br>";
            $add = false;
        }
    }
    if($add == false)
    {
        echo "not adding";
    }
    else
    {
        echo "adding";
    }
}
?> 

 

If I choose jan 8th - jan 13th, I get a time mismatch since their is already an event jan 10th - jan 15th

 

That is working the way it should.

 

However if I choose, jan 11th - jan 14th, it allows me to add it. I have an event from jan 10th - jan 15th. It should give a time mismatch but it is working incorrectly and allowing me to add it.

 

Here is a link to the page that has this form..you can see the echos I am putting out so you get the idea. The current event dates are below.

 

http://drewestate.com/new/2008/admin/featured-event-manager.php?action=viewfeatured

[sader]

I would try to do it with one query

 

something like

$sql = "SELECT * FROM table_name WHERE end_date < $begindate LIMIT 1"

 

if query gets results then u cant add event

[rondog]

ahh no that isnt going to work..I think your idea of doing it with one query is better and limiting it to 1 makes sense.

 

My query still isnt working however. It still lets me add if I choose a date between two already made dates like

 

Jan 10th - Jan 15th is an event..if I pick Jan 11th - Jan 14th it allows me to add it. I cant allow that!

 

If I do Jan 8th - Jan 12th it works fine.

<?php
if(isset($_POST['add'])) {
$begdate_m = $_POST['begdatem'];
$begdate_d = $_POST['begdated'];
$begdate_y = $_POST['begdatey'];
$enddate_m = $_POST['enddatem'];
$enddate_d = $_POST['enddated'];
$enddate_y = $_POST['enddatey'];
$startdate = $begdate_y."-".$begdate_m."-".$begdate_d;
$enddate = $enddate_y."-".$enddate_m."-".$enddate_d;
$startdate = strtotime($startdate);
$enddate = strtotime($enddate);

$query = mysql_query("SELECT * FROM de_featuredevent WHERE (start_date > $startdate AND start_date < $enddate) OR (end_date > $startdate AND end_date < $startdate) LIMIT 1") or die(mysql_error());
$num = mysql_num_rows($query);
if($num == 1)
{
	echo "found one";
}
else
{
	echo "found none";
}
}
?>

 

I've been trying to figure this out for three days :[ sooo frustrating!

[sader]

does it help?

 

"SELECT * FROM de_featuredevent WHERE (start_date < $startdate AND $startdate < end_date) OR (start_date < $enddate AND $enddate < end_date) LIMIT 1"

[samshel]

try is this works...

"SELECT * FROM de_featuredevent WHERE ('$startdate' BETWEEN '$row[start_date]' AND '$row[end_date]') OR ('$enddate' BETWEEN '$row[start_date]' AND '$row[end_date]')";

[rondog]

Thanks guys I finally got it to work!

$sql = mysql_query("SELECT * FROM de_featuredevent WHERE '$startdate' BETWEEN start_date AND end_date OR '$enddate' BETWEEN start_date AND end_date") or die(mysql_error());