searching all dates within a range specified

blackwidow

hi guys,

I have a calendar script that inputs the data into a database with a start date and an end date.

I'm trying to develop script that will search through the all the dates between the start date and the end date, and return true/false accordingly. (this is a basic 'check availability' script).

eg: if the start date is 01/04/2008, and the end date is 01/05/2008, and the user searches for 15/04/08, then the script needs to search through all dates between the start date and the end date, and if one of them matches return 'not available' etc.

here is what I have so far (thats not working!):

$searched_for = "2008-03-06";
$start = "2008-03-01";
$end = "2008-03-15";

$init_date = strtotime($start);
$dst_date = strtotime($end);
$r = strtotime($searched_for);

$offset = $dst_date-$init_date;

$dates = floor($offset/60/60/24) + 1;

for ($i = 0; $i < $dates; $i++)
{
$newdate = date("Y-m-d", mktime(12,0,0,date("m", strtotime($start)),
(date("d", strtotime($start)) + $i), date("Y", strtotime($start))));
echo $newdate ."<br>";
}
if ($r == $newdate) {echo "the date you searched for is within the date range"; } else { echo "the date you searched for is outside the date range"; }

any help greatly appreciated!!

Mark

cahva

I didnt test your script but I would use the newer & better datetime funtions to achieve this(PHP 5 only). Heres an primitive example but you'll get the idea:

$start = new DateTime("2008-03-01");
$end = new DateTime("2008-03-15");

$searched_for = new DateTime("2008-03-06");

while ($start<=$end) {
    $curday = $start->format("Y-m-d");
    if ($start==$searched_for)
        echo $curday.": Yep</br>";
    else
        echo $curday.": No</br>";

$start->modify("+1 day");
}

blackwidow

Hi Cahva, many thanks for your reply. I'll definetly be using the new date functions in the script from now on.

One thing I dont understand is that your code produces a 'Yep' after the date that was searched for. heres a better expalnation of what I'm looking for:

If I go on holiday on the 01/03/08($start), and return on the 15/03/08($end), I'm not available any where between those dates, so if the client searches for 06/03/08($searched_for) he/she should be given a 'Not available' response.

hope this helps a little.

cheers

mark

cahva

Ok. I thought you are printing out every day in the calendar. If I undestood you correctly, it is easy as this(replace the while statement):

if ($searched_for>=$start && $searched_for<=$end) {
    echo 'Im not available at '.$searched_for->format("Y-m-d");
} else {
    echo 'Im available at '.$searched_for->format("Y-m-d");
}

Is this what you were looking for?

blackwidow

Oooh, that worked a treat! awsome, many thanks for that. I should have explained it better though, apologies for that.

Now comes the tricky bit...I have a field in the database called 'available' it is either 'yes', 'no', or empty.

I'd like to search as before but only return 'I'm available' if 'available' is 'yes'. heres what I've got so far but its not working right:

<?
### results that will eventually be posted from a form ###

$day = "30"; 
$month = "3"; 
$year = "2008"; 



### grab data from the databse based on form variables ###

$sql=@mysql_query("SELECT * FROM calendar WHERE startday='$day' AND startmonth='$month' AND startyear='$year'") or die ('Error: '.mysql_error ());
if (!$sql) {
echo "Unable to perform search";
}

while ($result=mysql_fetch_array($sql)) {

$sd = $result['startday'];
$sm = $result['startmonth'];
$sy = $result['startyear'];
$ed = $result['endday'];
$em = $result['endmonth'];
$ey = $result['endyear'];
$available = $result['available'];

$start = new DateTime("$sy-$sm-$sd");
$end = new DateTime("$ey-$em-$ed");

}


if ($available = "yes" && $available = "no") {$available = "no";} // if there is more than one record, and one of the 'available' results is 'no' then we return false
if ($available = "yes" || $available = "") {$available = "yes";} // if the record is 'available - yes' or there is no record then we return true

$searched_for = new DateTime("$year-$month-$day");

if ($searched_for>=$start && $searched_for<=$end && $available == "no") { ?>
    <div align="left">I'm not available on <? echo $searched_for->format("Y-m-d") ?> - <? echo $available ?></div>

<? } else { ?>

   <div align="left">I'm available on <? echo $searched_for->format("Y-m-d") ?> - <? echo $available ?></div>

<? } ?>

Where I'm echoing $available in the results bit is just for me to see if its working.

I appreciate all your help with this, you're a lifesaver!

cheers

Mark

cahva

Hmm.. Is there a reason why you have put day, month and year separately in the database? If it was a normal date or timestamp field, you could do all checking directly in the query. Mysql has powerfull date and time functions which you can use(and which you should use).

If I were you, I would redesign the table and use just "startdate" and "enddate". Then you could easily do the check in the query for example:

$search_for = "$year-$month-$day";
$sql = "SELECT startdate,enddate,available FROM calendar WHERE '{$search_for}' >= startdate AND '{$search_for}' <= enddate";

This way, if you get some rows, you just check the available row. If it doesnt return any rows, its available.(I dont quite see the meaning for available in the table but maybe you have your reasons 😉 )

blackwidow

You have some very valid ideas there. The original calendar script is one which I purchased (hence the database entries for the date). Its a good script, does exactly what I want it to do etc. The 'available' field in the table was added by me so that I could use the calendar for both business and personal use (i.e. if I had a day where an invoice was due to be paid I could enter it into the calendar but still be available for work on that day.

If its going to be more troublesome than its worth then I'll knock that idea on the head and use your code for the checking of the dates etc, and just use the calendar for inputting actual days where I wont be available for work. The last thing I want to do is waste your time!

cheers

mark

derekcpt

Hi Blackwidow.

I'm sitting with the EXACT coding to crack. I already cracked my brains, so I will appreciate it if you could assist me in this one. I need achieve the exact same functions, so could you assit me here? Please.