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
But that does not work.
I tried
function members_after_insert($data, $memberInfo, &$args){
$daysremaining = (strtotime($data['startdate'])- strtotime($data['enddate']))/86400;
return TRUE;
But that does not work.
You should add a where daysremaining is null
To avoid the update reapplying and running on all rows when unnecessary.
To avoid the update reapplying and running on all rows when unnecessary.
Ok, that's a good idea, then run:
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.
UPDATE members
SET daysremaining = DATEDIFF(enddate,startdate)
WHERE daysremaining IS NULL;
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.
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.
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.
ASKER
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.
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:
This will calculate the value for every new inserted row.
And also create this trigger:
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.
CREATE TRIGGER trigCalculateDays
AFTER INSERT ON members
FOR EACH ROW SET NEW.daysremaining = DATEDIFF(enddate,startdate);
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);
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.
ASKER
When I run
I get
I also tried a before insert. This didn't give an error in phpmyadmin but all I get is a blank screen when submitting.
CREATE TRIGGER trigCalculateDays
AFTER INSERT ON members
FOR EACH ROW SET NEW.daysremaining = DATEDIFF(enddate,startdate);
I get
#1362 - Updating of NEW row is not allowed in after trigger
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.
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.
ASKER
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.
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)
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);
}
To get newly inserted records working I also added into members.phpfunction members_after_insert($data, $memberInfo, &$args){
updatedaysremaining("ID='{$data['selectedID']}'");
return TRUE;
}
To update records I added to members.phpfunction members_after_update($data, $memberInfo, &$args){
updatedaysremaining("ID='{$data['selectedID']}'");
return TRUE;
}
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;
}
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('$e nddate',st artdate) 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(endd ate,startd ate) 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
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('$e
or something to that effect.
If you want to simply trigger an update on all the table
update members set daysremainin=datediff(endd
you can use the hook as you outlined. on each update/insert the
ASKER
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 memberYes I enter in the start date and end date.
update members set enddate='$enddate', daysremaining=datediff('$enddate',st artdate) 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,startd ate) 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?
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?
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
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.
Open in new window
Hope it helps. Regards.