Date Function: Calculating Days...

[complex05]

Hello,

I have a table for visitor parking passes. There are three columns: Start (date) Finish (date) Username (text)

Passes can expire in either 1, 2 or 3 day intervals

Now the problem I'm having is a user can only have a maximum of 12 days per month. My current query is this:

//$count_reserves = mysql_query("SELECT id FROM visitorparking_slotdata WHERE username='$thisuser' AND DATE_FORMAT(start,'%m')='$month'");
//$count_reserves = mysql_num_rows($count_reserves);

if ($count_reserves >= $maxreserves)
{
echo<<<endhtml
You have exceeded the maximum amount of reservations ($maxreserves) by an owner for this month. Please contact management for more information.
endhtml;
}

This doesn't work because it's giving me 12 passes instead of 12 days. I cannot use the MySQL function DateDiff because my MySQL version doesn't support it. I'm sure I have to do it in PHP using mktime but I don't know how to get a 'number of days' value.

Any help is much appreciated!

Thanks,

Barry

[ronverdonk]

There is a date/time class (Simple Data Time Object) at phpclasses.org that can achieve what you are looking for.

Look at [url=http://www.phpclasses.org/browse/package/1952.html]http://www.phpclasses.org/browse/package/1952.html[/url]

Ronald  8)

[Corona4456]

So does the maximum # of reservations include the number of days the passes are for?

i.e. a 3 day pass = 3 total reservations?

[craygo]

Can try this

[code]$total = 0;
$sql = "SELECT * FROM visitorparking_slotdata WHERE username='$thisuser' AND DATE_FORMAT(start,'%m')='$month'";
  $res = mysql_query($sql) or die (mysql_error());
    while($r=mysql_fetch_array($res)){
    $sday = date("z", strtotime($r['start]));
    $eday = date("z", strtotime($r['finish']));
    $diff = $eday - $sday +1;
    echo "$diff<br>";
    $total += $diff;
        }
echo $total;[/code]

Ray

[complex05]

ah good ol strtotime i didn't even think of that... let me give it a shot, thanks!

[Barand]

[code]SELECT Username, SUM(TO_DAYS(Finish) - TO_DAYS(start) + 1) as days
FROM visitorparking_slotdata
GROUP BY Username
HAVING SUM(TO_DAYS(Finish) - TO_DAYS(start) + 1) > 12[/code]

[complex05]

Thank you, great query!

[complex05]

Now we are having a different problem, how do I modify the query so it will only look for how many reservations are in the current month?

[Barand]

How do you define "in current month"?
If it must be entirely in the current month then
[code]
SELECT Username, SUM(TO_DAYS(Finish) - TO_DAYS(start) + 1) as days
FROM visitorparking_slotdata
WHERE MONTH(Finish) = '8' AND MONTH(Start) = '8'
GROUP BY Username
HAVING SUM(TO_DAYS(Finish) - TO_DAYS(start) + 1) > 12[/code]

If it started last month and finishes this month, or starts this month and finishes next, then it gets a bit more complex

[complex05]

That's fine, if there are exceptions where the expiry runs into the next month, it's no big deal

thanks a lot for your help!