PHP DatabaseConnection::exec_SELECTgetRows方法代码示例

 /**
  * Migrate tt_news_categorymounts to category_pems in either be_groups or be_users
  *
  * @param string $table either be_groups or be_users
  */
 public function migrateTtNewsCategoryMountsToSysCategoryPerms($table)
 {
     /** @var \TYPO3\CMS\Core\DataHandling\DataHandler $dataHandler */
     $dataHandler = GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\DataHandling\\DataHandler');
     $dataHandler->admin = TRUE;
     /* assign imported categories to be_groups or be_users */
     $whereClause = 'tt_news_categorymounts != \'\'' . BackendUtility::deleteClause($table);
     $beGroupsOrUsersWithTtNewsCategorymounts = $this->databaseConnection->exec_SELECTgetRows('*', $table, $whereClause);
     $data = array();
     foreach ((array) $beGroupsOrUsersWithTtNewsCategorymounts as $beGroupOrUser) {
         $ttNewsCategoryPermissions = GeneralUtility::trimExplode(',', $beGroupOrUser['tt_news_categorymounts']);
         $sysCategoryPermissions = array();
         foreach ($ttNewsCategoryPermissions as $ttNewsCategoryPermissionUid) {
             $whereClause = 'import_source = \'TT_NEWS_CATEGORY_IMPORT\' AND import_id = ' . $ttNewsCategoryPermissionUid;
             $sysCategory = $this->databaseConnection->exec_SELECTgetSingleRow('uid', 'sys_category', $whereClause);
             if (!empty($sysCategory)) {
                 $sysCategoryPermissions[] = $sysCategory['uid'];
             }
         }
         if (count($sysCategoryPermissions)) {
             $data[$table][$beGroupOrUser['uid']] = array('category_perms' => implode(',', $sysCategoryPermissions) . ',' . $beGroupOrUser['category_perms']);
         }
     }
     $dataHandler->start($data, array());
     $dataHandler->process_datamap();
 }

 /**
  * @param \S3b0\ProjectRegistration\Scheduler\InfoMail\Task $task
  *
  * @return bool
  */
 public function run(\S3b0\ProjectRegistration\Scheduler\InfoMail\Task $task)
 {
     $settings = unserialize($GLOBALS['TYPO3_CONF_VARS']['EXT']['extConf'][$this->extensionName]);
     $upperLimit = new \DateTime();
     $lowerLimit = new \DateTime();
     $daysLeft = $settings['warnXDaysBeforeExpireDate'];
     $sender = [$task->getSenderAddress()];
     $receiver = [$task->getReceiverAddress()];
     $subject = \TYPO3\CMS\Extbase\Utility\LocalizationUtility::translate('infomail.subject', $this->extensionName);
     $this->databaseConnection = $GLOBALS['TYPO3_DB'];
     // Upper limit (expiry) = Current date + Days left
     $upperLimit->setTimestamp($upperLimit->getTimestamp() + $daysLeft * 86400);
     // Lower limit (expiry) = Current date + Days left - Scheduler frequency
     $lowerLimit->setTimestamp($lowerLimit->getTimestamp() + $daysLeft * 86400 - $task->getExecution()->getInterval());
     $where = "date_of_expiry > '{$lowerLimit->format('Y-m-d h:i:s')}' AND date_of_expiry < '{$upperLimit->format('Y-m-d h:i:s')}'";
     if ($this->databaseConnection->exec_SELECTcountRows('*', 'tx_projectregistration_domain_model_project', $where)) {
         $expiredProjects = $this->databaseConnection->exec_SELECTgetRows('project.*, registrant.name as registrant_name, registrant.company as registrant_company', 'tx_projectregistration_domain_model_project as project join tx_projectregistration_domain_model_person as registrant on project.registrant=registrant.uid', $where);
         $list = [];
         /** @var array $expiredProject */
         foreach ($expiredProjects as $expiredProject) {
             $list[] = "#{$expiredProject['uid']} - '{$expiredProject['title']}' by {$expiredProject['registrant_name']} ({$expiredProject['registrant_company']})";
         }
         $mailContent = \TYPO3\CMS\Extbase\Utility\LocalizationUtility::translate('infomail.message', $this->extensionName, [$daysLeft, '<li>' . implode('</li><li>', $list) . '</li>']);
         /** @var \TYPO3\CMS\Core\Mail\MailMessage $mail */
         $mail = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance(\TYPO3\CMS\Core\Mail\MailMessage::class);
         $mail->setContentType('text/html');
         /**
          * Email to sender
          */
         $mail->setFrom($sender)->setTo($receiver)->setPriority(1)->setSubject($subject)->setBody($mailContent)->send();
     }
     return true;
 }

 /**
  * @test
  */
 public function storedGzipCompressedDataReturnsSameData()
 {
     $testStringWithBinary = @gzcompress('sdfkljer4587');
     $this->fixture->exec_INSERTquery($this->testTable, array('fieldblob' => $testStringWithBinary));
     $id = $this->fixture->sql_insert_id();
     $entry = $this->fixture->exec_SELECTgetRows('fieldblob', $this->testTable, 'id = ' . $id);
     $this->assertEquals($testStringWithBinary, $entry[0]['fieldblob']);
 }

 /**
  * @test
  */
 public function addDataSetsPageLanguageOverlayRows()
 {
     $input = ['effectivePid' => '23'];
     $expected = $input;
     $expected['pageLanguageOverlayRows'] = [0 => ['uid' => '1', 'pid' => '42', 'sys_language_uid' => '2']];
     $this->dbProphecy->exec_SELECTgetRows('*', 'pages_language_overlay', 'pid=23')->shouldBeCalled()->willReturn($expected['pageLanguageOverlayRows']);
     $this->assertSame($expected, $this->subject->addData($input));
 }

 private function getImageRecordsOfFeedImport($feedImport)
 {
     $imageRows = $this->db->exec_SELECTgetRows('*', 'tx_gorillary_images', "deleted=0 AND feedimport='" . $feedImport['uid'] . "' AND pid='" . $feedImport['pid'] . "'");
     $imgArray = array();
     foreach ($imageRows as $imgRow) {
         $imgArray[$imgRow['image']] = $imgRow;
     }
     return $imgArray;
 }

 private function getImageRecordsOfCollection($collection)
 {
     $imageRows = $this->db->exec_SELECTgetRows('*', 'tx_gorillary_images', "deleted=0 AND collection='" . $collection['uid'] . "' AND pid='" . $collection['pid'] . "'");
     $imgArray = array();
     foreach ($imageRows as $imgRow) {
         $imgArray[$imgRow['image']] = $imgRow;
     }
     return $imgArray;
 }

 /**
  * Performs the database update.
  *
  * @param array &$dbQueries Queries done in this update
  * @param mixed &$customMessages Custom messages
  * @return boolean TRUE on success, FALSE on error
  */
 public function performUpdate(array &$dbQueries, &$customMessages)
 {
     $this->flexObj = GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\Configuration\\FlexForm\\FlexFormTools');
     $outdatedContentElements = $this->db->exec_SELECTgetRows('*', 'tt_content', $this->flexFormWhere);
     foreach ($outdatedContentElements as $outdatedContent) {
         $this->updateOutdatedContentFlexForm($outdatedContent, $dbQueries);
     }
     return TRUE;
 }

 /**
  * The actual update function. Add your update task in here.
  *
  * @return void
  */
 protected function processUpdates()
 {
     // Do the magic!
     $transferData = $this->databaseConnection->exec_SELECTgetRows('*', $this->transferTable, '1=1');
     if ($transferData && sizeof($transferData)) {
         $this->truncateTables();
         $this->addInitialStaticData();
         $this->transferData($transferData);
     }
 }

 /**
  * add cities to selectbox.
  *
  * @param array                              $parentArray
  * @param \TYPO3\CMS\Backend\Form\FormEngine $fObj
  */
 public function addCityItems(array $parentArray, \TYPO3\CMS\Backend\Form\FormEngine $fObj)
 {
     $this->init();
     $rows = $this->database->exec_SELECTgetRows('city', 'tx_clubdirectory_domain_model_address', '1=1 ' . BackendUtility::BEenableFields('tx_clubdirectory_domain_model_address') . BackendUtility::deleteClause('tx_clubdirectory_domain_model_address'), 'city', 'city', '');
     foreach ($rows as $row) {
         $item = array();
         $item[0] = $row['city'];
         $item[1] = $row['city'];
         $item[2] = null;
         $parentArray['items'][] = $item;
     }
 }

 /**
  * 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;
 }

 /**
  * Fetches the rows directly from the database, not using prepared statement
  *
  * @param array $statementParts
  * @return array the result
  */
 protected function getRowsFromDatabase(array $statementParts)
 {
     $queryCommandParameters = $this->createQueryCommandParametersFromStatementParts($statementParts);
     $rows = $this->databaseHandle->exec_SELECTgetRows($queryCommandParameters['selectFields'], $queryCommandParameters['fromTable'], $queryCommandParameters['whereClause'], '', $queryCommandParameters['orderBy'], $queryCommandParameters['limit']);
     $this->checkSqlErrors();
     return $rows;
 }

 /**
  * Removes active orphan processes from process list
  *
  * @return void
  */
 private function removeActiveOrphanProcesses()
 {
     $results = $this->db->exec_SELECTgetRows('process_id, system_process_id', 'tx_crawler_process', 'ttl <= ' . intval(time() - $this->extensionSettings['processMaxRunTime']) . ' AND active = 1');
     if (!is_array($results)) {
         return;
     }
     foreach ($results as $result) {
         $processExists = FALSE;
         $systemProcessId = (int) $result['system_process_id'];
         $processId = $result['process_id'];
         if ($systemProcessId > 1) {
             $dispatcherProcesses = $this->findDispatcherProcesses();
             if (!is_array($dispatcherProcesses) || empty($dispatcherProcesses)) {
                 $this->removeProcessFromProcesslist($processId);
                 return;
             }
             foreach ($dispatcherProcesses as $process) {
                 $responseArray = $this->createResponseArray($process);
                 if ($systemProcessId === (int) $responseArray[1]) {
                     $processExists = TRUE;
                 }
             }
             if (!$processExists) {
                 $this->removeProcessFromProcesslist($processId);
             }
         }
     }
 }

 /**
  * Make store control
  *
  * @return string
  */
 public function makeStoreControl()
 {
     // Load/Save
     $storeArray = $this->initStoreArray();
     $opt = array();
     foreach ($storeArray as $k => $v) {
         $opt[] = '<option value="' . $k . '">' . htmlspecialchars($v) . '</option>';
     }
     // Actions:
     if (ExtensionManagementUtility::isLoaded('sys_action') && $this->backendUserAuthentication->isAdmin()) {
         $rows = $this->databaseConnection->exec_SELECTgetRows('*', 'sys_action', 'type=2', '', 'title');
         $opt[] = '<option value="0">__Save to Action:__</option>';
         foreach ($rows as $row) {
             $opt[] = '<option value="-' . (int) $row['uid'] . '">' . htmlspecialchars($row['title'] . ' [' . (int) $row['uid'] . ']') . '</option>';
         }
     }
     $markup = [];
     $markup[] = '<div class="load-queries">';
     $markup[] = '  <div class="form-inline">';
     $markup[] = '    <div class="form-group">';
     $markup[] = '      <select class="form-control" name="storeControl[STORE]" onChange="document.forms[0]' . '[\'storeControl[title]\'].value= this.options[this.selectedIndex].value!=0 ' . '? this.options[this.selectedIndex].text : \'\';">' . implode(LF, $opt) . '</select>';
     $markup[] = '      <input class="form-control" name="storeControl[title]" value="" type="text" max="80">';
     $markup[] = '      <input class="btn btn-default" type="submit" name="storeControl[LOAD]" value="Load">';
     $markup[] = '      <input class="btn btn-default" type="submit" name="storeControl[SAVE]" value="Save">';
     $markup[] = '      <input class="btn btn-default" type="submit" name="storeControl[REMOVE]" value="Remove">';
     $markup[] = '    </div>';
     $markup[] = '  </div>';
     $markup[] = '</div>';
     return implode(LF, $markup);
 }

 /**
  * Creates file identifier hashes for a single storage.
  *
  * @param ResourceStorage $storage The storage to update
  * @return array The executed database queries
  */
 protected function updateIdentifierHashesForStorage(ResourceStorage $storage)
 {
     $queries = array();
     if (!ExtensionManagementUtility::isLoaded('dbal')) {
         // if DBAL is not loaded, we're using MySQL and can thus use their
         // SHA1() function
         if ($storage->usesCaseSensitiveIdentifiers()) {
             $updateCall = 'SHA1(identifier)';
         } else {
             $updateCall = 'SHA1(LOWER(identifier))';
         }
         $queries[] = $query = sprintf('UPDATE sys_file SET identifier_hash = %s WHERE storage=%d', $updateCall, $storage->getUid());
         $this->db->sql_query($query);
         // folder hashes cannot be done with one call: so do it manually
         $files = $this->db->exec_SELECTgetRows('uid, storage, identifier', 'sys_file', sprintf('storage=%d AND folder_hash=""', $storage->getUid()));
         foreach ($files as $file) {
             $folderHash = $storage->hashFileIdentifier($storage->getFolderIdentifierFromFileIdentifier($file['identifier']));
             $queries[] = $query = $this->db->UPDATEquery('sys_file', 'uid=' . $file['uid'], array('folder_hash' => $folderHash));
             $this->db->sql_query($query);
         }
     } else {
         // manually hash the identifiers when using DBAL
         $files = $this->db->exec_SELECTgetRows('uid, storage, identifier', 'sys_file', sprintf('storage=%d AND identifier_hash=""', $storage->getUid()));
         foreach ($files as $file) {
             $hash = $storage->hashFileIdentifier($file['identifier']);
             $folderHash = $storage->hashFileIdentifier($storage->getFolderIdentifierFromFileIdentifier($file['identifier']));
             $queries[] = $query = $this->db->UPDATEquery('sys_file', 'uid=' . $file['uid'], array('identifier_hash' => $hash, 'folder_hash' => $folderHash));
             $this->db->sql_query($query);
         }
     }
     return $queries;
 }

 /**
  * Migrate news_category.image (CSV) to sys_category.images (sys_file_reference)
  *
  * @return void
  */
 protected function migrateCategoryImages()
 {
     $oldCategories = $this->databaseConnection->exec_SELECTgetRows('uid, pid, image, migrate_sys_category_uid', 'tx_news_domain_model_category', 'deleted=0 AND image!=""');
     // no images to process then skip
     if (!count($oldCategories)) {
         return;
     }
     $processedImages = 0;
     foreach ($oldCategories as $oldCategory) {
         $files = \TYPO3\CMS\Core\Utility\GeneralUtility::trimExplode(',', $oldCategory['image'], true);
         $i = 0;
         foreach ($files as $file) {
             if (file_exists(PATH_site . 'uploads/tx_news/' . $file)) {
                 $fileObject = $this->getCategoryImageFolder()->addFile(PATH_site . 'uploads/tx_news/' . $file);
                 $dataArray = array('uid_local' => $fileObject->getUid(), 'tstamp' => $_SERVER['REQUEST_TIME'], 'crdate' => $_SERVER['REQUEST_TIME'], 'tablenames' => 'sys_category', 'uid_foreign' => $oldCategory['migrate_sys_category_uid'], 'pid' => $oldCategory['pid'], 'fieldname' => 'images', 'table_local' => 'sys_file', 'sorting_foreign' => $i);
                 $this->databaseConnection->exec_INSERTquery('sys_file_reference', $dataArray);
                 $processedImages++;
             }
             $i++;
         }
     }
     $message = 'Migrated ' . $processedImages . ' category images';
     $status = FlashMessage::INFO;
     $title = '';
     $this->messageArray[] = array($status, $title, $message);
 }