PHP Object::query方法代码示例

 /**
  * __construct
  *
  * @desc 构造器
  * @access private
  * @return void
  */
 private function __construct($dbOptions)
 {
     $dsn = $dbOptions["adapter"] . ':host=' . $dbOptions["host"] . ';dbname=' . $dbOptions["dbname"];
     try {
         self::$_dbh = new PDO($dsn, $dbOptions["username"], $dbOptions["password"], array(PDO::ATTR_PERSISTENT => $dbOptions["use_pconnect"]));
         self::$_dbh->query("SET NAMES '" . $dbOptions["charset"] . "'");
         if (strtolower($dbOptions['adapter']) == 'mysql' && true === $dbOptions["use_buffered_query"]) {
             self::$_dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
         }
         if ($dbOptions['throw_exception']) {
             self::$_dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
         }
     } catch (PDOException $e) {
         throw new Exception($e->getMessage());
     }
 }

 function deleteQueueEntry($id, $position)
 {
     //delete DB entry
     $this->_wpdb->query("DELETE FROM {$this->QUEUE_TABLE} WHERE q_img_id = {$id}");
     //delete cat entries
     $this->_wpdb->query("DELETE FROM {$this->QCAT_TABLE} WHERE q_fk_img_id = {$id}");
     //delete field entries
     $this->_wpdb->query("DELETE FROM {$this->QUEUEMETA_TABLE} WHERE q_fk_img_id = {$id}");
     //update queue positions
     $this->_wpdb->query("UPDATE  {$this->QUEUE_TABLE} SET q_position = q_position-1 WHERE q_position > '{$position}'");
 }

 /**
  * Executa uma query no banco de dados
  *
  * @param string $sql
  * @return array
  */
 function query($sql, $relacionado = array())
 {
     if ($this->useTable === false) {
         return array();
     }
     $rs = $this->db->query($sql);
     if ($this->relacionado != null) {
         if (stripos($sql, "select") !== false) {
             return $this->db->getResultsRel($rs, $this->chavePrimaria, get_class($this) . "__" . $this->relacionado);
         }
     }
     if (stripos($sql, "select") !== false) {
         return $this->db->getResults($rs, $this->chavePrimaria);
     }
 }

 /**
  * Check if a character exists and is offline
  * @param Int $guid
  * @param Object $realmConnection
  * @param Int $realmId
  * @return Boolean
  */
 public function characterExists($guid, $realmConnection, $realmId)
 {
     $query = $realmConnection->query("SELECT COUNT(*) AS `total` FROM " . table("characters", $realmId) . " WHERE " . column("characters", "guid", false, $realmId) . " = ? AND " . column("characters", "online", false, $realmId) . " = 0", array($guid));
     if ($realmConnection->_error_message()) {
         die($realmConnection->_error_message());
     }
     if ($query->num_rows() > 0) {
         $result = $query->result_array();
         if ($result[0]['total']) {
             return true;
         } else {
             return false;
         }
     } else {
         return false;
     }
 }

 /**
  * Delete Comments
  *
  * This function will delete a comment by it's note_id
  * This function will mainly be used by administrators and
  * people with enough karma to manage comments.
  *
  * @access public
  * @param  Array $note_ids  The array of the notes to delete
  *
  * @return Mixed   $res      An error object if query was erroneous, bool
  *                           if it was successful
  */
 function deleteComments($note_ids)
 {
     if (!is_array($note_ids)) {
         return false;
     }
     /**
      * Let's just format the note ids so they are simple to
      * read within an IN()
      */
     $notes = "'" . implode(', ', $note_ids) . "'";
     $sql = "\n            UPDATE {$this->notesTableName}\n             SET note_deleted = 1, note_approved='no'\n              WHERE note_id IN({$notes})\n        ";
     $res = $this->dbc->query($sql);
     if (PEAR::isError($res)) {
         return $res;
     }
     return true;
 }

 /**
  * Fetch query parameters
  *
  * @return string
  */
 private function __queryParams()
 {
     $url = $this->request->query();
     if (strlen($url) > 0) {
         $url = "?{$url}";
     }
     return $url;
     if (isset($this->request->sock->config['request']['uri']['query'])) {
         $qParams = $this->request->sock->config['request']['uri']['query'];
         if (is_array($qParams) && count($qParams) > 0) {
             $url = '?' . OauthHelper::mapper($qParams, '&', '');
         } elseif (is_string($qParams)) {
             $url = $qParams;
         } else {
             $url = '?';
         }
         return $url;
     } else {
         return '';
     }
 }

 /**
  * Shot a query to database and return raw data
  *
  * @param   string  $query
  *
  * @return  mixed
  * 
  * @throws  \Comodojo\Exception\DatabaseException
  */
 public function rawQuery($query)
 {
     switch ($this->model) {
         case "MYSQLI":
             $response = $this->dbh->query($query);
             if (!$response) {
                 throw new DatabaseException($this->dbh->error, $this->dbh->errno);
             }
             break;
         case "MYSQL_PDO":
         case "ORACLE_PDO":
         case "SQLITE_PDO":
         case "DBLIB_PDO":
             try {
                 $response = $this->dbh->prepare($query);
                 $response->execute();
             } catch (\PDOException $e) {
                 throw new DatabaseException($e->getMessage(), (int) $e->getCode());
             }
             break;
         case "DB2":
             $response = db2_exec($this->dbh, $query);
             if (!$response) {
                 throw new DatabaseException(db2_stmt_error());
             }
             break;
         case "POSTGRESQL":
             $response = pg_query($this->dbh, $query);
             if (!$response) {
                 throw new DatabaseException(pg_last_error());
             }
             break;
         default:
             throw new DatabaseException('Invalid database model');
             break;
     }
     return $response;
 }

    /**
     * Builder method for the class 
     * @param Object $db Database interface
     * @param string $table table name
     * @param string $field column name
     * @return IBM_DB2Field
     */
    static function fromText($db, $table, $field)
    {
        global $wgDBmwschema;
        $q = <<<END
SELECT
lcase(coltype) AS typname,
nulls AS attnotnull, length AS attlen
FROM sysibm.syscolumns
WHERE tbcreator=%s AND tbname=%s AND name=%s;
END;
        $res = $db->query(sprintf($q, $db->addQuotes($wgDBmwschema), $db->addQuotes($table), $db->addQuotes($field)));
        $row = $db->fetchObject($res);
        if (!$row) {
            return null;
        }
        $n = new IBM_DB2Field();
        $n->type = $row->typname;
        $n->nullable = $row->attnotnull == 'N';
        $n->name = $field;
        $n->tablename = $table;
        $n->max_length = $row->attlen;
        return $n;
    }

 /**
  * Given a list of conditions in params and a list of desired
  * return Properties generate the required select and from
  * clauses. Note that since the where clause introduces new
  * tables, the initial attempt also retrieves all variables used
  * in the params list
  *
  * @return void
  * @access public
  */
 function selectClause()
 {
     $properties = array();
     $cfIDs = array();
     $this->addSpecialFields();
     $this->addContributeFields();
     //CRM_Core_Error::debug( 'f', $this->_fields );
     //CRM_Core_Error::debug( 'p', $this->_params );
     //format privacy options
     if (is_array($this->_params['privacy'])) {
         foreach ($this->_params['privacy'] as $key => $value) {
             if ($value) {
                 $this->_params[$key] = 1;
             }
         }
     }
     foreach ($this->_fields as $name => $field) {
         $value = CRM_Utils_Array::value($name, $this->_params);
         // if we need to get the value for this param or we need all values
         if (!CRM_Utils_System::isNull($value) || CRM_Utils_Array::value($name, $this->_returnProperties)) {
             $cfID = CRM_Core_BAO_CustomField::getKeyID($name);
             if ($cfID) {
                 $value = CRM_Utils_Array::value($name, $this->_params);
                 $cfIDs[$cfID] = $value;
             } else {
                 if (isset($field['where'])) {
                     list($tableName, $fieldName) = explode('.', $field['where'], 2);
                     if (isset($tableName)) {
                         if (CRM_Utils_Array::value($tableName, $GLOBALS['_CRM_CONTACT_BAO_QUERY']['_dependencies'])) {
                             $this->_tables['civicrm_location'] = 1;
                             $this->_select['location_id'] = 'civicrm_location.id as location_id';
                             $this->_element['location_id'] = 1;
                             $this->_tables['civicrm_address'] = 1;
                             $this->_select['address_id'] = 'civicrm_address.id as address_id';
                             $this->_element['address_id'] = 1;
                         }
                         $this->_tables[$tableName] = 1;
                         // also get the id of the tableName
                         $tName = substr($tableName, 8);
                         if ($tName != 'contact') {
                             $this->_select["{$tName}_id"] = "{$tableName}.id as {$tName}_id";
                             $this->_element["{$tName}_id"] = 1;
                         }
                         //special case for phone
                         if ($name == 'phone') {
                             $this->_select['phone_type'] = "civicrm_phone.phone_type as phone_type";
                             $this->_element['phone_type'] = 1;
                         }
                         if ($name == 'state_province') {
                             $this->_select[$name] = "civicrm_state_province.abbreviation as `{$name}`";
                         } else {
                             $this->_select[$name] = $field['where'] . " as `{$name}`";
                         }
                         $this->_element[$name] = 1;
                     }
                 } elseif ($name === 'tags') {
                     $this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_tag.name)) AS tags";
                     $this->_tables['civicrm_tag'] = 1;
                     $this->_tables['civicrm_entity_tag'] = 1;
                 } elseif ($name === 'groups') {
                     $this->_select[$name] = "GROUP_CONCAT(DISTINCT(civicrm_group.name)) AS groups";
                     $this->_tables['civicrm_group'] = 1;
                     $this->_tables['civicrm_group_contact'] = 1;
                 }
             }
         } else {
             if (CRM_Utils_Array::value('is_search_range', $field)) {
                 // this is a custom field with range search enabled, so we better check for two/from values
                 $cfID = CRM_Core_BAO_CustomField::getKeyID($name);
                 if ($cfID) {
                     $value = CRM_Utils_Array::value($name . '_from', $this->_params);
                     if (!CRM_Utils_System::isNull($value)) {
                         $cfIDs[$cfID]['from'] = $value;
                     }
                     $value = CRM_Utils_Array::value($name . '_to', $this->_params);
                     if (!CRM_Utils_System::isNull($value)) {
                         $cfIDs[$cfID]['to'] = $value;
                     }
                 }
             }
         }
     }
     // add location as hierarchical elements
     $this->addHierarchicalElements();
     if (!empty($cfIDs)) {
         //CRM_Core_Error::debug( 'cfIDs', $cfIDs );
         require_once 'CRM/Core/BAO/CustomQuery.php';
         $this->_customQuery = new CRM_Core_BAO_CustomQuery($cfIDs);
         $this->_customQuery->query();
         $this->_select = array_merge($this->_select, $this->_customQuery->_select);
//.........这里部分代码省略.........

/**
* Retrieve packages informations
*
* @param Object $db A reference to the database
*
* @return array
*/
function report($db)
{
    global $what, $smarty;
    $packages = null;
    $rpmrepo = new TableRpmRepo($db);
    $repos = $rpmrepo->getAllRepoHash();
    $smarty->assign('repos', $repos);
    if (substr($what, 0, 1) == '%') {
        $sql = sprintf("SELECT DISTINCT name\n             FROM rpm\n             WHERE SUBSTRING(name,1,1)='%s'\n             ORDER BY name", substr($what, 1, 1));
    } else {
        $sql = sprintf("SELECT DISTINCT name\n             FROM acls\n             WHERE owner='%s'\n             ORDER BY name", $what);
    }
    //echo "<p>SQL=$sql</p>";
    $i = 0;
    $res = $db->query($sql);
    if ($res) {
        while ($desc = $res->fetchObject()) {
            $package = null;
            $rpmname = $desc->name;
            $sql2 = "SELECT DISTINCT owner FROM acls WHERE name LIKE '{$rpmname}'";
            $res2 = $db->query($sql2);
            $dispowner = "";
            $owners = array();
            if ($res2) {
                while ($owner = $res2->fetchObject()) {
                    $owners[] = $owner->owner;
                }
            }
            $sql3 = "SELECT * FROM rpm WHERE name LIKE '{$rpmname}'";
            $res3 = $db->query($sql3);
            $rpm = $res3 ? $res3->fetchObject() : false;
            if ($rpm) {
                $url = $rpm->url;
                $des = htmlentities($rpm->summary);
                $rpms = array();
                do {
                    $rpms[$rpm->repo_main . "-" . $rpm->repo_sub] = $rpm;
                } while ($rpm = $res3->fetchObject());
                $package['name'] = $rpmname;
                $package['description'] = $des;
                $package['owners'] = $owners;
                $versions = null;
                foreach ($repos as $repomain) {
                    $display = "";
                    $class = "";
                    foreach ($repomain as $repo) {
                        if (isset($rpms[$repo['main'] . "-" . $repo['sub']])) {
                            $rpm = $rpms[$repo['main'] . "-" . $repo['sub']];
                            $maxver = isset($rpms["devel-"]) ? $rpms["devel-"]->ver : "";
                            if (strpos($repo['sub'], '-os') || strpos($repo['sub'], '-beta') || strpos($repo['sub'], '-base') || strpos($repo['sub'], '-optional') || strpos($repo['sub'], '-stable')) {
                                // For CentOS
                                $repotype = 'base';
                            } else {
                                if (strpos($repo['sub'], 'testing')) {
                                    $repotype = 'testing';
                                } else {
                                    if (strpos($repo['sub'], 'updates')) {
                                        $repotype = 'updates';
                                    } else {
                                        $repotype = $repo['sub'];
                                    }
                                }
                            }
                            switch ($repotype) {
                                case "base":
                                    if (isset($rpms[$repo['main'] . "-updates"])) {
                                        $display .= sprintf("%s-%s<br/>", $rpm->ver, $rpm->rel);
                                    } else {
                                        $display .= sprintf("<strong>%s</strong>-%s<br/>", $rpm->ver, $rpm->rel);
                                        $class = $rpm->ver == $maxver ? "check" : "attn";
                                    }
                                    break;
                                case "":
                                    $display .= sprintf("<strong>%s</strong>-%s<br/>", $rpm->ver, $rpm->rel);
                                    break;
                                case "updates":
                                    $display .= sprintf("<strong>%s</strong>-%s<br/><small>(%s)</small><br/>", $rpm->ver, $rpm->rel, $repo['sub']);
                                    $class = $rpm->ver == $maxver ? "check" : "attn";
                                    break;
                                case "testing":
                                    $display .= sprintf("%s-%s<br/><small>(%s)</small><br/>", $rpm->ver, $rpm->rel, $repo['sub']);
                                    $class = $rpm->ver == $maxver ? "info" : "attn";
                                    break;
                            }
                        }
                        // RPM exists
                    }
                    // sub repo
                    $versions[] = array('class' => $class && $maxver ? $class : '', 'display' => $display);
                    $package['versions'] = $versions;
                }
                // mainrepo
                $i++;
//.........这里部分代码省略.........

 /**
  * Method that returns count/total number of reviews
  * @param Object $dbObj Datatbase connectivity object
  * @param int $courseId Course Id 
  * @return int Number of reviews
  */
 public static function getRawCount($dbObj, $courseId = 0)
 {
     $sql = "SELECT * FROM course_review ";
     if ($courseId !== 0) {
         $sql = "SELECT * FROM course_review WHERE course = {$courseId} ";
     }
     $count = "";
     $result = $dbObj->query($sql);
     $totalData = mysqli_num_rows($result);
     if ($result !== false) {
         $count = $totalData;
     }
     return $count;
 }

/**
 * Parses the sql file and executes the given statements.
 *
 * @param Object $db The db adapter to use
 * @param String $path The path to the sql file to execute
 * @param String $prefix The prefix to use for the tables
 */
function conjoon_createDatastructure($db, $path, $prefix = "")
{
    // check here if we need to migrate data
    $migrate = false;
    $sql = "SELECT * FROM " . $prefix . "groupware_email_folders_users";
    $result = $db->query($sql);
    if (!$result) {
        $migrate = true;
    }
    // twitter migrate
    $twittersql = "SELECT twitter_id FROM " . $prefix . "service_twitter_accounts";
    $twitterresult = $db->query($twittersql);
    if (!$twitterresult) {
        $db->query("TRUNCATE TABLE `" . $prefix . "service_twitter_accounts`");
    }
    $sqlFile = file_get_contents($path);
    // remove sql comments
    $sqlFile = preg_replace("/^--.*?\$/ims", "", $sqlFile);
    //replace prefix
    $sqlFile = str_replace('{DATABASE.TABLE.PREFIX}', $prefix, $sqlFile);
    $statements = explode(';', $sqlFile);
    for ($i = 0, $len = count($statements); $i < $len; $i++) {
        $statement = trim($statements[$i]);
        if ($statement == "") {
            continue;
        }
        InstallLogger::stdout(InstallLogger::getInstance()->logMessage("[STRUCTURE]: " . $statement));
        if (!$db->query($statement)) {
            $err = $db->errorInfo();
            InstallLogger::stdout(InstallLogger::getInstance()->logMessage("[STRUCTURE:FAILED]: " . (!empty($err) ? $err[2] : $statement)));
        }
    }
    if ($migrate) {
        sleep(1);
        // migrate to groupware_email_folders_users
        // get the user ids associated with the user accounts
        $folderAccountsQuery = "SELECT " . $prefix . "groupware_email_folders_accounts.*, " . "" . $prefix . "groupware_email_accounts.user_id FROM " . " " . $prefix . "groupware_email_folders_accounts, " . "" . $prefix . "groupware_email_accounts" . " " . $prefix . "groupware_email_accounts " . " WHERE " . $prefix . "groupware_email_accounts.id = " . "" . $prefix . "groupware_email_folders_accounts.groupware_email_accounts_id";
        $folderAccountsResult = $db->query($folderAccountsQuery);
        if (!$folderAccountsResult) {
            // error or something - return
            return;
        }
        $folderAccountsResultCount = 0;
        $folderMapping = array();
        foreach ($folderAccountsResult as $row) {
            $folderAccountsResultCount++;
            $folderMapping[] = $row;
        }
        if ($folderAccountsResultCount == 0) {
            return;
        }
        for ($i = 0, $len = count($folderMapping); $i < $len; $i++) {
            $query = "INSERT INTO " . $prefix . "groupware_email_folders_users " . "(groupware_email_folders_id, users_id, relationship) " . "VALUES (" . "" . $folderMapping[$i]['groupware_email_folders_id'] . "," . "" . $folderMapping[$i]['user_id'] . "," . "'owner'" . ")";
            $db->query($query);
        }
    }
}

 /**
  * Method that returns count/total number of a particular course
  * @param Object $dbObj Datatbase connectivity object
  * @return int Number of courses
  */
 public static function getRawCount($dbObj)
 {
     $sql = "SELECT * FROM " . self::$tableName . " ";
     $count = "";
     $result = $dbObj->query($sql);
     $totalData = mysqli_num_rows($result);
     if ($result !== false) {
         $count = $totalData;
     }
     return $count;
 }

 /**
  * Method that returns count/total number of a particular course
  * @param Object $dbObj Datatbase connectivity object
  * @return int Number of courses
  */
 public static function getRawCount($dbObj, $dbPrefix)
 {
     $tableName = $dbPrefix . 'course_category';
     $sql = "SELECT * FROM {$tableName} ";
     $count = "";
     $result = $dbObj->query($sql);
     $totalData = mysqli_num_rows($result);
     if ($result !== false) {
         $count = $totalData;
     }
     return $count;
 }

 /**
  * Given a list of conditions in params and a list of desired
  * return Properties generate the required select and from
  * clauses. Note that since the where clause introduces new
  * tables, the initial attempt also retrieves all variables used
  * in the params list
  *
  * @return void
  */
 public function selectClause()
 {
     foreach ($this->_fields as $name => $field) {
         // if this is a hierarchical name, we ignore it
         $names = explode('-', $name);
         if (count($names) > 1 && isset($names[1]) && is_numeric($names[1])) {
             continue;
         }
         $cfID = CRM_Core_BAO_CustomField::getKeyID($name);
         if (!empty($this->_paramLookup[$name]) || !empty($this->_returnProperties[$name])) {
             if ($cfID) {
                 // add to cfIDs array if not present
                 if (!array_key_exists($cfID, $this->_cfIDs)) {
                     $this->_cfIDs[$cfID] = array();
                 }
             } elseif (isset($field['where'])) {
                 list($tableName, $fieldName) = explode('.', $field['where'], 2);
                 if (isset($tableName)) {
                     // also get the id of the tableName
                     $this->_select[$name] = "{$tableName}.{$fieldName}  as `{$name}`";
                 }
             } else {
                 //dsm('volgende velden worden niet in select verwerkt');
                 //dsm($name);
             }
             if ($cfID && !empty($field['is_search_range'])) {
                 // this is a custom field with range search enabled, so we better check for two/from values
                 if (!empty($this->_paramLookup[$name . '_from'])) {
                     if (!array_key_exists($cfID, $this->_cfIDs)) {
                         $this->_cfIDs[$cfID] = array();
                     }
                     foreach ($this->_paramLookup[$name . '_from'] as $pID => $p) {
                         // search in the cdID array for the same grouping
                         $fnd = FALSE;
                         foreach ($this->_cfIDs[$cfID] as $cID => $c) {
                             if ($c[3] == $p[3]) {
                                 $this->_cfIDs[$cfID][$cID][2]['from'] = $p[2];
                                 $fnd = TRUE;
                             }
                         }
                         if (!$fnd) {
                             $p[2] = array('from' => $p[2]);
                             $this->_cfIDs[$cfID][] = $p;
                         }
                     }
                 }
                 if (!empty($this->_paramLookup[$name . '_to'])) {
                     if (!array_key_exists($cfID, $this->_cfIDs)) {
                         $this->_cfIDs[$cfID] = array();
                     }
                     foreach ($this->_paramLookup[$name . '_to'] as $pID => $p) {
                         // search in the cdID array for the same grouping
                         $fnd = FALSE;
                         foreach ($this->_cfIDs[$cfID] as $cID => $c) {
                             if ($c[4] == $p[4]) {
                                 $this->_cfIDs[$cfID][$cID][2]['to'] = $p[2];
                                 $fnd = TRUE;
                             }
                         }
                         if (!$fnd) {
                             $p[2] = array('to' => $p[2]);
                             $this->_cfIDs[$cfID][] = $p;
                         }
                     }
                 }
             }
         }
     }
     if (!empty($this->_cfIDs)) {
         $this->_customQuery = new CRM_Core_BAO_CustomQuery($this->_cfIDs);
         $this->_customQuery->query();
         // Customquery hardcodes te relationship table to civicrm_relationship
         // We call this relationship, so we need to change this.
         $this->_customQuery->_tables = str_replace('civicrm_relationship', 'relationship', $this->_customQuery->_tables);
         $this->_customQuery->_whereTables = str_replace('civicrm_relationship', 'relationship', $this->_customQuery->_whereTables);
         $this->_select = array_merge($this->_select, $this->_customQuery->_select);
         $this->_element = array_merge($this->_element, $this->_customQuery->_element);
         $this->_tables = array_merge($this->_tables, $this->_customQuery->_tables);
         $this->_whereTables = array_merge($this->_whereTables, $this->_customQuery->_whereTables);
         $this->_options = $this->_customQuery->_options;
     }
 }