1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL to change active user upgrades from permanent to expiry

Discussion in 'General PHP and MySQL Discussions' started by Mouth, Jan 16, 2016.

  1. Mouth

    Mouth Well-Known Member

    I'm looking at changing active user upgrades that are currently permanent to instead having an expiry.
    Would the following conceptual SQL work?

    UPDATE xf_user_upgrade_active
    SET end_date = UNIX_TIMESTAMP('2016-06-30')
    WHERE user_upgrade_id = '1'

    ... and then the standard 'Downgrade Expired User Upgrades' cron job would remove them on the expiry date?
     
  2. Mouth

    Mouth Well-Known Member

    This should work for my purposes (set expiry date on permanent upgrades to being upcoming annual date from when user first upgraded) ...

    Code:
    UPDATE xf_user_upgrade_active
    SET end_date = unix_timestamp(from_unixtime(start_date,'2016-%m-%d %T'))
    WHERE user_upgrade_id = '1';
    
    UPDATE xf_user_upgrade_active
    SET end_date = unix_timestamp(from_unixtime(start_date,'2017-%m-%d %T'))
    WHERE user_upgrade_id = '1' AND end_date < unix_timestamp(now());
    
     
  3. Jim Boy

    Jim Boy Well-Known Member

    I dont know if it is still the case with 1.5, but you used to have to update xf_user_group_change as well.
     
    Mouth likes this.
  4. Mouth

    Mouth Well-Known Member

    Thanks. Don't think so though ...

    Code:
    mysql> describe xf_user_group_change;
    +------------+------------------+------+-----+---------+-------+
    | Field      | Type             | Null | Key | Default | Extra |
    +------------+------------------+------+-----+---------+-------+
    | user_id    | int(10) unsigned | NO   | PRI | NULL    |       |
    | change_key | varbinary(50)    | NO   | PRI | NULL    |       |
    | group_ids  | varbinary(255)   | NO   |     | NULL    |       |
    +------------+------------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
     

Share This Page