PHP DatabaseConnection::sql_free_result方法代码示例

 /**
  * Returns the object data using a custom statement
  *
  * @param Statement $statement
  * @return array
  */
 protected function getObjectDataByRawQuery(Statement $statement)
 {
     $realStatement = $statement->getStatement();
     $parameters = $statement->getBoundVariables();
     if ($realStatement instanceof \TYPO3\CMS\Core\Database\PreparedStatement) {
         $realStatement->execute($parameters);
         $rows = $realStatement->fetchAll();
         $realStatement->free();
     } else {
         /**
          * @deprecated since 6.2, this block will be removed in two versions
          * the deprecation log is in Qom\Statement
          */
         if (!empty($parameters)) {
             $this->replacePlaceholders($realStatement, $parameters);
         }
         $result = $this->databaseHandle->sql_query($realStatement);
         $this->checkSqlErrors();
         $rows = array();
         while ($row = $this->databaseHandle->sql_fetch_assoc($result)) {
             if (is_array($row)) {
                 $rows[] = $row;
             }
         }
         $this->databaseHandle->sql_free_result($result);
     }
     return $rows;
 }

 /**
  * Get column names
  *
  * @since 1.0.0
  *
  * @param $table
  *
  * @return array
  */
 protected function getColumnNames($table)
 {
     $table = preg_replace('/[^a-z0-9_]/', '', $table);
     if (isset($this->tableColumnCache[$table])) {
         return $this->tableColumnCache[$table];
     } else {
         $result = $this->databaseConnection->exec_SELECTgetSingleRow('*', $table, '1 = 1');
         if ($result) {
             $columnNames = array_keys($result);
             $this->tableColumnCache[$table] = $columnNames;
         } else {
             $columnNames = array();
             $result = $this->databaseConnection->sql_query('SELECT DATABASE();');
             $row = $this->databaseConnection->sql_fetch_row($result);
             $databaseName = $row[0];
             $this->databaseConnection->sql_free_result($result);
             $result = $this->databaseConnection->sql_query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" . $databaseName . "' AND TABLE_NAME = '" . $table . "';");
             while ($row = $this->databaseConnection->sql_fetch_row($result)) {
                 $columnNames[] = $row[0];
             }
             $this->databaseConnection->sql_free_result($result);
             $this->tableColumnCache[$table] = $columnNames;
         }
         return $columnNames;
     }
 }

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

 /**
  * news records got a relation to content elements and the relation uses now a mm query
  * This method allows to update the mm table to got everything in sync again
  *
  * @return void
  */
 protected function updateContentRelationToMm()
 {
     $title = 'Update tt_content relation';
     $countMmTable = $this->databaseConnection->exec_SELECTcountRows('*', 'tx_news_domain_model_news_ttcontent_mm', '1=1');
     $countContentElementRelation = $this->databaseConnection->exec_SELECTcountRows('*', 'tx_news_domain_model_news', 'deleted=0 AND content_elements != ""');
     if ($countMmTable === 0 && $countContentElementRelation > 0) {
         $newsCount = 0;
         $res = $this->databaseConnection->exec_SELECTquery('uid,content_elements', 'tx_news_domain_model_news', 'deleted=0 AND content_elements != ""');
         while ($row = $this->databaseConnection->sql_fetch_assoc($res)) {
             $newsCount++;
             $contentElementUids = explode(',', $row['content_elements']);
             $i = 1;
             foreach ($contentElementUids as $contentElement) {
                 // Insert mm relation
                 $insert = array('uid_local' => $row['uid'], 'uid_foreign' => $contentElement, 'sorting' => $i++);
                 $this->databaseConnection->exec_INSERTquery('tx_news_domain_model_news_ttcontent_mm', $insert);
             }
             // Update new record
             $update = array('content_elements' => count($contentElementUids));
             $this->databaseConnection->exec_UPDATEquery('tx_news_domain_model_news', 'uid=' . $row['uid'], $update);
         }
         $this->databaseConnection->sql_free_result($res);
         $this->messageArray[] = array(FlashMessage::OK, $title, $newsCount . ' news records have been updated!');
     } else {
         $this->messageArray[] = array(FlashMessage::NOTICE, $title, 'Not needed/possible anymore as the mm table is already filled!');
     }
 }

 /**
  * Migrate dam references to fal references
  *
  * @param \mysqli_result $result
  * @param string $table
  * @param string $type
  * @param array $fieldnameMapping Re-map fieldnames e.g.
  *    tx_damnews_dam_images => tx_falttnews_fal_images
  *
  * @return void
  */
 protected function migrateDamReferencesToFalReferences($result, $table, $type, $fieldnameMapping = array())
 {
     $counter = 0;
     $total = $this->database->sql_num_rows($result);
     $this->controller->infoMessage('Found ' . $total . ' ' . $table . ' records with a dam ' . $type);
     while ($record = $this->database->sql_fetch_assoc($result)) {
         $identifier = $this->getFullFileName($record);
         try {
             $fileObject = $this->storageObject->getFile($identifier);
         } catch (\Exception $e) {
             // If file is not found
             // getFile will throw an invalidArgumentException if the file
             // does not exist. Create an empty file to avoid this. This is
             // usefull in a development environment that has the production
             // database but not all the physical files.
             try {
                 GeneralUtility::mkdir_deep(PATH_site . $this->storageBasePath . dirname($identifier));
             } catch (\Exception $e) {
                 $this->controller->errorMessage('Unable to create directory: ' . PATH_site . $this->storageBasePath . $identifier);
                 continue;
             }
             $config = $this->controller->getConfiguration();
             if (isset($config['createMissingFiles']) && (int) $config['createMissingFiles']) {
                 $this->controller->infoMessage('Creating empty missing file: ' . PATH_site . $this->storageBasePath . $identifier);
                 try {
                     GeneralUtility::writeFile(PATH_site . $this->storageBasePath . $identifier, '');
                 } catch (\Exception $e) {
                     $this->controller->errorMessage('Unable to create file: ' . PATH_site . $this->storageBasePath . $identifier);
                     continue;
                 }
             } else {
                 $this->controller->errorMessage('File not found: ' . PATH_site . $this->storageBasePath . $identifier);
                 continue;
             }
             $fileObject = $this->storageObject->getFile($identifier);
         }
         if ($fileObject instanceof \TYPO3\CMS\Core\Resource\File) {
             if ($fileObject->isMissing()) {
                 $this->controller->warningMessage('FAL did not find any file resource for DAM record. DAM uid: ' . $record['uid'] . ': "' . $identifier . '"');
                 continue;
             }
             $record['uid_local'] = $fileObject->getUid();
             foreach ($fieldnameMapping as $old => $new) {
                 if ($record['ident'] === $old) {
                     $record['ident'] = $new;
                 }
             }
             $progress = number_format(100 * ($counter++ / $total), 1) . '% of ' . $total;
             if (!$this->doesFileReferenceExist($record)) {
                 $insertData = array('tstamp' => time(), 'crdate' => time(), 'cruser_id' => $GLOBALS['BE_USER']->user['uid'], 'uid_local' => $record['uid_local'], 'uid_foreign' => (int) $record['uid_foreign'], 'sorting' => (int) $record['sorting'], 'sorting_foreign' => (int) $record['sorting_foreign'], 'tablenames' => (string) $record['tablenames'], 'fieldname' => (string) $record['ident'], 'table_local' => 'sys_file', 'pid' => $record['item_pid'], 'l10n_diffsource' => (string) $record['l18n_diffsource']);
                 $this->database->exec_INSERTquery('sys_file_reference', $insertData);
                 $this->amountOfMigratedRecords++;
                 $this->controller->message($progress . ' Migrating relation for ' . (string) $record['tablenames'] . ' uid: ' . $record['item_uid'] . ' dam uid: ' . $record['dam_uid'] . ' to fal uid: ' . $record['uid_local']);
             } else {
                 $this->controller->message($progress . ' Reference already exists for uid: ' . (int) $record['item_uid']);
             }
         }
     }
     $this->database->sql_free_result($result);
 }

 function getConfigurationsForBranch($rootid, $depth)
 {
     $configurationsForBranch = array();
     $pageTSconfig = $this->getPageTSconfigForId($rootid);
     if (is_array($pageTSconfig) && is_array($pageTSconfig['tx_crawler.']['crawlerCfg.']) && is_array($pageTSconfig['tx_crawler.']['crawlerCfg.']['paramSets.'])) {
         $sets = $pageTSconfig['tx_crawler.']['crawlerCfg.']['paramSets.'];
         if (is_array($sets)) {
             foreach ($sets as $key => $value) {
                 if (!is_array($value)) {
                     continue;
                 }
                 $configurationsForBranch[] = substr($key, -1) == '.' ? substr($key, 0, -1) : $key;
             }
         }
     }
     $pids = array();
     $rootLine = \TYPO3\CMS\Backend\Utility\BackendUtility::BEgetRootLine($rootid);
     foreach ($rootLine as $node) {
         $pids[] = $node['uid'];
     }
     /* @var \TYPO3\CMS\Backend\Tree\View\PageTreeView */
     $tree = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance('TYPO3\\CMS\\Backend\\Tree\\View\\PageTreeView');
     $perms_clause = $GLOBALS['BE_USER']->getPagePermsClause(1);
     $tree->init('AND ' . $perms_clause);
     $tree->getTree($rootid, $depth, '');
     foreach ($tree->tree as $node) {
         $pids[] = $node['row']['uid'];
     }
     $res = $this->db->exec_SELECTquery('*', 'tx_crawler_configuration', 'pid IN (' . implode(',', $pids) . ') ' . \TYPO3\CMS\Backend\Utility\BackendUtility::BEenableFields('tx_crawler_configuration') . \TYPO3\CMS\Backend\Utility\BackendUtility::deleteClause('tx_crawler_configuration') . ' ' . \TYPO3\CMS\Backend\Utility\BackendUtility::versioningPlaceholderClause('tx_crawler_configuration') . ' ');
     while ($row = $this->db->sql_fetch_assoc($res)) {
         $configurationsForBranch[] = $row['name'];
     }
     $this->db->sql_free_result($res);
     return $configurationsForBranch;
 }

 /**
  * @return array
  */
 public function fetchAll()
 {
     $rows = array();
     while (($a_row = $this->connection->sql_fetch_assoc($this->resource)) == true) {
         $rows[] = $a_row;
     }
     $this->connection->sql_free_result($this->resource);
     return $rows;
 }

 /**
  * Fetches the UIDs of all maximal versions for all extensions.
  * This is done by doing a LEFT JOIN to itself ("a" and "b") and comparing
  * both integer_version fields.
  *
  * @param int $repositoryUid
  * @return array
  */
 protected function fetchMaximalVersionsForAllExtensions($repositoryUid)
 {
     $queryResult = $this->databaseConnection->sql_query('SELECT a.uid AS uid ' . 'FROM ' . self::TABLE_NAME . ' a ' . 'LEFT JOIN ' . self::TABLE_NAME . ' b ON a.repository = b.repository AND a.extension_key = b.extension_key AND a.integer_version < b.integer_version ' . 'WHERE a.repository = ' . (int) $repositoryUid . ' AND b.extension_key IS NULL ' . 'ORDER BY a.uid');
     $extensionUids = array();
     while ($row = $this->databaseConnection->sql_fetch_assoc($queryResult)) {
         $extensionUids[] = $row['uid'];
     }
     $this->databaseConnection->sql_free_result($queryResult);
     return $extensionUids;
 }

 /**
  * Find the affected page ids by going through all the flexforms of all
  * active fal gallery content elements and checking if the current folder
  * is contained in the settings folder.
  *
  * @param Folder $folder The folder to check
  *
  * @return array
  */
 protected function getAffectedPageIds(Folder $folder)
 {
     $pids = array();
     if ($folder->getStorage()->getDriverType() === 'Local') {
         $res = $this->databaseConnection->sql_query("\n\t\t\t\tSELECT\n\t\t\t\t\tpid,\n\t\t\t\t\tExtractValue(pi_flexform, '/T3FlexForms/data/sheet[@index=''list'']/language/field[@index=''settings.default.folder'']/value') as folder\n\t\t\t\tFROM\n\t\t\t\t\ttt_content\n\t\t\t\tWHERE\n\t\t\t\t\tlist_type = 'falgallery_pi1'\n\t\t\t\t\tAND deleted = 0\n\t\t\t\t\tAND hidden = 0\n\t\t\t\t\tAND ExtractValue(pi_flexform, '/T3FlexForms/data/sheet[@index=''list'']/language/field[@index=''settings.default.folder'']/value') LIKE 'file:" . $folder->getCombinedIdentifier() . "%'");
         while ($row = $this->databaseConnection->sql_fetch_assoc($res)) {
             $pids[] = $row['pid'];
         }
         $this->databaseConnection->sql_free_result($res);
     }
     return $pids;
 }

 /**
  * DB select object by id
  *
  * @param int $uid Uid
  * @param Tx_Commerce_Dao_BasicDaoObject $object Object
  *
  * @return void
  */
 protected function dbSelectById($uid, Tx_Commerce_Dao_BasicDaoObject &$object)
 {
     $dbFields = '*';
     $dbTable = $this->dbTable;
     $dbWhere = 'uid = ' . (int) $uid;
     $dbWhere .= 'AND deleted = 0';
     // execute query
     $res = $this->database->exec_SELECTquery($dbFields, $dbTable, $dbWhere);
     // insert into object
     $model = $this->database->sql_fetch_assoc($res);
     if ($model) {
         // parse into object
         $this->parser->parseModelToObject($model, $object);
     } else {
         // no object found, empty obj and id
         $object->clear();
     }
     // free results
     $this->database->sql_free_result($res);
 }

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

 /**
  * Returns the object data using a custom statement
  *
  * @param Qom\Statement $statement
  * @return array
  */
 protected function getObjectDataByRawQuery(Qom\Statement $statement)
 {
     $realStatement = $statement->getStatement();
     $parameters = $statement->getBoundVariables();
     if ($realStatement instanceof \TYPO3\CMS\Core\Database\PreparedStatement) {
         $realStatement->execute($parameters);
         $rows = $realStatement->fetchAll();
         $realStatement->free();
     } else {
         $result = $this->databaseHandle->sql_query($realStatement);
         $this->checkSqlErrors();
         $rows = array();
         while ($row = $this->databaseHandle->sql_fetch_assoc($result)) {
             if (is_array($row)) {
                 $rows[] = $row;
             }
         }
         $this->databaseHandle->sql_free_result($result);
     }
     return $rows;
 }

 /**
  * Returns the number of tuples matching the query.
  *
  * @throws \TYPO3\CMS\Extbase\Persistence\Generic\Storage\Exception\BadConstraintException
  * @return int The number of matching tuples
  */
 public function countResult()
 {
     $parameters = array();
     $statementParts = $this->parseQuery($this->query, $parameters);
     $statementParts = $this->processStatementStructureForRecursiveMMRelation($statementParts);
     // Mmm... check if that is the right way of doing that.
     // Reset $statementParts for valid table return
     reset($statementParts);
     // if limit is set, we need to count the rows "manually" as COUNT(*) ignores LIMIT constraints
     if (!empty($statementParts['limit'])) {
         $statement = $this->buildQuery($statementParts);
         $this->replacePlaceholders($statement, $parameters, current($statementParts['tables']));
         #print $statement; exit(); // @debug
         $result = $this->databaseHandle->sql_query($statement);
         $this->checkSqlErrors($statement);
         $count = $this->databaseHandle->sql_num_rows($result);
     } else {
         $statementParts['fields'] = array('COUNT(*)');
         // having orderings without grouping is not compatible with non-MySQL DBMS
         $statementParts['orderings'] = array();
         if (isset($statementParts['keywords']['distinct'])) {
             unset($statementParts['keywords']['distinct']);
             $distinctField = $this->query->getDistinct() ? $this->query->getDistinct() : 'uid';
             $statementParts['fields'] = array('COUNT(DISTINCT ' . reset($statementParts['tables']) . '.' . $distinctField . ')');
         }
         $statement = $this->buildQuery($statementParts);
         $this->replacePlaceholders($statement, $parameters, current($statementParts['tables']));
         #print $statement; exit(); // @debug
         $result = $this->databaseHandle->sql_query($statement);
         $this->checkSqlErrors($statement);
         $count = 0;
         if ($result) {
             $row = $this->databaseHandle->sql_fetch_assoc($result);
             $count = current($row);
         }
     }
     $this->databaseHandle->sql_free_result($result);
     return (int) $count;
 }

    /**
     * @param \GeorgRinger\News\Domain\Model\News $news
     * @param $pidList
     * @param $sortField
     * @return array
     */
    protected function getNeighbours(\GeorgRinger\News\Domain\Model\News $news, $pidList, $sortField)
    {
        $pidList = empty($pidList) ? $news->getPid() : $pidList;
        $select = 'SELECT tx_news_domain_model_news.uid,tx_news_domain_model_news.title ';
        $from = 'FROM tx_news_domain_model_news';
        $whereClause = 'tx_news_domain_model_news.pid IN(' . $this->databaseConnection->cleanIntList($pidList) . ') ' . $this->getEnableFieldsWhereClauseForTable();
        $query = $select . $from . '
					WHERE ' . $whereClause . ' && ' . $sortField . ' >= (SELECT MAX(' . $sortField . ')
						' . $from . '
					WHERE ' . $whereClause . ' AND ' . $sortField . ' < (SELECT ' . $sortField . '
						FROM tx_news_domain_model_news
						WHERE tx_news_domain_model_news.uid = ' . $news->getUid() . '))
					ORDER BY ' . $sortField . ' ASC
					LIMIT 3';
        $query2 = $select . $from . '
			WHERE ' . $whereClause . ' AND ' . $sortField . '= (SELECT MIN(' . $sortField . ')
				FROM tx_news_domain_model_news
				WHERE ' . $whereClause . ' AND ' . $sortField . ' >
					(SELECT ' . $sortField . '
					FROM tx_news_domain_model_news
					WHERE tx_news_domain_model_news.uid = ' . $news->getUid() . '))
			';
        $res = $this->databaseConnection->sql_query($query);
        $out = array();
        while ($row = $this->databaseConnection->sql_fetch_assoc($res)) {
            $out[] = $row;
        }
        $this->databaseConnection->sql_free_result($res);
        if (count($out) === 0) {
            $res = $this->databaseConnection->sql_query($query2);
            while ($row = $this->databaseConnection->sql_fetch_assoc($res)) {
                $out[] = $row;
            }
            $this->databaseConnection->sql_free_result($res);
            return $out;
        }
        return $out;
    }

 /**
  * Will select all records from the "category table", $table, and return them in an array.
  *
  * @param string $table The name of the category table to select from.
  * @param int $pid The page from where to select the category records.
  * @param string $whereClause Optional additional WHERE clauses put in the end of the query. DO NOT PUT IN GROUP BY, ORDER BY or LIMIT!
  * @param string $groupBy Optional GROUP BY field(s), if none, supply blank string.
  * @param string $orderBy Optional ORDER BY field(s), if none, supply blank string.
  * @param string $limit Optional LIMIT value ([begin,]max), if none, supply blank string.
  * @return array The array with the category records in.
  */
 public function pi_getCategoryTableContents($table, $pid, $whereClause = '', $groupBy = '', $orderBy = '', $limit = '')
 {
     $res = $this->databaseConnection->exec_SELECTquery('*', $table, 'pid=' . (int) $pid . $this->cObj->enableFields($table) . ' ' . $whereClause, $groupBy, $orderBy, $limit);
     $outArr = array();
     while ($row = $this->databaseConnection->sql_fetch_assoc($res)) {
         $outArr[$row['uid']] = $row;
     }
     $this->databaseConnection->sql_free_result($res);
     return $outArr;
 }