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

Run query on user upgrade (code example) 1.x

Useful if you need to grant additional awards or status when a user pays for an upgrade.

  1. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Jake Bunce submitted a new resource:

    Run query on user upgrade (code example) (version 1.x) - Useful if you need to grant additional awards or status when a user pays for an upgrade.

    Read more about this resource...
     
    0xym0r0n and Andy.N like this.
  2. bashy

    bashy Member

    Wow, will be using this for sure!
    Very useful for giving admin in another location.
     
  3. Andy.N

    Andy.N Well-Known Member

    Jake,
    This is extremely useful. Can't believe I overlook this.
    But more importantly, can this addon be built so that we don't edit core XF files? I probably will forget about this when I upgrade XF over time.
     
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    You edit the files for this addon, not the core files. They won't be overwritten during an upgrade.
     
    Andy.N likes this.
  5. Andy.N

    Andy.N Well-Known Member

    Also, I have multiple upgrades.
    How can I make one query when upgradeID1 happens and another query when upgradeID2 happens.
    Thanks
     
  6. Andy.N

    Andy.N Well-Known Member

    Wow. That a huge relief. Thanks jake.
     
  7. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    The $upgrade parameter is the xf_user_upgrade record. You can check fields from that table. For example:

    Code:
    if ($upgrade['user_upgrade_id'] == 1)
    {
    	...
    }
    else if ($upgrade['user_upgrade_id'] == 2)
    {
    	...
    }
    
    That should do it.
     
    nacs and Andy.N like this.
  8. Andy.N

    Andy.N Well-Known Member

    Wow. This works great right off the bat. Amazing.
    I'm trying to insert a record into a custom table when an upgrade occurs.
    Code:
     if ($upgrade['user_upgrade_id'] == 3)
     {
     $db->query("
     INSERT INTO xf_qntta_relation (ta_user_id, student_user_id, note)
     VALUES (1, 167, 'this is a notes')
              ");
     }
    I hardcoded the value 167 where it should be the user_id of the member who is being upgraded.
    'this is a note' is also hardcoded. I'd really like to get some variables such as the user's email address, the date of the upgrade. This is nice to have but not important.
    Jake,
    How do I get the userid and insert that into the table? You have this query but it's for update query, not insert query
    Code:
    WHERE username = (
            SELECT username
            FROM xf_user
            WHERE user_id = " . $userId . "
                     )
    Thank you, Jake.
     
  9. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    The userid is already one of the function parameters, $userId
     
    akia likes this.
  10. Andy.N

    Andy.N Well-Known Member

    Code:
    INSERT INTO xf_qntta_relation (ta_user_id, student_user_id, note)
     VALUES (1, " . $userId . " , 'this is a notes')
    You are the man, Jake. Appreciate it.
     
  11. Andy.N

    Andy.N Well-Known Member

    Jake,
    1) I'd like to add an extra 4 weeks (28 days) to the start date of an existing upgrade when an additional upgrade takes place. The start_date is unix time. This is what I have so far. I think I need to convert the date before I can add?
    Code:
     else if ($upgrade['user_upgrade_id'] == 3)
     {
     $db->query("
     UPDATE xf_user_upgrade_active
     SET start_date = start_date + 28
     WHERE user_id = " . $userId . "
     AND user_upgrade_id = 2
                ");
     }
    2) How do I run multiple sql queries within this block. Do I do this?
    Code:
     else if ($upgrade['user_upgrade_id'] == 3)
     {
     $db->query("
     query one;
    query two;
    query three
                ");
     }
     
  12. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    1) Use this in your query:

    SET start_date = start_date + 28*86400

    86400 is the number of seconds in one day.

    2) Do this:

    Code:
    else if ($upgrade['user_upgrade_id'] == 3)
     {
     $db->query("
     query one
                ");
    
     $db->query("
    query two
                ");
    
     $db->query("
    query three
                ");
     }
    
     
    Andy.N likes this.
  13. Andy.N

    Andy.N Well-Known Member

    Great stuff, Jake. Many thanks.
    I'd like to keep records of the original start date and the new start update in a table, How can I achieve it?
    Code:
    ###Get current start_date
    $db->query("
    UPDATE xf_qntta_relation
    SET note = concat(note,'Original start date is ',SELECT CURRENT START_DATE from xf_user_upgrade_active table WHERE user_id = " . $userId . " AND user_upgrade_id = 2 )
    WHERE student_user_id = " . $userId . "
                ");
     
    #####Update start_date
    $db->query("
    UPDATE xf_user_upgrade_active
    SET start_date = start_date + 28*86400
    WHERE user_id = " . $userId . "
    AND user_upgrade_id = 2
        ");
     
    #########Add a note of new start date
    $db->query("
    UPDATE xf_qntta_relation
    SET note = concat(note,'New start date is ', SELECT CURRENT START_DATE from xf_user_upgrade_active table where user_id = " . $userId . " AND user_upgrade_id = 2)
    WHERE student_user_id = " . $userId . "
        ");
     
  14. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Code:
    $stateDate = $db->fetchOne("
    	SELECT start_date
    	FROM xf_user_upgrade_active
    	WHERE .....
    ");
    
     
    Andy.N likes this.
  15. Andy.N

    Andy.N Well-Known Member

    Last one, I promise ;)
    I'm trying to fetch the datetime, convert it mm/dd/yyy from unix time and concat it at the front of the note field. Somehow, it does not add the date format but keep the unix date time when it adds in the note field.
    Code:
    $startDate = $db->fetchOne("
    SELECT FROM_UNIXTIME(min(start_date),'%c/%e/%Y')
    FROM xf_user_upgrade_active
    WHERE user_id = " . $userId . "
    ");
     
    $db->query("
    UPDATE xf_qntta_relation
    SET note = concat('Extension. Original start date is '," . $startDate . ", note)
     
  16. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Missing single quotes around the date. It's a string.
     
    Andy.N likes this.
  17. Andy.N

    Andy.N Well-Known Member

    Everything works perfectly now. Thank you, JAKE.
     
  18. Jake Hakoda Shirley

    Jake Hakoda Shirley Active Member

    This is pretty awesome, but we need a way to run a query when the user's subscription runs out as well. Any advice?
     
  19. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    You can add a downgradeUserUpgrades() function to the extended class in this addon.

    Code:
    	public function downgradeUserUpgrades(array $upgrades)
    	{
    		parent::downgradeUserUpgrades($upgrades);
    
    		// YOUR STUFF HERE
    	}
    
     
  20. a legacy reborn

    a legacy reborn Well-Known Member

    Would it be possible to use something like this to run a query on a different DB? It seems so but I know little about SQL/DBs.
     

Share This Page