PHP SQL函数代码示例

 /**
  * Function to test if DOB is stored correctly.
  */
 public function testSetDOB()
 {
     $dob = time() - 10000;
     $this->xobj->setDOB($dob);
     $result = SQL("SELECT `DOB` FROM XUSER WHERE USERID = ?", array($this->obj->getUserID()));
     $this->assertTrue($result[0]['DOB'] == $dob);
 }

 /**
  * Function to write the log messages in DB.
  * @param Array $args   Array of messages as given by the user to be written in log files.
  */
 public function log($args)
 {
     $logValues = $this->changeTemplate($args);
     //change the user given message appropriate to the template of the log files. This is necessary to maintain consistency among all the log files.
     $noOfEntries = count($this->template);
     //get the how many entries are in the template. This is same as number of columns present in the DB and this helps in preparing the SQL statement.
     //Prepare the SQL statement.
     $SQLStatement = "INSERT INTO " . $this->dbConfig['TABLENAME'] . " (";
     foreach ($this->template as $key => $value) {
         $SQLStatement = $SQLStatement . $key . ",";
         //Add all the column names.
     }
     $SQLStatement = substr($SQLStatement, 0, -1) . ") VALUES (";
     for ($i = 0; $i < $noOfEntries; $i = $i + 1) {
         $SQLStatement = $SQLStatement . "?,";
         //Add as many "?" as there are number of columns.
     }
     $SQLStatement = substr($SQLStatement, 0, -1) . ")";
     $values = array();
     foreach ($logValues as $k => $v) {
         array_push($values, $v);
         //create an array that contains values for all columns.
     }
     SQL($SQLStatement, $values);
     //execute the SQL statement.
 }

 function RepStandard()
 {
     // First Generate the query
     $sq = $this->RepStandardQuery();
     if ($this->display == 'SQL') {
         echo "<h1>The Generated SQL</h1>";
         hprint_r($sq);
         return;
     }
     // Now execute the query and run the report
     if ($this->display == 'CSV') {
         echo "<pre>";
         echo implode(',', $this->Cols) . "\n";
         $res = SQL($sq);
         while ($row = pg_fetch_row($res)) {
             echo implode(',', $row) . "\n";
         }
         echo "</pre>";
         return;
     }
     // Pull the info on breaking, sums, etc.
     $srep = SQLFC($this->report_id);
     $s2 = "SELECT rcl.column_id,rcl.reportlevel,rcl.summact\n             FROM reportcollevels rcl\n             JOIN reportcolumns   rc  ON rcl.column_id = rc.column_id\n            WHERE rc.report = {$srep}\n            ORDER BY rcl.reportlevel,rc.uicolseq";
     $breaks = SQL_AllRows($s2);
     $abreaks = array();
     foreach ($breaks as $break) {
         if ($break['summact'] == 'BREAK') {
             $abreaks[$break['reportlevel']]['breaks'][$break['column_id']] = '';
         } else {
             $abreaks[$break['reportlevel']]['data'][$break['column_id']] = array('summact' => $break['summact'], 'val' => 0, 'cnt' => 0);
         }
     }
     // There is always some setup, for either PDF or HTML, so do that
     // here.
     $this->RepHeader();
     // Now execute the query and run the report
     $res = SQL($sq);
     $firstrow = true;
     while ($row = SQL_Fetch_Array($res)) {
         if ($firstrow) {
             $firstrow = false;
             $this->RepStandardBreakLevelsInit($abreaks, $row);
         } else {
             $this->RepStandardBreakLevels($abreaks, $row);
         }
         $xpos = 0;
         foreach ($this->rows_col as $column_id => $colinfo) {
             $disp = substr($row[$column_id], 0, $colinfo['dispsize']);
             $disp = STR_PAD($disp, $colinfo['dispsize'], ' ');
             $this->PlaceCell($xpos, $disp);
             $xpos += 2 + $colinfo['dispsize'];
         }
         $this->ehFlushLine();
         $this->RepStandardRowsToLevels($abreaks, $row);
     }
     $this->RepStandardBreakLevels($abreaks, $row, true);
     // There is always some cleanup, either PDF or HTML
     $this->RepFooter();
 }

function GetAccessToken($appid, $uid)
{
    $sql = mysql_fetch_assoc(SQL("SELECT access_token FROM auth_token WHERE app_id='{$appid}' AND user_id={$uid}"));
    if ($sql == false) {
        throw new IAuthException('access token not exist, maybe user delete ');
    }
    return $sql['access_token'];
}

 /**
  * Function to check if the temp password expiry functionality is working.
  */
 public function testCheckIfTempPassExpired()
 {
     //update the temp pass time to current time.
     SQL("UPDATE PASSWORD SET TEMP_TIME = ? WHERE USERID = ?", array(time("SYS"), $this->user->getUserID()));
     $this->assertFalse(AdvancedPasswordManagement::checkIfTempPassExpired($this->user->getUserID()));
     //this check will provide false, since the temp password time has not expired.
     time("SET", time() + 1000000);
     //Now set the time to some distant future time.
     $this->assertTrue(AdvancedPasswordManagement::checkIfTempPassExpired($this->user->getUserID()));
     //this check will provide true, since the temp password time has expired.
 }

