Procedure Confirmed with Comparing Dates

:) too true. got it outta there.

i'm sure i'll have to re-write it the comparison statement as the accounts
we need to track become more diverse, but the real gem in here (to me) was
the SQL date manipulation. Getting that puppy solved was very satisfying
indeed (you can see how long this little project has been wanting to get
attention).

Thanks again man, your help was invaluable.

Jon

"Joe Makowiec" <makowiec@invalid.invalid> wrote in message
news:Xns98519A4E66659makowiecatnycapdotrE@216.104.212.96...
> On 03 Oct 2006 in macromedia.dreamweaver.appdev, crash wrote:
>
> Glad it worked.
>
>> function checkValidity($a, $b){
>> if($a <= $b){
>> $status='EXPIRED';
>> }elseif($a > $b){
>> $status='PAID';
>> }
>> return $status;
>> }
>
> You don't need the elseif; if it gets there, you already know that $a >
> $b. So you can do something more like:
>
> function checkValidity($a, $b){
> $status='EXPIRED'; // Assume expired
> if($a > $b){ // Unless expiry date > today
> $status='PAID';
> }
> return $status;
> }
>
> --
> Joe Makowiec
> http://makowiec.net/
> Email: http://makowiec.net/email.php

On 03 Oct 2006 in macromedia.dreamweaver.appdev, crash wrote:

Glad it worked.

> function checkValidity($a, $b){
> if($a <= $b){
> $status='EXPIRED';
> }elseif($a > $b){
> $status='PAID';
> }
> return $status;
> }

You don't need the elseif; if it gets there, you already know that $a >
$b. So you can do something more like:

function checkValidity($a, $b){
$status='EXPIRED'; // Assume expired
if($a > $b){ // Unless expiry date > today
$status='PAID';
}
return $status;
}

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/email.php

Joe-

I used a derivative of this that worked perfectly:

SELECT cID, cPrimaryDomain, cUsername, cClass, cClientName, cSetupDate,
UNIX_TIMESTAMP(DATE_ADD(cRenewDate, INTERVAL 12 MONTH)) AS cRenewDate,
cPackageClass, cTerm, cPriceAdjustment FROM tblclient WHERE cClass =
'".$pageClass."' ORDER BY cClientName ASC";

and then:

function checkValidity($a, $b){
if($a <= $b){
$status='EXPIRED';
}elseif($a > $b){
$status='PAID';
}
return $status;
}

I'm not messing with terms, I don't think, as we have only year-contracts
that people are using (for the forseeable future).

Thanks for all your help!

"Joe Makowiec" <makowiec@invalid.invalid> wrote in message
news:Xns9823890AE6646makowiecatnycapdotrE@216.104.212.96...
> On 18 Aug 2006 in macromedia.dreamweaver.appdev, crash wrote:
>
>> I was just looking at doing it with SQL, but here is my
>> question/problem:
>>
>> I am doing this for a client database of hosting customers - a
>> one-stop list so we can see who's due. I'm worried about having
>> two sql queries per customer.
>
> Why would you need two SQL queries per customer? David's method gives
> you registered date and expiry date. If you wanted, you could also add
> an expired field to the query:
>
> SELECT registered, DATE_ADD(registered, INTERVAL 12 MONTH) AS expiry,
> expiry > NOW( ) AS expired
> FROM myTable
>
> expired will return a 0 or 1 depending on whether expiry > now().
>
> --
> Joe Makowiec
> http://makowiec.net/
> Email: http://makowiec.net/email.php

How's this look guys?

PERIOD_DIFF(P1,P2)

Returns the number of months between periods P1 and P2. P1 and P2 should be
in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are
not date values.

mysql> SELECT PERIOD_DIFF(9802,199703);
-> 11
:O)"crash" <crash@bcdcdigital.com> wrote in message
news:ec4o6r$jpa$1@forums.macromedia.com...
> Trying to compare two dates, and there's so many ways I'm just getting a
> bit confused.
>
> I have a date stored in my database in a timestamp field with this format:
> 2006-03-05 19:13:05
>
> I have a term defined in months (in the database) of how long the account
> is valid:
> term: 12
>
> I can now almost get the expired date, with this code, where $vDate is the
> untouched date from my database, and $term is the term in months.
> $blog2 = date("M jS Y",strtotime($vDate,'+'.$term.' Month'));
>
> But it only increments the months, not the years.
>
> Now, here's my problem:
>
> I can create an array with expired and current dates, but then I have to
> construct a moderately complex if/elseif statement to check year, then
> month, then day.
>
> Is there a better way? IE, can anybody think of a method in which I can
> compare the dates more efficiently? I'm going in circles. Everytime I
> think I've almost got it, I lose something.
>
> Any suggestions?
>
> TIA,
>
> Jon
>

hehehe, the sql part i mean. ;o)

