Need help comparing dates

[rondog]

I am making an interface that allows you to create an event specified with a start date and an end date.

 

I am posting the data to mysql right now into a date field that looks like:

 

2008-07-21 (july 21st 2008)

 

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 2008 to july 25th 2008 will come into conflict if someone makes an event on july 24th 2008 to july 30th 2008.

 

Hope that makes sense. Would I do the comparison with PHP or should I use mysql date functions to do this?

[realjumper]

I would use timestamp

[rondog]

Alright that will work too, although I am not using time of day.

[rondog]

How would I compare like I explained above? Any tips?

[realjumper]

Well, I would insert the start date (as a timestamp) in the 'startdate' field. I would insert the finish date (as a timestamp) in the 'finishdate' field. If another start time or finish time falls between 'startdate' and 'finishdate', then don't insert/accept it. It's a simple query to see whether the new startdate falls between the existing startdate and the existing finishdate.....and another simple query to see whether the new finishdate falls between the existing startdate and the existing finishdate. If it does, don't accept/insert the new record. If it doesn't, then the time frame required is free, so, insert it

[rondog]

explain to me your reasoning why time stamp would be a better choice. I am not using time. I think the date field would work fine right? Unless their is something you know that I don't.

[realjumper]

I didn't say my way is better, I just said that is the way I would do it. The reason I would do it with timestamps is because they are so easy to work with, and they are foolproof

[rondog]

ahh I cant figure this out...

 

im trying things like:

$sql = mysql_query("SELECT start_date FROM de_featuredevent WHERE start_date BETWEEN '$startdate' AND '$enddate'") or die(mysql_error());
$num = mysql_num_rows($sql);
if($num > 0)
{
	echo "time mismatch";
}
else
{
	echo "go ahead and add";
}

but no luck ???

[rondog]

ok im getting a bit close, but still not working!

<?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;
$add = true;
//$sql = mysql_query("SELECT start_date FROM de_featuredevent WHERE start_date > '$startdate' AND end_date < '$startdate'") or die(mysql_error());
$query = mysql_query("SELECT * FROM de_featuredevent") or die(mysql_error());
while($row = mysql_fetch_array($query))
{
	$sql = mysql_query("SELECT * FROM de_featuredevent WHERE '$startdate' OR '$enddate' BETWEEN '$row[start_date]' AND '$row[end_date]'") 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)
	{
		echo "=time mismatch<br>";
		$add = false;
	}
	else
	{
		echo "=go ahead and add<br>";
	}
}
if($add == false)
{
	echo "not adding";
}
else
{
	echo "adding";
}
}

 

If I do a test run that SHOULD work I get the following result:

SELECT * FROM de_featuredevent WHERE '2008-02-13' OR '2008-02-14' BETWEEN '2008-01-01' AND '2008-01-05'=time mismatch
SELECT * FROM de_featuredevent WHERE '2008-02-13' OR '2008-02-14' BETWEEN '2008-02-05' AND '2008-02-10'=time mismatch
not adding 

 

what am I doing wrong??

[mg.83]

Hi rondog,

 

this is what i would do (i never seem to have much luck with the timestamp/datetime etc in mysql)

 

i would set the field in the db that i am saving the date to as an int

 

then i would get my date (needs to be in string form - parse it if necessary)

 

then using strtotime i would convert it to a unix timestamp (which is an integer)

 

this would then be saved to the db

 

the beauty of this is that it makes it very simple to compare dates by comparing the value of the timestamp

 

then you can use date() to format it to your liking ie(date("d-m-Y"),"timestamp from db")

 

just my 2 cents

[rondog]

Thanks mg.83..that sounds like a good plan..So what I am doing so far is the following and I am getting the same output.

<?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 * FROM de_featuredevent WHERE '$startdate' OR '$enddate' BETWEEN '$row[start_date]' AND '$row[end_date]'") 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)
	{
		echo "=time mismatch<br>";
		$add = false;
	}
	else
	{
		echo "=go ahead and add<br>";
	}
}
if($add == false)
{
	echo "not adding";
}
else
{
	echo "adding";
}
}
?>

 

I have two dates in the database right now:

 

Jan 2 - Jan 5

&

Jan 7 - Jan 10

 

If I try any date ie: Jan 15 - Jan 20 with all my echos above I get this:

SELECT * FROM de_featuredevent WHERE '1200373200' OR '1200805200' BETWEEN '1199250000' AND '1199509200'=time mismatch
SELECT * FROM de_featuredevent WHERE '1200373200' OR '1200805200' BETWEEN '1199941200' AND '1200373200'=time mismatch
not adding 

 

So my issue is with my $sql...I have to be doing something wrong here. My logic isnt right or something.