Link to home
Start Free TrialLog in
Avatar of Pat Shortt
Pat Shortt

asked on

Calculate days remaining between 2 Dates

Table is called members. I have two date fields startdate and enddate. I have another integer field called daysremaining. I want to calculate the days remaining between the 2 dates and have the result put into daysremaining.

I tried

function members_after_insert($data, $memberInfo, &$args){
$daysremaining = (strtotime($data['startdate'])- strtotime($data['enddate']))/86400;
      return TRUE;

Open in new window


But that does not work.
Avatar of gplana
gplana
Flag of Spain image

You can solve this just executing this UPDATE:

UPDATE members
    SET daysremaining = DATEDIFF(enddate,startdate);

Open in new window

Hope it helps. Regards.
You should add a where daysremaining is null
To avoid the update reapplying and running on all rows when unnecessary.
Ok, that's a good idea, then run:

UPDATE members
    SET daysremaining = DATEDIFF(enddate,startdate)
   WHERE daysremaining IS NULL;

Open in new window


but only if you don't want to recalculate all rows. If you want to make the calculation for the current date values of every row, run my previous version.

Regards.
One more thing to add to the where  to test whether both columns are not null or are appropriate date/time fields.
Avatar of Pat Shortt
Pat Shortt

ASKER

I am not going to get to try this for a few days but will the above work for only current records or for all future entries. I need it to work for new entries also.
Not sure what you mean or what you need.
The update will only be valid when run, you could setup a trigger on insert/on update to calculate the entry for just this row. i.e. when the record is inserted, or updated, the trigger will fire and making the calculation.

What are you using to collect/insert the entries, you could make it part of the insert/uptdate?
i.e. daysremaining column will have the function with the data as input.
Yes its a trigger is what i need. How can I set a trigger to do this?
It might be easier to add this to the interface you are using to collect the information php. Etc.


Which version of MySQL are you running,
Select @@version;

An example for trigger creation for 5.1 though......

http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

Is the end date often added as an update or is all the information is present during the insert?
If the end date is always added as an update, you would need only create a trigger for updates.
I think you need is something like this:
CREATE TRIGGER trigCalculateDays
AFTER INSERT ON members
FOR EACH ROW SET NEW.daysremaining = DATEDIFF(enddate,startdate);

Open in new window


This will calculate the value for every new inserted row.

And also create this trigger:
CREATE TRIGGER trigCalculateDays
AFTER UPDATE ON members
FOR EACH ROW SET NEW.daysremaining = DATEDIFF(enddate,startdate);

Open in new window


so that when you make an update the daysremaining value will be recalculated.

Don't forget to also execute my previous UPDATE in order to update the already inserted data.

Hope it helps. Regards.
When I run
CREATE TRIGGER trigCalculateDays
AFTER INSERT ON members
FOR EACH ROW SET NEW.daysremaining = DATEDIFF(enddate,startdate);

Open in new window


I get

#1362 - Updating of NEW row is not allowed in after trigger

Open in new window


I also tried a before insert. This didn't give an error in phpmyadmin but all I get is a blank screen when submitting.
You can not do a before, on insert, because the row is not there.


Why not do this in the interface you use to add the entries?  What are you using to insert/update these rows?

You have many ways to enforce what you want, I am just puzzled why you are trying to push this mechanism to the sql side where other more direct options exist.
After some research the php application allows me to use hooks and I was told this is a better way to achieve what I want rather than using sql triggers. I need to create 3 hooks.

1, for the newly inserted record to be updated
2.  for updated records to be updated
3. for all records.

I have 1 and 2 working but 3 I cannot get working. the daysremaining field is suppose to count down each day. Below is how I got it working. In the members.php file I added.

function updatedaysremaining($where = ''){ 
$sql = "update `members` set `daysremaining` = round(datediff(`enddate`,'".date('Y-m-d')."') )"; 
if($where) $sql .= "where $where"; 
sql($sql, $eo); 
} 

Open in new window

To get newly inserted records working I also added into members.php
function members_after_insert($data, $memberInfo, &$args){
       updatedaysremaining("ID='{$data['selectedID']}'"); 
		return TRUE;
	}

Open in new window

To update records I added to members.php
function members_after_update($data, $memberInfo, &$args){
       updatedaysremaining("ID='{$data['selectedID']}'"); 
		return TRUE;
	}

Open in new window


Next part is where I am stuck. I have tested above and it works but I know the same numbers are going to be in daysremaining field tommorrow as is there today. They are only calculating from the day of insert.

I tried adding the following to test ( I wont be leaving it as seconds this is just for testing)
function members_header($contentType, $memberInfo, &$args){
		$header='';
updatedaysremaining("SECOND(startdate)=" . time('s')); 
		return $header;
	}

Open in new window

startdate is setup as a timestamp field
A hook is often to update a second/another table/record than the one you are currently updating/inserting.

What is the php application that you are using, did you create it or are you using an open source vehicle to handle your own.

Let me ask you this:
When this application inserts a new record do both start and end dates present? If not, the only check is on the update that you run for the enddate at which point you can include the parameter in the entry where you have update member
update members set enddate='$enddate', daysremaining=datediff('$enddate',startdate) where memberid=$memberid
or something to that effect.

If you want to simply trigger an update on all the table
update members set daysremainin=datediff(enddate,startdate) where startdate is not null and enddate is not null and daysremaining is null

you can use the hook as you outlined. on each update/insert the
A hook is often to update a second/another table/record than the one you are currently updating/inserting.
Yes this is what I am doing. I have a startdate and enddate field. The calculation between these two fields are inserted into the daysremaining field
What is the php application that you are using, did you create it or are you using an open source vehicle to handle your own.
It is a custom built script for Membership system. I have no contact with guy who did it so I am trying to figure it out to make some changes to it.
When this application inserts a new record do both start and end dates present? If not, the only check is on the update that you run for the enddate at which point you can include the parameter in the entry where you have update member
Yes I enter in the start date and end date.
update members set enddate='$enddate', daysremaining=datediff('$enddate',startdate) where memberid=$memberid
or something to that effect.

If you want to simply trigger an update on all the table
update members set daysremainin=datediff(enddate,startdate) where startdate is not null and enddate is not null and daysremaining is null

you can use the hook as you outlined. on each update/insert the

I am confused here. Do I enter above in phpmyadmin or with a hook like the others.
A hook is to update a separate Table not a field within the same table that you are updating.

i.e. members when update starttime/endtime you want member_time_remaining table to reflect the current number of days remaining.

This is where you would use a hook.

If you are simply looking to show the user that they only have X days remaining, you can display this in the browser.


Unfortunately, your question was answered as asked.

What is it you are trying to accomplish?

What is the end goal?

I think the question you posed is not clear.
Do you want to have the ability to display to the user or are you looking at a report where you want the user's daysremaining data reflected?

I.e. user joins, selects the duration of their membership. You want to use this data to notify the user of their upcoming expiration so that they will renew? You want this information displayed in the browser when daysremaining is less than sixty days?
You want an email notification when daysremaining is equal to 60,30,15 and 5 days?
user joins, selects the duration of their membership. You want to use this data to notify the user of their upcoming expiration so that they will renew? You want this information displayed in the browser when daysremaining is less than sixty days?
You want an email notification when daysremaining is equal to 60,30,15 and 5 days?

Yes this is what I want to achieve only the daysremaining will display all days remaining.
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think creating a trigger and then make an UPDATE also will be the correct solution for this. A hook is also a possibility, however I think we provided a good solution (and also I think it's better, because it will guarantee data consistency, not only when using the php app, but also when you change values manually on the database or from another application.

Hope it helps. Regards.
I think the user is looking to display membership days remaining to the user, there is no need to do anything on the database side as far as an additional column in a table, trigger or anything of that nature.  All the user has to do is to add an additional parameter to the query they use to authenticate if the data is within the same table  that will do a datediff between the date when the membership expires and now which I think is a requirement to limit a user whos membership expired and is trying to login.
Or the user has to run a separate process on the DB to deactivate an account when the membership expires.

A display for member since is enough to illustrate that the member is a long standing member.  No reason to calculate the number of days the user has been a member which is what the question originally sought.
I believe the user question is using the duration of the membership which is membership end date minus current membership end date.
The user confirmed in the post http:#a40760929

The query is there that answers the confirmed functionality the user wants in http:#a40761175
which says to use the datediff(enddate,now()) and use this info to reflect days remaining for the membership.