PHP DatabaseConnection::exec_SELECTquery方法代码示例

 /**
  * @param $uid
  * @param $parentUid
  */
 function validateParentUid($uid, $parentUid)
 {
     # Always validate for new forums.
     if ($uid == -1) {
         return;
     }
     $res = $this->databaseHandle->exec_SELECTquery('*', 'tx_mmforum_forums', 'parentID=' . intval($uid) . ' AND deleted=0 ' . $this->parent->getStoragePIDQuery());
     if ($this->databaseHandle->sql_num_rows($res) > 0 && $parentUid != 0) {
         $this->addErrorForField('parent', 'no-nested-forums', array($this->databaseHandle->sql_num_rows($res)));
     }
 }

 function loadFromDB($pid = -1)
 {
     $andWhere = '';
     if ($pid + 1) {
         $andWhere = ' AND pid=' . $pid;
     }
     $res = $this->databaseHandle->exec_SELECTquery('*', $this->getTableName(), 'uid=' . $this->getUid() . ' AND deleted=0 ' . $andWhere);
     if ($this->databaseHandle->sql_num_rows($res) == 0) {
         $this->data = null;
         $this->origData = array();
     } else {
         $this->data = $this->origData = $this->databaseHandle->sql_fetch_assoc($res);
     }
     $this->loaded = true;
 }

 /**
  * Fetches the speakers and returns them comma seperated
  * for displaying in Planning Module
  *
  * @param $uid int
  * @return string
  */
 public function getSpeakers($uid)
 {
     if (!\TYPO3\CMS\Core\Utility\MathUtility::canBeInterpretedAsInteger($uid)) {
         throw new \InvalidArgumentException('Param $uid must be an integer');
     }
     $res = $this->db->exec_SELECTquery('fe_users.username, fe_users.name', 'tx_sessions_session_record_mm
         LEFT JOIN fe_users ON tx_sessions_session_record_mm.uid_foreign = fe_users.uid', ' tx_sessions_session_record_mm.uid_local = ' . $uid . ' AND tx_sessions_session_record_mm.tablenames = \'fe_users\' ', '', ' tx_sessions_session_record_mm.sorting ASC ');
     if ($res === false) {
         return '';
     }
     $speakers = [];
     while ($row = $res->fetch_assoc()) {
         $speakers[] = empty($row['name']) ? $row['username'] : $row['name'];
     }
     return implode(', ', $speakers);
 }

 /**
  * Get a user from DB by social identifier
  *
  * @param string $identifier social identifier
  * @param string $extraWhere Additional WHERE clause: " AND ...
  * @param array $dbUserSetup User db table definition: $this->db_user
  * @return mixed User array or FALSE
  */
 public function fetchUserRecordByIdentifier($identifier, $extraWhere = '', $dbUserSetup = '')
 {
     $result = FALSE;
     $identityClassName = 'Portrino\\PxHybridAuth\\Domain\\Model\\Identity\\' . ucfirst($this->getServiceProvider()) . 'Identity';
     if (class_exists($identityClassName) && defined($identityClassName . '::EXTBASE_TYPE')) {
         $extbaseType = constant($identityClassName . '::EXTBASE_TYPE');
         $identityClause = 'deleted=0 AND hidden=0 AND identifier=' . $this->db->fullQuoteStr($identifier, 'tx_pxhybridauth_domain_model_identity') . ' AND ' . 'tx_extbase_type=' . $this->db->fullQuoteStr($extbaseType, 'tx_pxhybridauth_domain_model_identity');
         $socialIdentities = $this->db->exec_SELECTgetRows('*', 'tx_pxhybridauth_domain_model_identity', $identityClause);
         foreach ($socialIdentities as $socialIdentity) {
             if (isset($socialIdentity['fe_user'])) {
                 $dbUser = is_array($dbUserSetup) ? $dbUserSetup : $this->db_user;
                 // Look up the user by the username and/or extraWhere:
                 $dbres = $this->db->exec_SELECTquery('*', $dbUser['table'], 'uid' . '=' . $this->db->fullQuoteStr($socialIdentity['fe_user'], $dbUser['table']) . $this->db->fullQuoteStr($dbUser['check_pid_clause'], $dbUser['table']) . $dbUser['enable_clause'] . $extraWhere);
                 if ($dbres) {
                     $result = $this->db->sql_fetch_assoc($dbres);
                     $this->db->sql_free_result($dbres);
                     if ($result) {
                         break;
                     }
                 }
             }
         }
     }
     return $result;
 }

 /**
  * Determines if the current user may write in a certain topic.
  * @param  mixed   $topic The topic identifier. This may either be a topic UID pointing to
  *                        a record in the tx_mmforum_topics table or an associative array
  *                        already containing this record.
  * @return boolean        TRUE, if the user that is currently logged in may write in the
  *                        specified topic, otherwise FALSE.
  * @author Martin Helmich <m.helmich@mittwald.de>
  */
 function getMayWrite_topic($topic)
 {
     $userId = $this->getUserID();
     // If the $topic parameter is not an array, treat this parameter as a topic UID.
     if (!is_array($topic)) {
         $topic = intval($topic);
         // Look in the cache. In case of a hit, just return the result
         $cacheRes = $this->cache->restore('getMayWrite_topic_' . $topic . '_' . $userId);
         if ($cacheRes !== null) {
             return $cacheRes;
         }
         // Load the topic's forum UID
         $res = $this->databaseHandle->exec_SELECTquery('f.*', 'tx_mmforum_forums f, tx_mmforum_topics t', 't.uid="' . $topic . '" AND f.uid = t.forum_id');
         $arr = $this->databaseHandle->sql_fetch_assoc($res);
         $result = $this->getMayWrite_forum($arr);
         // Save the result to cache and return
         $this->cache->save('getMayWrite_topic_' . $topic . '_' . $userId, $result);
         return $result;
     } else {
         /* If the topic's forum UID is already known, just delegate to the
          * getMayWrite_forum function. Since the result of that function is
          * already being cached, there is no need to cache the result at this
          * place again. */
         return $this->getMayWrite_forum($topic['forum_id']);
     }
 }

 /**
  * Determines the user's rank by his/her post count.
  *
  * @author  Martin Helmich <m.helmich@mittwald.de>
  * @version 2007-06-06
  * @param   int   $post_count The user's post count.
  * @return  array             The regarding user rank as associative array.
  */
 function getRankByPostCount($post_count)
 {
     $res = $this->databaseHandle->exec_SELECTquery('*', 'tx_mmforum_ranks', 'minPosts <= ' . $post_count . ' AND deleted=0 AND hidden=0 AND special=0', '', 'minPosts DESC');
     if ($this->databaseHandle->sql_num_rows($res) == 0) {
         return 'error';
     } else {
         return $this->databaseHandle->sql_fetch_assoc($res);
     }
 }

 /**
  *
  * Retrievs a topic's forum UID.
  *
  * @author  Martin Helmich <m.helmich@mittwald.de>
  * @version 2007-07-21
  * @param   int $topic_uid The topic's UID
  * @return  int            The forum's UID
  */
 function getForumUIDByTopic($topic_uid)
 {
     $topic_uid = intval($topic_uid);
     $res = $this->databaseHandle->exec_SELECTquery('forum_id', 'tx_mmforum_topics', 'uid=' . $topic_uid . ' AND deleted=0');
     if ($this->databaseHandle->sql_num_rows($res) > 0) {
         list($forum_uid) = $this->databaseHandle->sql_fetch_row($res);
         return $forum_uid;
     } else {
         return false;
     }
 }

 /**
  * Check if there are still resources left for the process with the given id
  * Used to determine timeouts and to ensure a proper cleanup if there's a timeout
  *
  * @param  string  identification string for the process
  * @return boolean determines if the process is still active / has resources
  */
 function CLI_checkIfProcessIsActive($pid)
 {
     $ret = false;
     $this->db->sql_query('BEGIN');
     $res = $this->db->exec_SELECTquery('process_id,active,ttl', 'tx_crawler_process', 'process_id = \'' . $pid . '\'  AND deleted=0', '', 'ttl', '0,1');
     if ($row = $this->db->sql_fetch_assoc($res)) {
         $ret = intVal($row['active']) == 1;
     }
     $this->db->sql_query('COMMIT');
     return $ret;
 }

 /**
  * Update categories in flexforms
  *
  * @param string $pluginName
  * @param array $oldNewCategoryUidMapping
  * @param string $flexformField name of the flexform's field to look for
  * @return void
  */
 protected function updateFlexformCategories($pluginName, $oldNewCategoryUidMapping, $flexformField)
 {
     $count = 0;
     $title = 'Update flexforms categories (' . $pluginName . ':' . $flexformField . ')';
     $res = $this->databaseConnection->exec_SELECTquery('uid, pi_flexform', 'tt_content', 'CType=\'list\' AND list_type=\'' . $pluginName . '\' AND deleted=0');
     /** @var \TYPO3\CMS\Core\Configuration\FlexForm\FlexFormTools $flexformTools */
     $flexformTools = GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\Configuration\\FlexForm\\FlexFormTools');
     while ($row = $this->databaseConnection->sql_fetch_assoc($res)) {
         $status = null;
         $xmlArray = GeneralUtility::xml2array($row['pi_flexform']);
         if (!is_array($xmlArray) || !isset($xmlArray['data'])) {
             $status = FlashMessage::ERROR;
             $message = 'Flexform data of plugin "' . $pluginName . '" not found.';
         } elseif (!isset($xmlArray['data']['sDEF']['lDEF'])) {
             $status = FlashMessage::WARNING;
             $message = 'Flexform data of record tt_content:' . $row['uid'] . ' did not contain sheet: sDEF';
         } elseif (isset($xmlArray[$flexformField . '_updated'])) {
             $status = FlashMessage::NOTICE;
             $message = 'Flexform data of record tt_content:' . $row['uid'] . ' is already updated for ' . $flexformField . '. No update needed...';
         } else {
             // Some flexforms may have displayCond
             if (isset($xmlArray['data']['sDEF']['lDEF'][$flexformField]['vDEF'])) {
                 $updated = false;
                 $oldCategories = GeneralUtility::trimExplode(',', $xmlArray['data']['sDEF']['lDEF'][$flexformField]['vDEF'], true);
                 if (!empty($oldCategories)) {
                     $newCategories = array();
                     foreach ($oldCategories as $uid) {
                         if (isset($oldNewCategoryUidMapping[$uid])) {
                             $newCategories[] = $oldNewCategoryUidMapping[$uid];
                             $updated = true;
                         } else {
                             $status = FlashMessage::WARNING;
                             $message = 'The category ' . $uid . ' of record tt_content:' . $row['uid'] . ' was not found in sys_category records. Maybe the category was deleted before the migration? Please check manually...';
                         }
                     }
                     if ($updated) {
                         $count++;
                         $xmlArray[$flexformField . '_updated'] = 1;
                         $xmlArray['data']['sDEF']['lDEF'][$flexformField]['vDEF'] = implode(',', $newCategories);
                         $this->databaseConnection->exec_UPDATEquery('tt_content', 'uid=' . $row['uid'], array('pi_flexform' => $flexformTools->flexArray2Xml($xmlArray)));
                     }
                 }
             }
         }
         if ($status !== null) {
             $this->messageArray[] = array($status, $title, $message);
         }
     }
     $status = FlashMessage::INFO;
     $message = 'Updated ' . $count . ' tt_content flexforms for  "' . $pluginName . ':' . $flexformField . '"';
     $this->messageArray[] = array($status, $title, $message);
 }

    /**
     * Gets the latest posts from all public forums.
     *
     * @author  Martin Helmich <m.helmich@mittwald.de>
     * @return  array             An array containing all matching posts
     */
    function getPosts_all()
    {
        $res = $this->databaseHandle->exec_SELECTquery($this->selectFields, 'tx_mmforum_posts p
			 LEFT JOIN tx_mmforum_posts_text x ON x.post_id = p.uid
			 LEFT JOIN fe_users u ON u.uid = p.poster_id
			 LEFT JOIN tx_mmforum_topics t ON t.uid = p.topic_id
			 LEFT JOIN tx_mmforum_forums f ON t.forum_id = f.uid
			 LEFT JOIN tx_mmforum_forums c ON f.parentID = c.uid', 'p.deleted=0 AND
			 t.deleted=0 AND
			 f.deleted=0 ' . $this->pObj->getMayRead_forum_query('f') . $this->pObj->getMayRead_forum_query('c'), '', 'p.post_time DESC', $this->getPostNum());
        $results = array();
        while ($arr = $this->databaseHandle->sql_fetch_assoc($res)) {
            array_push($results, $arr);
        }
        return $results;
    }

 /**
  * Get array with markers from a complete form
  *
  * @return array
  */
 protected function getFieldMarkersFromForm()
 {
     $result = array();
     $select = 'f.marker, f.uid';
     $from = 'tx_powermail_domain_model_forms fo ' . 'LEFT JOIN tx_powermail_domain_model_pages p ON p.forms = fo.uid ' . 'LEFT JOIN tx_powermail_domain_model_fields f ON f.pages = p.uid';
     $where = 'fo.uid = ' . (int) $this->formUid . ' and f.deleted = 0';
     $groupBy = '';
     $orderBy = '';
     $limit = 1000;
     $res = $this->databaseConnection->exec_SELECTquery($select, $from, $where, $groupBy, $orderBy, $limit);
     if ($res) {
         while ($row = $this->databaseConnection->sql_fetch_assoc($res)) {
             $result['_' . $row['uid']] = $row['marker'];
         }
     }
     return $result;
 }

 /**
  * Search for users and returns usernames as result
  *
  * @param string $sword search string
  * @return array Array of usernames
  */
 public function search($sword)
 {
     $result = array();
     if (!$this->is_init) {
         $this->init();
     }
     if (!$this->validateName($this->field)) {
         return $result;
     }
     /** @see https://buzz.typo3.org/teams/security/article/correct-usage-of-typo3-database-api/ */
     $sword = '"' . $this->databaseHandle->escapeStrForLike($this->databaseHandle->quoteStr($sword, 'fe_users'), 'fe_users') . '%"';
     $res = $this->databaseHandle->exec_SELECTquery($this->field, 'fe_users', 'disable=0 AND deleted=0 AND ' . $this->field . ' LIKE ' . $sword . ' AND pid=' . $this->pid . ' AND FIND_IN_SET(' . $this->group_id . ', usergroup)', '', $this->field . ' ASC', '8');
     while (list($item) = $this->databaseHandle->sql_fetch_row($res)) {
         array_push($result, $item);
     }
     return $result;
 }

 /**
  * Update existing record
  *
  * @return int uid of updated record
  */
 protected function update()
 {
     // find existing record in database
     $searchterm = $this->databaseConnection->fullQuoteStr($this->getProperty($this->getUniqueField()), $this->getTable());
     $res = $this->databaseConnection->exec_SELECTquery('uid', $this->getTable(), $this->getUniqueField() . ' = ' . $searchterm . ' and deleted = 0 ' . $this->getAdditionalWhereClause(), '', '', 1);
     if ($res) {
         $row = $this->databaseConnection->sql_fetch_assoc($res);
     }
     // if there is no existing entry, insert new one
     if (empty($row['uid'])) {
         return $this->insert();
     }
     // update existing entry (only if mode is not "none")
     if ($this->getMode() !== 'none') {
         $this->databaseConnection->exec_UPDATEquery($this->getTable(), 'uid = ' . (int) $row['uid'], $this->getProperties());
     }
     return $row['uid'];
 }

 function get($data)
 {
     if (is_int($data)) {
         /* Load record from database */
         $res = $this->databaseHandle->exec_SELECTquery('*', 'tx_mmforum_userfields', 'uid=' . intval($data));
         if ($this->databaseHandle->sql_num_rows($res) == 0) {
             return null;
         }
         $arr = $this->databaseHandle->sql_fetch_assoc($res);
     } else {
         $arr = $data;
     }
     /* Unserialize array with meta information */
     $arr['meta'] = unserialize($arr['meta']);
     /* Parse configuration TypoScript */
     $parser =& $this->userLib->getTSParser();
     $parser->setup = array();
     $parser->parse($arr['config']);
     $arr['config_parsed'] = $parser->setup;
     /* Do some corrections for backwards compatibility */
     if (!$arr['meta']['label']['default']) {
         $arr['meta']['label']['default'] = $arr['label'];
     }
     if (!$arr['meta']['type']) {
         $arr['meta']['type'] = 'custom';
     }
     if (!$arr['meta']['link'] && $arr['config_parsed']['datasource']) {
         $arr['meta']['link'] = $arr['config_parsed']['datasource'];
     }
     if (!isset($arr['meta']['required']) && isset($arr['config_parsed']['required'])) {
         $arr['meta']['required'] = $arr['config_parsed']['required'] ? true : false;
     }
     if (!$arr['meta']['text']['validate']) {
         $arr['meta']['text']['validate'] = 'none';
     }
     if (!$arr['meta']['text']['length']) {
         $arr['meta']['text']['length'] = '-1';
     }
     $this->data = $arr;
     $this->meta =& $arr['meta'];
     $this->conf =& $arr['config_parsed'];
 }

 /**
  * Encrypt old bounce account passwords and preserve old default config
  *
  * @return string[]
  */
 private function getQueriesToEncryptOldBounceAccountPasswords()
 {
     // Fetch the old records - they will have a default port and an empty config.
     $rs = $this->databaseConnection->exec_SELECTquery('uid, password', 'tx_newsletter_domain_model_bounceaccount', 'port = 0 AND config = \'\'');
     $records = [];
     while ($record = $this->databaseConnection->sql_fetch_assoc($rs)) {
         $records[] = $record;
     }
     $this->databaseConnection->sql_free_result($rs);
     if (empty($records)) {
         return [];
     }
     // Keep the old config to not break old installations
     $config = Tools::encrypt("poll ###SERVER###\nproto ###PROTOCOL### \nusername \"###USERNAME###\"\npassword \"###PASSWORD###\"\n");
     $queries = [];
     foreach ($records as $record) {
         $queries[] = $this->databaseConnection->UPDATEquery('tx_newsletter_domain_model_bounceaccount', 'uid=' . intval($record['uid']), ['password' => Tools::encrypt($record['password']), 'config' => $config]);
     }
     return ['Encrypt bounce account passwords' => $queries];
 }