Jump to content


Photo

Date Function: Calculating Days...


  • Please log in to reply
9 replies to this topic

#1 complex05

complex05
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 23 August 2006 - 05:54 PM

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


#2 ronverdonk

ronverdonk
  • Members
  • PipPipPip
  • Advanced Member
  • 277 posts
  • LocationNetherlands

Posted 23 August 2006 - 06:03 PM

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

Look at http://www.phpclasse...ckage/1952.html

Ronald  8)
RTFM is an almost extinct art form, it should be subsidized.

#3 Corona4456

Corona4456
  • Members
  • PipPipPip
  • Advanced Member
  • 244 posts
  • LocationEl Paso, TX

Posted 23 August 2006 - 06:05 PM

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

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


What is the CoronaMatrix?

#4 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 23 August 2006 - 06:14 PM

Can try this

$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;

Ray

#5 complex05

complex05
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 23 August 2006 - 06:37 PM

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

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 23 August 2006 - 07:48 PM

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 complex05

complex05
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 24 August 2006 - 03:31 PM

Thank you, great query!

#8 complex05

complex05
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 24 August 2006 - 07:50 PM

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?

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 24 August 2006 - 08:10 PM

How do you define "in current month"?
If it must be entirely in the current month then
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

If it started last month and finishes this month, or starts this month and finishes next, then it gets a bit more complex
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 complex05

complex05
  • Members
  • PipPipPip
  • Advanced Member
  • 124 posts

Posted 24 August 2006 - 08:24 PM

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!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users