[Resolved] how to update time in mysql database

ainars

My users can upgrade their accounts to "silver" and "gold" accounts.

I use this function to set account validation time for 30 days:

$time = time();
$exptime = $time + 30 3600 24;

sql_query("update portal_users set validation= '$exptime' where username='$user'", $dbi);

that's ok so far, but some users want to renew accounts before they expire. so I have to update time keeping existing validation time: existing time + new time

Validation time in mysql looks like this: "1109737055"

So, I can not just update table in this way:
sql_query("update portal_users set validation= validation+'$exptime' where username='$user'", $dbi);

Any ideas?

Thanks!

The_Chancer

Use this suggestion instead...

sql_query("update portal_users set validation= 'validation+".$exptime."' where username='".$user."'", $dbi);

ainars

Thanks, The Chancer, but my question is not about this...

Lets's say, I activate a "silver" account for 35 days. Right after that I want to add more 35 days, so, account will be valid 70 days (35+35).

If I use:
sql_query("update portal_users set validation= 'validation+".$exptime."' where username='".$user."'", $dbi);

I get validation time not 70 days, but for 34 years - till 19.01.2038.

So, first of all I have in some way to convert time which was entered in MySQL first time (how many days left) and only then to update +$exptime

Sorry for my English. I hope you understand what I mean.

Thanks

nemonoman

I think you would want to use a mysql date field.

When you create the record:

INSERT INTO mytable SET id=$somenumber, datefield=CURR_DATE();

Then to update this this record

UPDATE mytable SET datefield=datefield + INTERVAL 35 DAY
WHERE id=$somenumber;

ainars

Hmm, I asked to many people if its possible and all of them said I have to change the format of time...

Finally, I figured out that I dont need to change anything:

$days_left=round(($exptime - $time) / 3600 / 24,0);

$new_days = $days_left + 30;

$new_exp_time = $time + $newdays 3600 24;

sidney

just add 30 days to the allready exsisting value

 $exptime=30*(3600*24);
sql_query("update portal_users set validation= validation+$exptime where username='$user'", $dbi);