PHP DatabaseConnection::sql_fetch_assoc方法代码示例

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

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

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

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

    /**
     * Displays a statistic grouped by the time of day.
     *
     * @return  string The statistic table
     * @author  Martin Helmich <m.helmich@mittwald.de>
     * @version 2007-05-31
     */
    function stat_timeOfDay()
    {
        $tstamp_Start = $this->param['start'];
        $tstamp_Stop = $this->param['stop'];
        $table = $this->param['table'];
        $timeField = $this->timeFields[$table];
        $tstamp_Span = $tstamp_Stop - $tstamp_Start;
        $hours = $tstamp_Span / 60 / 60;
        $results = array();
        if ($this->param['mode'] == 'total') {
            $tTstamp_Stop = $tstamp_Start;
            if ($this->param['month'] == 'all') {
                return '<br /><br />' . $this->getLL('error.noMonth') . '<br /><br />';
            }
            if ($this->param['day'] == 'all') {
                return '<br /><br />' . $this->getLL('error.noDay') . '<br /><br />';
            }
            if ($this->param['year'] == 'all') {
                return '<br /><br />' . $this->getLL('error.noYear') . '<br /><br />';
            }
            $maxValue = 0;
            for ($i = 0; $i < 24; $i++) {
                $tTstamp_Stop += 3600 * $i;
                if ($tTstamp_Stop > $GLOBALS['EXEC_TIME']) {
                    $results[$i] = 0;
                    continue;
                }
                $res = $this->databaseHandle->exec_SELECTquery('COUNT(*)', $table, "{$timeField} <= {$tTstamp_Stop} AND deleted=0");
                list($num) = $this->databaseHandle->sql_fetch_row($res);
                $results[$i] = $num;
                if ($num > $maxValue) {
                    $maxValue = $num;
                }
            }
        } else {
            $res = $this->databaseHandle->exec_SELECTquery('*', $table, "{$timeField} >= {$tstamp_Start} AND {$timeField} <= {$tstamp_Stop} AND deleted=0");
            $maxValue = 0;
            while ($arr = $this->databaseHandle->sql_fetch_assoc($res)) {
                $hour = intval(date("H", $arr[$timeField]));
                $results[$hour]++;
                if ($results[$hour] > $maxValue) {
                    $maxValue = $results[$hour];
                }
            }
        }
        $content = $this->getDateTitleLink($tstamp_Start, $tstamp_Stop, 4, 0);
        for ($i = 0; $i < 24; $i++) {
            $result = intval($results[$i]);
            $avg = round($result / $hours, 2);
            $width = $maxValue ? round($result / $maxValue * 100, 2) : 0;
            $color = $i % 2 ? '#e00000' : '#ff0000';
            $content .= '<tr class="mm_forum-listrow' . ($i % 2 ? '2' : '') . '">
	<td style="width:1px; white-space:nowrap; text-align:right; padding:2px;">' . $i . ':00</td><td style="width:1px; white-space:nowrap;">&mdash; ' . $i . ':59</td>
	<td style="width:1px; padding:2px;">' . $result . '</td>
	<td><div style="background-color: ' . $color . '; width:' . $width . '%; height:14px; border-right: 1px solid #660000; border-bottom: 1px solid #660000; border-top: 1px solid #ff6666;">&nbsp;</div></td>
</tr>';
        }
        $content .= '</table>';
        return $content;
    }

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

 /**
  *
  * Reads all user groups into an array.
  * @return array An array containing information on all feuser groups. The
  *               array follows the pattern [Group UID]=>[Group name]
  *
  */
 function feGroups2Array()
 {
     $ug = array();
     $res = $this->databaseHandle->exec_SELECTquery('uid,title', 'fe_groups', 'hidden=0 and deleted=0', '', 'uid asc');
     while ($row = $this->databaseHandle->sql_fetch_assoc($res)) {
         $ug[$row['uid']] = $row['title'];
     }
     return $ug;
 }

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

 /**
  *
  * Generates an HTML <OPTION>-List that contains all toplevel boards.
  *
  * @param  Integer $selectedUid The UID of the forum that is to be marked as
  *                              preselected
  * @return String               HTML content
  */
 function getForumSelectOptionList($selectedUid = NULL)
 {
     $content = '';
     $res = $this->databaseHandle->exec_SELECTquery('*', 'tx_mmforum_forums', 'deleted=0 AND parentID=0 ' . $this->p->getStoragePIDQuery(), '', 'sorting ASC');
     while ($arr = $this->databaseHandle->sql_fetch_assoc($res)) {
         $selected = $arr['uid'] == $selectedUid;
         $content .= '<option value="' . $arr['uid'] . '" ' . ($selected ? 'selected="selected"' : '') . '>' . $this->validator->specialChars($arr['forum_name']) . '</option>';
     }
     return $content;
 }

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

 /**
  * @test
  *
  * @return void
  */
 public function sqlFetchAssocReturnsAssocArray()
 {
     $this->prepareTableForFetchTests();
     $res = $this->subject->admin_query("SELECT * FROM {$this->testTable} ORDER BY id");
     $expectedResult = [['id' => '1', $this->testField => null, $this->anotherTestField => null, 'name' => 'Mr. Smith', 'deleted' => '0', 'street' => 'Oakland Road', 'city' => 'Los Angeles', 'country' => 'USA'], ['id' => '2', $this->testField => null, $this->anotherTestField => null, 'name' => 'Ms. Smith', 'deleted' => '0', 'street' => 'Oakland Road', 'city' => 'Los Angeles', 'country' => 'USA'], ['id' => '3', $this->testField => null, $this->anotherTestField => null, 'name' => 'Alice im Wunderland', 'deleted' => '0', 'street' => 'Große Straße', 'city' => 'Königreich der Herzen', 'country' => 'Wunderland'], ['id' => '4', $this->testField => null, $this->anotherTestField => null, 'name' => 'Agent Smith', 'deleted' => '1', 'street' => 'Unbekannt', 'city' => 'Unbekannt', 'country' => 'Matrix']];
     $i = 0;
     while ($row = $this->subject->sql_fetch_assoc($res)) {
         $this->assertSame($expectedResult[$i], $row);
         $i++;
     }
 }

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

 /**
  * Retrieves the default character set of the database.
  *
  * @return string
  */
 protected function getDefaultDatabaseCharset()
 {
     $result = $this->databaseConnection->admin_query('SHOW VARIABLES LIKE "character_set_database"');
     $row = $this->databaseConnection->sql_fetch_assoc($result);
     $key = $row['Variable_name'];
     $value = $row['Value'];
     $databaseCharset = '';
     if ($key == 'character_set_database') {
         $databaseCharset = $value;
     }
     return $databaseCharset;
 }

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

 /**
  * Transforms a Resource from a database query to an array of rows.
  *
  * @param resource $result The result
  * @return array The result as an array of rows (tuples)
  */
 protected function getRowsFromResult($result)
 {
     $rows = array();
     while ($row = $this->databaseHandle->sql_fetch_assoc($result)) {
         if (is_array($row)) {
             // Get language uid from querySettings.
             // Ensure the backend handling is not broken (fallback to Get parameter 'L' if needed)
             $overlaidRow = $this->doLanguageAndWorkspaceOverlay($this->query->getSource(), $row, $this->query->getQuerySettings());
             $contentObject = GeneralUtility::makeInstance($this->objectType, $this->query->getType(), $overlaidRow);
             $rows[] = $contentObject;
         }
     }
     return $rows;
 }