function sendFeedbackMail($title, $content)
{
    $res = SQL("SELECT email FROM accounts WHERE id = ?", $_SESSION['accountID']);
    $email = $res[0]["email"];
    $to = FEEDBACK_EMAIL;
    $subject = "#FEEDBACK: " . $title;
    $message = $_SESSION['username'] . " sent feedback.\nEmail: {$email}\n\n";
    $message .= $content;
    $from = "\"Croupier feedback\" <" . NOREPLY_EMAIL . ">";
    $headers = "From:" . $from;
    mail($to, $subject, $message, $headers);
}

function GetAccessInfo($appid, $accessToken)
{
    $sqlTmp = mysql_fetch_assoc(SQL("SELECT user_id,rights,access_secret,faile_t FROM auth_token WHERE app_id='{$appid}' AND access_token='{$accessToken}' "));
    if ($sqlTmp == '') {
        throw new IAuthException('access token not exist');
    }
    if ($sqlTmp['faile_t'] < date('Y-m-d H:i:s', time())) {
        /* echo $sqlTmp['faile_t'] /\* .'    '. date('Y-m-d H:i:s', time()) *\/; */
        throw new IAuthException('access token failed');
    }
    return $sqlTmp;
}

 function main()
 {
     hidden('gp_page', 'x_emailblast');
     hidden('gp_table', CleanGet('gp_table', '', false));
     hidden('gp_posted', 1);
     $table = CleanGet('gp_table', '', false);
     if ($table == '') {
         ErrorAdd('Incorrect call to x_emailblast, no table parameter.' . 'This is a programming error, please contact your ' . 'technical support department');
         return;
     }
     // Get an object for the page we need.  Then
     // pull the list of skeys in the current search
     // and pull the rows.
     //
     $obj = DispatchObject($table);
     $a_skeys = ContextGet("tables_" . $obj->table_id . "_skeys", array());
     $l_skeys = implode(',', $a_skeys);
     // get this little detail taken care of
     //
     $this->PageSubtitle = 'Email blast to ' . $obj->table['description'];
     // Get the list of columns of interest, and pull them
     // and slot them by skey, so the list of skeys can be
     // used to order them
     //
     $lDisplayColumns = $obj->table['projections']['email'];
     $aDisplayColumns = explode(',', $lDisplayColumns);
     $EmailColumn = $obj->table['projections']['emailaddr'];
     $sql = 'SELECT skey,' . $EmailColumn . ',' . $lDisplayColumns . ' FROM ' . $table . ' WHERE skey IN (' . $l_skeys . ')';
     $DBRes = SQL($sql);
     $rows = array();
     while ($row = SQL_FETCH_Array($DBRes)) {
         $rows[$row['skey']] = $row;
     }
     $okToSend = false;
     if (CleanGet('gp_posted', '', false) == 1) {
         if (CleanGet('txt_subject', '', false) == '') {
             ErrorAdd('Please fill in a subject first');
         }
         if (trim(CleanGet('txt_email', '', false)) == '') {
             ErrorAdd('Please fill in an email body');
         }
         if (!Errors()) {
             $okToSend = true;
         }
     }
     // Now that we have the results, decide whether we
     // are sending out the email or
     if ($okToSend) {
         $this->EmailBlast($rows, $a_skeys, $EmailColumn, $aDisplayColumns);
     } else {
         $this->EmailHTML($rows, $a_skeys, $aDisplayColumns);
     }
 }

 /**
  * Function to test the storage of logs in DB.
  */
 public function testCreation()
 {
     $result1 = SQL("SELECT COUNT(`ID`) FROM `LOGS`");
     $this->myLogger->log("This is the first message", "WARNING", "LOW");
     //store this log.
     $this->myLogger->log("This is the second message");
     //store this log.
     $result2 = SQL("SELECT COUNT(`ID`) FROM `LOGS`");
     //get how many records are there in the log DB.
     $this->assertTrue($result2[0]["COUNT(`ID`)"] - $result1[0]["COUNT(`ID`)"] == 2);
     // Should have two log entries.
 }

