Date Alerts

[ayesha789]

Hi, I am developing a software. I need to show only those subscriber, their subscription will expire after 90 days and less then 90 days. and when it show these subscriber it saves information in another table automatically.

For Example: if Subscriber have expiry date 15-decemeber-2009, it shows alerts that 1 subscriber will be expire within 90 days. and when I press detailed it will show all the info of that subscriber.

Thanks in advance

Your db field should have the date in unix format, i.e. yyyy-mm-dd.

You could try something like this:

SELECT * FROM mytable WHERE expiry_date - INTERVAL 90 DAY < current_date()

Sorry - this is off the top of my head. You definietely can do something like this, don't know if I've got the syntax totally right.

[ayesha789]

why we use -

[ayesha789]

$result = mysql_query("SELECT * FROM mytable WHERE ((end_date - INTERVAL 90 DAY) <= current_date())");

but it shows Aug,Apr, Mar months but I want if expiry date is 15 December , it shows alerts 90 days before the subscription expires.

My rationale was that:
1. the expiry date is in the future. (e.g. 2009-10-10)
2. calculate 90 days before that to see if within the reminder period (so expiry date - 90 -> roughly 2009-07-10)
3. Compare this earlier date (let's call it cDate) with today. If cDate is less than or equal to today, i.e. earlier than today (< or more correctly, <= current_date()), place it in the recordset.

Like I said, it came from the top of my head and I hadn't tested it. I don't think it's far off though.

If you want to test who's expiring 90 days from a certain date, then change current_date() to the date you want to check.

[ayesha789]

Thanks Ardav.
I have a new Idea.
1. expiry date is 2009-10-10
2. I calculate 3 months before using
expiry date - interval 3 months as beforedate
it shows 2009-07-10 (I want to add beforedate in Database , how its possuble?) please guide me.
3. then I will use query select * from table where beforedate month is july and year is 2009.( please guide me about this query)
Now which I select in July is alert about october expiry.
So I need to write queries for 12 months and have to show in 12 divisions.

[ayesha789]

please help me
Thanks

please help me
Thanks

OK, but I don't think you need the beforedate - it's basically replicating data - "expiry - 3 months". These "hard" calculated fields shouldn't really be in a db, they should be calculated on the fly in a query.

If creating a new record (e.g. link table):

INSERT INTO mytable SET user_id = '{$user_id}', expiry = '{$thedate}', beforedate = '{$thedate}' - INTERVAL 3 MONTH

You just compare the beforedate to the current date to see if expiry is less than 3 months:

SELECT * FROM mytable WHERE beforedate <= current_date()

If you want to check accounts in danger of expiring on a certain date (e.g. today or tomorrow):

SELECT * FROM mytable WHERE beforedate <= '{$checkdate}'

where $checkdate is a yyyy-mm-dd date - use a form/$_POST variable for this check.

I would avoid writing 12 'hard' queries - make one dynamic one from a form:

<form ...>

  <label for="checkdate">Date to check:</label>
  <input type="text" id="checkdate" name="checkdate" value = "<?php echo date('Y-m-d');?>" />
  <input type="submit" ... />

</form>

Then in form handler, clean and validate the date, call it $checkdate. Then:

$r = mysql_query("SELECT * FROM mytable WHERE beforedate <= '{$checkdate}'" ORDER BY expiry);

$out = "<table><thead><tr><th>ID</th><th>EXPIRES</th></tr></thead><tbody>"
while($d = mysql_fetch_array($r)){
  $out .= "<tr><td>{customer_id}</td><td>{$expiry}</td></tr>";
}

$out .="</tbody></table>";


...(more code)...


echo $out;

[ayesha789]

Thats Great. But I am still confused. I will share my code and Db with u soon.

[ayesha789]

i do not understand $checkdate

i do not understand $checkdate

where $checkdate is a yyyy-mm-dd date - use a form/$_POST variable for this check.

I would avoid writing 12 'hard' queries - make one dynamic one from a form:

<form ...>
 
  <label for="checkdate">Date to check:</label>
  <input type="text" id="checkdate" name="checkdate" value = "<?php echo date('Y-m-d');?>" />
  <input type="submit" ... />
 
</form>

Then in form handler, clean and validate the date, call it $checkdate.

$checkdate is just a convenient date which you place into a form to see who is within the three month expiry zone (from that date).

It has a default value of today - date('Y-m-d'), but you can replace that date with any unix format short date (e.g. 2009-10-24).

Your form will be sent to another php file (the form handler), which will handle the $_POST values from that form. You just check the $_POST variable against the db.

Any records with beforedate values that are equal to or less than this are within the "danger of expiry zone" (i.e. within 3 months). However, you will also return records where the account/membership/whatever has expired.

To discount the expired records you need to change the SQL. HOWEVER, I think this is all unnecessary as just the expiry field should be sufficient - no need for beforedate field at all.

Personally, I'd have another table, call it config, where there would be the following fields:

id (autonumber/PK)
key
value

I'd add a record: key = 'expiry_period', value = '3 MONTH'

You then have an easy way of controlling the expiry period without having to hard code it. The value should be based upon the 'INTERVAL' keyword, where you can use 'x MONTH', 'x DAY' etc, where x is a number.

[ayesha789]

u provide me many solutions but i am still confused.
it shows all the dates of year 2009,2010 ans so on.

[ayesha789]

please check my problem.
I have a DB in which there is an ID and Expiry date
for example
id Expiry Date
1 20 DEC 2009
2 27 DEC 2009
3 25 DEC 2009

I need alert today of those which are expiring within 3 months from today. like ID 1 is expiring with in 3 moths and id 3 is expiring exactly in 3 months. but id 2 is after 3 months so i do not want alert for id 2.
means alert for those which are expirin within 3 months or exactly in 3 months.

please guide me through this example.
and i have more then 4000 ids in my DB so please consider year , month. please guide me.
thanks in advance....