Calculating date and time

Report · Oct 18, 2006

Using DW8, PHP and MySQL.

I created 3 radio button options:

1. Suspended for 24 hours
2. Suspended for 3 days
3. Suspended for 5 days

I want to be able to select one of those options and save it. When I go to look at the persons profile, it will tell me when they can be unsuspended. I could easily make it output "Person is suspended for 3 days" but I don't want to have to do the match down to the hour and minute to see exactly what time they are unsuspended.

Thanks for your help!

Report · Oct 19, 2006

I work in SQL-Server, rather than in MySQL, so I'm not sure whether you can write a Stored Procedure. If you can . . .

You need three fields in your personnel table:

1.) Toggle field to store the value set by the radio buttons. The default should be 0 and the other values should be integers that represent the interval to reactivation: 1, 3, or 5 days.

2). Date field to store the Reactivation Date

3.) Active field to store the bit, TRUE OR FALSE, to signify whether the account is Active or Inactive.

You need to write two small procedures:

1.) Write a procedure to run from a trigger attached to your table that will EXECUTE the procedure whenever there is an INSERT, or UPDATE. Write an update statement in SQL that will use the value stored in your radio button field to calculate the reactivation date and poke that date value into the Activate field. It will also be necessary to set the Active field to FALSE. Since this procedure runs whenever there is an INSERT or UPDATE to the record. It will examine all records and will inactivate those that have a toggle value in the radio button field. The last step in the procedure should reset the toggle field to 0.

2.) You need a second procedure to run on a job scheduled to run daily. This would be best between 12:01 and 6:00 am, say. This procedure compares today’s date to the value in your Activate field. If the current date is greater than or equal to that date, it resets the Active field to TRUE. If not, the Active date = Active date. This procedure will update all records in the personnel table, ignoring those that do not have a value in the toggle field and updating those that do.

Once you have these two procedures in place, the whole process will be automatic. I wrote this quickly, so I hope that I didn’t leave anything out.

Good luck.

Steve

Report · Oct 19, 2006

Eiolon wrote:
> Using DW8, PHP and MySQL.
>
> I created 3 radio button options:
>
> 1. Suspended for 24 hours
> 2. Suspended for 3 days
> 3. Suspended for 5 days
>
> I want to be able to select one of those options and save it. When I go to
> look at the persons profile, it will tell me when they can be unsuspended.

This is very simple to do in MySQL. Add a DATETIME column to the table
that lists users' details. Call it something like active_date. Then
update the table like this:

UPDATE users SET active_date = NOW()

That gives everyone an active_date of the current date and time.

When you suspend someone, set the value of the radio buttons as 1, 3, and 5.

Use the following SQL:

UPDATE users SET active_date = DATE_ADD(NOW(), INTERVAL param1 DAY)
WHERE user_id = param2

Set param1 as the value of the radio button and set the type to numeric
(if you're using DW 8.0.2). Set param2 to the value of the user_id
(also numeric).

You can check if the user is suspended by comparing active_date with NOW().

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