function getLeaderboardForUser($lbID)
{
    $leaderboard = SQL("SELECT tableName FROM leaderboards WHERE id = ?", $lbID);
    if ($leaderboard == null) {
        return null;
    }
    $tableName = $leaderboard[0]["tableName"];
    $result = SQL("SELECT name, username, score\n                        FROM bots\n                        INNER JOIN {$tableName} on {$tableName}.botID = bots.id\n                        INNER JOIN accounts on accounts.id = bots.accountID\n                        ORDER BY {$tableName}.score DESC");
    for ($i = 0; $i < count($result); $i++) {
        $result[$i]["score"] = number_format(floatval($result[$i]["score"]), 0, '', '');
    }
    return $result;
}

 static function getClientsById($id)
 {
     openSQLConnection();
     $q = "SELECT * FROM client WHERE id = ?;";
     $rows = SQL($q, $id);
     $clients = [];
     if (!empty($rows)) {
         foreach ($rows as $name => $value) {
             $clients[] = Client::fromJSON(json_encode($value));
         }
     }
     return $clients;
 }

 static function getAllUsers()
 {
     openSQLConnection();
     $q = "SELECT * FROM user;";
     $rows = SQL($q);
     $users = [];
     if (!empty($rows)) {
         foreach ($rows as $name => $value) {
             $users[] = User::fromJSON(json_encode($value));
         }
     }
     return $users;
 }

 function main()
 {
     if (gpExists('gp_xajax')) {
         $sq = "UPDATE variables\n                    SET variable_value = " . SQLFC(gp('varval')) . "\n                  WHERE variable = " . SQLFC(gp('variable'));
         SQL($sq);
     }
     if (gpExists('gp_cache')) {
         //unlink($GLOBALS['AG']['dirs']['dynamic'].'table_variables.php');
         OptionGet('X');
     }
     if (gpExists('gp_xajax')) {
         return;
     }
     parent::main();
 }

    function main()
    {
        echo "<h1>View Build Log</h1>";
        ?>
<p>Currently viewing the log for application code "
  <b><?php 
        echo gp('application');
        ?>
</b>".  Click any
   of the links below to see the logs for the particular application:</p>
<?php 
        // Run out list of apps they can see
        echo "<hr>\n";
        $results = SQL("select * from applications");
        while ($row = pg_fetch_array($results)) {
            echo HTMLE_A_STD($row["application"] . " - " . $row["description"], "a_builder_log", "gp_out=info&application=" . $row["application"]) . "<br>\n";
        }
        echo "<hr>\n";
        // Make up a filename
        global $AG;
        $t = pathinfo(__FILE__);
        $pLogDir = $t["dirname"];
        $pLogFile = "AndroDBB." . CleanGet("application") . ".log";
        $pLogPath = "{$pLogDir}/{$pLogFile}";
        if (!file_exists($pLogPath)) {
            echo "<p>There is no build log file for this application at this time.  This usually\n\t\t\t\tmeans that the application has not been built yet.  Try launching a build process\n\t\t\t\tand then coming back to this page.</p>";
            return;
        }
        //echo "<pre style=\"background-color: silver; color: blue\">";
        $fgc = file_get_contents($pLogPath);
        $fgc = str_replace("\n", "<br>", $fgc);
        echo "<div style=\"font: 10pt courier; color: navy;\">" . $fgc . "</div>";
        //echo file_get_contents($pLogPath);
        //echo "</pre>";
        return "View Build/Update Log for: " . CleanGet("application");
    }

        $datcadastro = $_REQUEST['datcadastro'];
        $datcadastro = FormataData($datcadastro, "en");
        $desnoticia = $_REQUEST['desnoticia'];
        $flaativo = $_REQUEST['flaativo'];
        $desurl = $_REQUEST['desurl'];
        $sql2 = "insert into noticia (desnoticia, datcadastro, flaativo, desurl)" . " values " . "('" . $desnoticia . "','" . $datcadastro . "','" . $flaativo . "','" . $desurl . "')";
        $result = mysql_query($sql2) or die(mysql_error());
    }
    if ($bttipoacao == "Editar") {
        $datcadastro = $_REQUEST['datcadastro'];
        $datcadastro = FormataData($datcadastro, 'en');
        $sql = "update noticia set desnoticia = '" . $_REQUEST['desnoticia'] . "',\n  \t                           datcadastro = '" . $datcadastro . "',\n  \t                           desurl = '" . $_REQUEST['desurl'] . "',\n  \t                           flaativo = '" . $_REQUEST['flaativo'] . " '\n  \t                           where codnoticia = " . $_REQUEST['codnoticia'];
        $result = mysql_query($sql) or die("Erro na Inserção!");
    }
    if ($bttipoacao == "Excluir") {
        $sql = SQL("noticia", "delete", "", "codnoticia");
        mysql_query($sql) or die(mysql_error());
    }
    ?>
<br>
 <table width='580' class="table" cellspacing='0' cellpadding='0'>
  <tr>
    <td width="5">&nbsp;</td>
    <td align='center' class="td3"><strong><b>Mensagem</b></strong></td>
  </tr>
  <tr>
    <td width="5"></td>
    <td align='center' ></td>
  </tr>
  <tr>
    <td width="5">&nbsp;</td>