How to edit my existing php function and add anoth

Question

I'd like to add another option for premium accounts. At the moment you can class change your character every 2 days but I want to add another check to allow every 12 hours for premium users. They are determined from another table in PREMIUM there is their strAccountID If they are not premium there will be no rows in that table for their account.

That's my current PHP function which works for all users and allows them to do it every 2 days.

private function doProcessClassChangeFormD()
{
    $db = $this->database[GDB];
    $character = $this->site->SanitizeName($_POST['character']);
    $num_rows = $db->doQuery('SELECT bNation FROM ACCOUNT_CHAR WHERE strAccountID = ? AND ? IN(strCharID1, strCharID2, strCharID3)', $_SESSION['strAccountID'], $character);
    if ($num_rows == -1)
    {
        $db->GetError(__file__, __line__);
        $this->m_ccError = Template::GetLangVar('DB_ERROR');
        return false;
    }
    else if ($num_rows == 0)
    {
        $this->m_ccError = Template::GetLangVar('CC_INVALID_ACCOUNT');
        return false;
    }

    $row = $db->doRead();
    $nation = $row['bNation'];

    $num_rows = $db->doQuery('SELECT Class, Race, Strong, Sta, Dex, Intel, Cha, Points, TransferTime FROM USERDATA WHERE strUserId = ? AND (TransferTime < DATEADD(DAY,-2,GETDATE()) OR TransferTime IS NULL) and zone<>199 and authority<>255', $character);
    if ($num_rows == -1)
    {
        $db->GetError(__file__, __line__);
        $this->m_ccError = Template::GetLangVar('DB_ERROR');
        return false;
    }
    else if ($num_rows == 0)
    {
        $this->m_ccError = Template::GetLangVar('CC_RECENT_TRANSFER');
        return false;
    }

    $row = $db->doRead();

    $oldRace = $row['Race'];
    $oldClass = $row['Class'];
    $newRace = intval($_POST['race']);
    $newClass = intval($_POST['class']);

    $pCT = new ClassTransfer($nation, $oldClass, $oldRace, $newClass, $newRace);
    if (!($res = $pCT->canChangeClass()))
    {   
        $this->m_ccError = $pCT->GetError(__file__, __line__);
        return false;
    }

    $newClass = $res[0];
    $newRace = $res[1];

    $free = $row['Points'] + $row['Strong'] + $row['Sta'] + $row['Dex'] + $row['Intel'] + $row['Cha'];
    $newStats = $pCT->getStarterStats($newClass, $newRace);

    for ($i = 0; $i < 5; $i++)
        $free -= $newStats[$i];

    if ($free > 255)
    {
        $_SESSION['bClassTransfer'] = true;
        $_SESSION['strUserId'] = $character;
        $_SESSION['bNewRace'] = $newRace;
        $_SESSION['bNewClass'] = $newClass;
        $_SESSION['bStrong'] = $newStats[KO_STR];
        $_SESSION['bStamina'] = $newStats[KO_STA];
        $_SESSION['bDexterity'] = $newStats[KO_DEX];
        $_SESSION['bIntelligence'] = $newStats[KO_INT];
        $_SESSION['bCharisma'] = $newStats[KO_CHA];
        $_SESSION['bAvailable'] = $free;

        $this->m_bSelectPoints = true;
        return false;
    }

    $newStats[5] = $free;
    return $this->doExecuteClassChangeD($character, $newClass, $newRace, $newStats);
}

http://pastebin.com/VK4Bi7FJ

I've tried several ways but no success. How I can achieve this?

Answer 1

Use a LEFT JOIN with the PREMIUM table in your query that checks the transfer time. Then use a match in that table to conditionalize the time that you compare with.

$num_rows = $db->doQuery('
    SELECT Class, Race, Strong, Sta, Dex, Intel, Cha, Points, TransferTime
    FROM USERDATA u
    LEFT JOIN PREMIUM p ON p.strAccountID = u.strUserId
    WHERE strUserId = ?
      AND (TransferTime < IF(p.strAccountId IS NULL,
                             DATEADD(DAY,-2,GETDATE()),
                             DATE_SUB(NOW(), INTERVAL 12 HOUR)))
           OR TransferTime IS NULL)
      AND zone<>199 and authority<>255', $character);

Answer 2

Easiest way to go about it would be to do a query of the premium table:

SELECT the strAccountID from PREMIUM where strAccountID equals the current logged in user (stored in a session/cookie somewhere?)

From there just do a simple if statement. If the above select statement returns a row and the time has been greater than 12 hours, then yes let them change character.

Do the above around this bit here:

    $num_rows = $db->doQuery('SELECT Class, Race, Strong, Sta, Dex, Intel, Cha, Points,  TransferTime FROM USERDATA WHERE strUserId = ? AND (TransferTime < DATEADD(DAY,-2,GETDATE()) OR TransferTime IS NULL) and zone<>199 and authority<>255', $character);

Then all you would need to do is, if the person is premium, change the above statement to only 12 hours and not 2 days. If they aren't premium then leave it at 2 days.