PHP DatabaseConnection::prepare_SELECTquery方法代码示例

 /**
  * @test
  *
  * @return void
  */
 public function prepareSelectQueryCreateValidQuery()
 {
     $this->assertTrue($this->subject->admin_query("INSERT INTO {$this->testTable} ({$this->testField}) VALUES ('aTestValue')"));
     $preparedQuery = $this->subject->prepare_SELECTquery("{$this->testField},{$this->anotherTestField}", $this->testTable, 'id=:id', '', '', '', [':id' => 1]);
     $preparedQuery->execute();
     $result = $preparedQuery->fetch();
     $expectedResult = [$this->testField => 'aTestValue', $this->anotherTestField => null];
     $this->assertSame($expectedResult, $result);
 }

 /**
  * Fetches the rows from the database, using prepared statement
  *
  * @param array $statementParts
  * @param array $parameters
  * @return array the result
  */
 protected function getRowsFromPreparedDatabase(array $statementParts, array $parameters)
 {
     $queryCommandParameters = $this->createQueryCommandParametersFromStatementParts($statementParts);
     $preparedStatement = $this->databaseHandle->prepare_SELECTquery($queryCommandParameters['selectFields'], $queryCommandParameters['fromTable'], $queryCommandParameters['whereClause'], '', $queryCommandParameters['orderBy'], $queryCommandParameters['limit']);
     $preparedStatement->execute($parameters);
     $rows = $preparedStatement->fetchAll();
     $preparedStatement->free();
     return $rows;
 }

    /**
     * The session_id is used to find user in the database.
     * Two tables are joined: The session-table with user_id of the session and the usertable with its primary key
     * if the client is flash (e.g. from a flash application inside TYPO3 that does a server request)
     * then don't evaluate with the hashLockClause, as the client/browser is included in this hash
     * and thus, the flash request would be rejected
     *
     * @return \TYPO3\CMS\Core\Database\PreparedStatement
     * @access private
     */
    protected function fetchUserSessionFromDB()
    {
        $statement = NULL;
        $ipLockClause = $this->ipLockClause();
        if ($GLOBALS['CLIENT']['BROWSER'] == 'flash') {
            // If on the flash client, the veri code is valid, then the user session is fetched
            // from the DB without the hashLock clause
            if (GeneralUtility::_GP('vC') == $this->veriCode()) {
                $statement = $this->db->prepare_SELECTquery('*', $this->session_table . ',' . $this->user_table, $this->session_table . '.ses_id = :ses_id
						AND ' . $this->session_table . '.ses_name = :ses_name
						AND ' . $this->session_table . '.ses_userid = ' . $this->user_table . '.' . $this->userid_column . '
						' . $ipLockClause['where'] . '
						' . $this->user_where_clause());
                $statement->bindValues(array(':ses_id' => $this->id, ':ses_name' => $this->name));
                $statement->bindValues($ipLockClause['parameters']);
            }
        } else {
            $statement = $this->db->prepare_SELECTquery('*', $this->session_table . ',' . $this->user_table, $this->session_table . '.ses_id = :ses_id
					AND ' . $this->session_table . '.ses_name = :ses_name
					AND ' . $this->session_table . '.ses_userid = ' . $this->user_table . '.' . $this->userid_column . '
					' . $ipLockClause['where'] . '
					' . $this->hashLockClause() . '
					' . $this->user_where_clause());
            $statement->bindValues(array(':ses_id' => $this->id, ':ses_name' => $this->name));
            $statement->bindValues($ipLockClause['parameters']);
        }
        return $statement;
    }

    /**
     * The session_id is used to find user in the database.
     * Two tables are joined: The session-table with user_id of the session and the usertable with its primary key
     * if the client is flash (e.g. from a flash application inside TYPO3 that does a server request)
     * then don't evaluate with the hashLockClause, as the client/browser is included in this hash
     * and thus, the flash request would be rejected
     *
     * @return \TYPO3\CMS\Core\Database\PreparedStatement
     * @access private
     */
    protected function fetchUserSessionFromDB()
    {
        $statement = null;
        $ipLockClause = $this->ipLockClause();
        $statement = $this->db->prepare_SELECTquery('*', $this->session_table . ',' . $this->user_table, $this->session_table . '.ses_id = :ses_id
					AND ' . $this->session_table . '.ses_name = :ses_name
					AND ' . $this->session_table . '.ses_userid = ' . $this->user_table . '.' . $this->userid_column . '
					' . $ipLockClause['where'] . '
					' . $this->hashLockClause() . '
					' . $this->user_where_clause());
        $statement->bindValues(array(':ses_id' => $this->id, ':ses_name' => $this->name));
        $statement->bindValues($ipLockClause['parameters']);
        return $statement;
    }

 /**
  * Method checks whether the given session collides with an existing one.
  * Checks if an associated speaker is associated to another session which:
  * - starts during the given session
  * - ends during the given session
  * - start at the same time as the given session
  * - ends at the same time as the given session
  * - surrounds the given session completely
  *
  * @param AbstractSession $session
  * @param array $exclude array of uids which should be exluded in the check (the given session is excluded by default)
  * @return array|false colliding sessions or false if no session collides
  * @throws \InvalidArgumentException
  */
 public function getCollidingSessions(AbstractSession $session, $exclude = [])
 {
     if (!is_array($exclude)) {
         throw new \InvalidArgumentException('$exclude is not of type array. should be an array of uids');
     }
     $speakers = $session->getSpeakers();
     // array holding named placeholder values
     $params = [];
     // helper in order to prepare a dynamic IN statement
     $inStmt = [];
     $i = 0;
     foreach ($speakers as $speaker) {
         /** @var \TYPO3\CMS\Extbase\Domain\Model\FrontendUser $speaker */
         // build unique placeholder name
         $placeholder = ':idref' . $i++;
         // assign the correct uid to the placeholder
         $params[$placeholder] = $speaker->getUid();
         // "store" the dynamic placeholder
         $inStmt[] = $placeholder . ' ';
     }
     // flatten the dynamic placeholders for later IN statement use
     $inStmt = implode(',', $inStmt);
     // helper for dynamic NOT IN statement
     if (count($exclude) > 0) {
         $i = 0;
         $excludeUids = array_merge([$session->getUid()], $exclude);
         $excludeInStmt = [];
         foreach ($excludeUids as $eUid) {
             // build unique placeholder name
             $placeholder = ':excludessessions' . $i++;
             // assign the correct uid to the placeholder
             $params[$placeholder] = $eUid;
             // store the dynamic placeholder for later
             $excludeInStmt[] = $placeholder;
         }
         // flatten dynamic placeholders for usage in IN statement
         $excludeInStmt = implode(', ', $excludeInStmt);
     }
     // set the rest of param values (respect DMBS datetime format)
     $params[':start'] = $session->getBegin()->format($this->dbDateTimeFormat);
     $params[':end'] = $session->getEnd()->format($this->dbDateTimeFormat);
     $params[':excludedsession'] = $session->getUid();
     $params[':scheduledtype'] = \TYPO3\Sessions\Domain\Model\ScheduledSession::class;
     $stmt = $this->db->prepare_SELECTquery(' DISTINCT tx_sessions_domain_model_session.uid AS uid ', ' tx_sessions_domain_model_session
             LEFT JOIN tx_sessions_session_record_mm AS srmm ON tx_sessions_domain_model_session.uid = srmm.uid_local AND srmm.tablenames = \'fe_users\'
             LEFT JOIN fe_users AS user ON srmm.uid_foreign = user.uid
         ', ' user.uid IN (' . $inStmt . ')
             AND (
                 /* this session starts while another session is running (start overlaps with other session) */
                 ( tx_sessions_domain_model_session.begin > :start AND tx_sessions_domain_model_session.begin < :end )
                 OR
                 /* this session ends while another session is running (end overlaps with other session) */
                 ( tx_sessions_domain_model_session.end > :start AND tx_sessions_domain_model_session.end < :end )
                 OR
                 /* this session starts at the same time */
                 tx_sessions_domain_model_session.begin = :start
                 OR
                 /* this session ends at the same time */
                 tx_sessions_domain_model_session.end = :end
                 OR
                 /* this session starts before and ends after */
                 (tx_sessions_domain_model_session.begin < :start AND tx_sessions_domain_model_session.end > :end)
             )
             AND tx_sessions_domain_model_session.uid ' . (isset($excludeInStmt) ? 'NOT IN(' . $excludeInStmt . ')' : '<> :excludedsession') . '
             AND tx_sessions_domain_model_session.type = :scheduledtype
             ' . \TYPO3\CMS\Backend\Utility\BackendUtility::BEenableFields('tx_sessions_domain_model_session') . '
         ', '', ' tx_sessions_domain_model_session.uid DESC ', '', $params);
     if ($stmt->execute() && $stmt->rowCount() > 0) {
         if ($stmt->rowCount() === 1) {
             $row = $stmt->fetch();
             $stmt->free();
             return [$this->sessionRepository->findByUid($row['uid'])];
         }
         $uids = [];
         while ($row = $stmt->fetch()) {
             $uids[] = $row['uid'];
         }
         $stmt->free();
         return $this->sessionRepository->findByUids($uids)->toArray();
     }
     return false;
 }