Calculate the No. of days from Dates NOT in the same row

[mark2326l]

I'm trying to calculate the number of days a particular property is vacant.
From this query...

SELECT Properties.PropId, Tenants.TenantId, Tenants.PropertyID, Tenants.TenantAdress, Tenants.MoveIn, Tenants.MoveOut, Properties.P_GpNo FROM Properties, Tenants WHERE Tenants.PropertyID=Properties.PropId AND Properties.P_GpNo ="3" AND TenantAdress = "5"
ORDER BY Tenants.TenantId

It returns..

               MoveIn    MoveOut
5   4   005 5   2012-12-28  2013-04-13  3
5   20  005 5   2013-05-01  2014-09-30  3
5   41  005 5   2014-10-03  NULL    3

I need to find the # of days between 2013-04-13 and 2013-05-01, and again between 2014-09-30 and 2014-10-03

Any Ideas?
Thank you

[milil]

This will return all dates that your propertie had people in it.
So you can do it like this:
Make a metod that will get input parameters fromDate and endDate.
You grab from database every dates you have.
Than use a for or while loop to go over all data you get.
Use a fromDate and see is it smaller then MoveIn date in frst row:
if yes then calculate how many days you have fromDate to MoveIn because it was empty
if not then use a MoveOut and save it to field with one flag

then use a second row and if flag for MoveOut is UP then you calculate how many days has passed from Move Out from first row to MoveIn from second and add it to sum off all days,
then use that for all rest rows if it satisfy condition that is endDate bigger then MoveIn or MoveOut.

If you have problems to understand what I mean with this. Ask me on PM to explain you better, but this can be one algorithm...

Regards, Mike.

[Traevel]

You can use the DateTime::diff() function to get a DateInterval object which contains the time difference in properties.

For example:

$datetime1 = new DateTime('2009-10-11');
$datetime2 = new DateTime('2009-10-13');
$interval = $datetime1->diff($datetime2);

would get you a difference object in $interval. In order to get the days you would call the intervals format function. Something like $interval->format('%d days'); to get 2 days as a result.

For the second part of getting the begin and end dates you could do something along the lines of

$arr[0] = array("2012-12-28","2013-04-13");
$arr[1] = array("2013-05-01","2014-09-30");
$arr[2] = array("2014-10-03",NULL);

$it = 0;
while($it<count($arr)){

    // if neither begin and end are NULL 
    if($arr[$it][1]!= null & $arr[$it+1][0]!=null){

        $datetime1 = new DateTime($arr[$it][1]);
        $datetime2 = new DateTime($arr[$it+1][0]);

        $diff = $datetime1->diff($datetime2);

        echo "Difference between ".$datetime1->format("Y-m-d")." and ".$datetime2->format("Y-m-d")." = " . $diff->format("%d days") . "<br/>";

    }
    $it++;
}

which will print

//Difference between 2013-04-13 and 2013-05-01 = 18 days
//Difference between 2014-09-30 and 2014-10-03 = 3 days

Note that for edge cases (i.e. begin or end is NULL or end lies before begin) the difference will not be calculated correctly so you'll want to alter this snippet to better reflect that. For instance if the end date is NULL you could substitute that with today's date.

[mark2326l]

Traevel

Your idea works well with the data intered into the array, but trying to get the data from mySql is a hang up for me.
I've tried...

$q_RS_Vac = "SELECT Properties.PropId, Tenants.TenantId, Tenants.PropertyID, Tenants.TenantAdress, Tenants.MoveIn, Tenants.MoveOut, Properties.P_GpNo FROM Properties, Tenants WHERE Tenants.PropertyID=Properties.PropId AND Properties.P_GpNo = '3' ";
$result = mysql_query($q_RS_Vac) or die ("no query");

$result_array = array();
while($arr = mysql_fetch_assoc($result))
{
    $result_array[] = $arr;
print_r($arr);
Echo "<br />";
}

I can see the resuls with print_r, but not sure where to edit your code to deal with it

[Traevel]

Well from the query results you posted it seemed like you only needed the MoveIn and MoveOut date, what you are doing now is inserting the entire row including all other values (which could also work).

If you just want the dates stored you could do this:

while($row = mysqli_fetch_array($result)){
    $values = array($row['MoveIn'],$row['MoveOut']);
    array_push($result_array,$values);
}

Then to retrieve both dates you'd have to loop over all results and for instance use $result_array[0][0] to get MoveIn and $result_array[0][1] to get MoveOut. (of course in the loop you'd substitute the first 0 with an iterator variable like $i)

If you want to store all values from your query (for instance if you need tennant number later on) you could do

while($row = mysqli_fetch_array($result)){
    array_push($result_array,$row);
}

Then you'd do the loop, same as option one, but retrieve the date values like $result_array[0]['MoveIn'] and $result_array[0]['MoveOut']. (same as before, in the loop first 0 becomes an iterator variable)

The $arr in my first example is now the $result_array in this example.

On a sidenote, it's common practice to either go with mysqli or PDO instead of the old mysql API to connect to your database.

[mark2326l]

how would I get a total of the of the days.
I thought I could use array_sum(), but that is not working.