;o) I have little doubt about that. My specs also got re-wrote while I was
in the midst of this, so I have to rework the page again today. I'll
probably attempt to get it working.

Jon

> Glad you got it working, but there must have been something wrong with the
> way you tried to use UNIX_TIMESTAMP(). It works just fine, and should be
> supported as far back as MySQL 3.23.
>
> --
> David Powers

crash wrote:
> This worked, thank you *so* much. The UNIX_Timestamp did NOT work, but I
> just converted the file reference inside the document and it works fine.

Glad you got it working, but there must have been something wrong with
the way you tried to use UNIX_TIMESTAMP(). It works just fine, and
should be supported as far back as MySQL 3.23.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

David-

This worked, thank you *so* much. The UNIX_Timestamp did NOT work, but I
just converted the file reference inside the document and it works fine.

Thanks so much. I wish I could have gotten the SQL to work, but for a Friday
I'm happy enough with the results.

Thanks again,

Jon
"crash" <crash@bcdcdigital.com> wrote in message
news:ec4uuh$s2j$1@forums.macromedia.com...
> thanks david, going to test this and the sql query joe mentioned and see
> what i can get worked out.
>
> iknow it's a lot of code. :( dates are hard for me, i have to write
> everything out almost 'plain text' so i can follow what i'm doing.
>
> "David Powers" <david@example.com> wrote in message
> news:ec4ucc$r4j$1@forums.macromedia.com...
>> crash wrote:
>>> I'm worried about having two sql queries per customer.
>>
>> Might be.
>>
>>> I'm doing it as such now (my previous method was not working):
>>
>> What a lot of code!
>>
>> Use the MySQL UNIX_TIMESTAMP() function to get the registered date in a
>> format that PHP understands:
>>
>> SELECT UNIX_TIMESTAMP(registered) AS registered, term
>> FROM myTable
>>
>> In your PHP file create a variable called $now to store the current
>> timestamp:
>>
>> $now = time();
>>
>> Then, assuming that $registered contains the timestamp retrieved from the
>> database and $term contains the number of months paid for, the following
>> will display the current status:
>>
>> echo checkValidity($registered, $term, $now);
>>
>> Here is the definition of checkValidity():
>>
>> function checkValidity($start, $term, $now) {
>> $expired = strtotime("+{$term} months", $start);
>> return $now > $expired ? 'Expired' : 'Paid';
>> }
>>
>> That should save a lot of typing and work more quickly.
>>
>> --
>> David Powers
>> Adobe Community Expert
>> Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
>> http://foundationphp.com/
>
>

thanks david, going to test this and the sql query joe mentioned and see
what i can get worked out.

iknow it's a lot of code. :( dates are hard for me, i have to write
everything out almost 'plain text' so i can follow what i'm doing.

"David Powers" <david@example.com> wrote in message
news:ec4ucc$r4j$1@forums.macromedia.com...
> crash wrote:
>> I'm worried about having two sql queries per customer.
>
> Might be.
>
>> I'm doing it as such now (my previous method was not working):
>
> What a lot of code!
>
> Use the MySQL UNIX_TIMESTAMP() function to get the registered date in a
> format that PHP understands:
>
> SELECT UNIX_TIMESTAMP(registered) AS registered, term
> FROM myTable
>
> In your PHP file create a variable called $now to store the current
> timestamp:
>
> $now = time();
>
> Then, assuming that $registered contains the timestamp retrieved from the
> database and $term contains the number of months paid for, the following
> will display the current status:
>
> echo checkValidity($registered, $term, $now);
>
> Here is the definition of checkValidity():
>
> function checkValidity($start, $term, $now) {
> $expired = strtotime("+{$term} months", $start);
> return $now > $expired ? 'Expired' : 'Paid';
> }
>
> That should save a lot of typing and work more quickly.
>
> --
> David Powers
> Adobe Community Expert
> Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
> http://foundationphp.com/

cool, checking on that now. will post back after some testing

thanks for all the help. i can't believe this could be done in sql. sheesh.

THANKS!

Post in a bit

"Joe Makowiec" <makowiec@invalid.invalid> wrote in message
news:Xns98238CEB33AmakowiecatnycapdotrE@216.104.212.96...
> On 18 Aug 2006 in macromedia.dreamweaver.appdev, crash wrote:
>
>> My term is defined in the database (some clients have 6 months, some
>> 12), and I believe David said that to get the query to run
>> correctly, I would need to first query the field (to get the terms)
>> and then use his query to get the expired material.
>
> You can do that, too:
>
> SELECT registered , DATE_ADD(registered, INTERVAL howMany MONTH) AS
> expiry
> FROM myTable
>
> where `howMany` is the field from the table which contains the number of
> months for which the client signed up.
>
> --
> Joe Makowiec
> http://makowiec.net/
> Email: http://makowiec.net/email.php