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.

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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.

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)

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

0

Pat ShorttManagerAuthor Commented: 2015-05-05

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?

0

Pat ShorttManagerAuthor Commented: 2015-05-05

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.

Where do you want to display it and when?
So a person renews their membership for a year, you update enddate?
All you actually need to display days remaining is the end date of the membership. And only when
Datediff (enddate,now()) is of the values you want
The startdate and enddate reflects the days in the membership period.

You can make this part of a response to a login ip event,
Select member_name, enddate ,datediff(enddate,now()) as daysremaining from members where members=$memberid
As a php query, the third column will be the days remaining based on the current date and when the membership expires. You can then within php evaluate what you want to do with this information and how you want to display it.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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.

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.