PHP Db::fetchAll方法代码示例

 protected function loadSettings()
 {
     $sql = "SELECT `setting_name`, `setting_value` FROM " . $this->getTableName() . " WHERE idsite = ?";
     $bind = array($this->idSite);
     $settings = $this->db->fetchAll($sql, $bind);
     $flat = array();
     foreach ($settings as $setting) {
         $flat[$setting['setting_name']] = unserialize($setting['setting_value']);
     }
     return $flat;
 }

 public static function getUsersBySite($idsite)
 {
     $getRegularUsers = Db::fetchAll("SELECT login,\n        (SELECT email FROM " . Common::prefixTable('user') . " WHERE login = acc.login) AS email\n        FROM " . Common::prefixTable('access') . " AS acc WHERE idsite = ?", array($idsite));
     $getSuperUsers = Db::fetchAll("SELECT login,email FROM " . Common::prefixTable('user') . " WHERE superuser_access = 1");
     $getUsers = array_merge($getRegularUsers, $getSuperUsers);
     return $getUsers;
 }

 private function assertDuplicatesFixedInLogConversionItemTable()
 {
     $columns = array('idaction_sku', 'idaction_name', 'idaction_category', 'idaction_category2', 'idaction_category3', 'idaction_category4', 'idaction_category5');
     $rows = Db::fetchAll("SELECT " . implode(',', $columns) . " FROM " . Common::prefixTable('log_conversion_item'));
     $expectedRows = array(array('idaction_sku' => '1', 'idaction_name' => '1', 'idaction_category' => '1', 'idaction_category2' => '4', 'idaction_category3' => '5', 'idaction_category4' => '6', 'idaction_category5' => '5'), array('idaction_sku' => '1', 'idaction_name' => '1', 'idaction_category' => '5', 'idaction_category2' => '5', 'idaction_category3' => '8', 'idaction_category4' => '4', 'idaction_category5' => '6'));
     $this->assertEquals($expectedRows, $rows);
 }

 public function test_delete_ConvertsIdActionsToInt()
 {
     $this->actionsAccess->delete(array("2", "0, 1"));
     $expectedActions = array(array('name' => 'action1'), array('name' => 'action3'));
     $actualActions = Db::fetchAll("SELECT name FROM " . Common::prefixTable('log_action'));
     $this->assertEquals($expectedActions, $actualActions);
 }

 /**
  * Analyzes numeric & blob tables for a single table date (ie, `'2015_01'`) and returns
  * statistics including:
  *
  * - number of archives present
  * - number of invalidated archives
  * - number of temporary archives
  * - number of error archives
  * - number of segment archives
  * - number of numeric rows
  * - number of blob rows
  *
  * @param string $tableDate ie `'2015_01'`
  * @return array
  */
 public function getArchiveTableAnalysis($tableDate)
 {
     $numericQueryEmptyRow = array('count_archives' => '-', 'count_invalidated_archives' => '-', 'count_temporary_archives' => '-', 'count_error_archives' => '-', 'count_segment_archives' => '-', 'count_numeric_rows' => '-');
     $tableDate = str_replace("`", "", $tableDate);
     // for sanity
     $numericTable = Common::prefixTable("archive_numeric_{$tableDate}");
     $blobTable = Common::prefixTable("archive_blob_{$tableDate}");
     // query numeric table
     $sql = "SELECT CONCAT_WS('.', idsite, date1, date2, period) AS label,\n                       SUM(CASE WHEN name LIKE 'done%' THEN 1 ELSE 0 END) AS count_archives,\n                       SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_invalidated_archives,\n                       SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_temporary_archives,\n                       SUM(CASE WHEN name LIKE 'done%' AND value = ? THEN 1 ELSE 0 END) AS count_error_archives,\n                       SUM(CASE WHEN name LIKE 'done%' AND CHAR_LENGTH(name) > 32 THEN 1 ELSE 0 END) AS count_segment_archives,\n                       SUM(CASE WHEN name NOT LIKE 'done%' THEN 1 ELSE 0 END) AS count_numeric_rows,\n                       0 AS count_blob_rows\n                  FROM `{$numericTable}`\n              GROUP BY idsite, date1, date2, period";
     $rows = Db::fetchAll($sql, array(ArchiveWriter::DONE_INVALIDATED, ArchiveWriter::DONE_OK_TEMPORARY, ArchiveWriter::DONE_ERROR));
     // index result
     $result = array();
     foreach ($rows as $row) {
         $result[$row['label']] = $row;
     }
     // query blob table & manually merge results (no FULL OUTER JOIN in mysql)
     $sql = "SELECT CONCAT_WS('.', idsite, date1, date2, period) AS label,\n                       COUNT(*) AS count_blob_rows\n                  FROM `{$blobTable}`\n              GROUP BY idsite, date1, date1, period";
     foreach (Db::fetchAll($sql) as $blobStatsRow) {
         $label = $blobStatsRow['label'];
         if (isset($result[$label])) {
             $result[$label] = array_merge($result[$label], $blobStatsRow);
         } else {
             $result[$label] = $blobStatsRow + $numericQueryEmptyRow;
         }
     }
     return $result;
 }

 public function test_fixDuplicateActionsInTable_CorrectlyUpdatesIdActionColumns_InSpecifiedTable()
 {
     $this->duplicateActionRemover->fixDuplicateActionsInTable('log_conversion_item', 5, array(3, 6, 7, 10));
     $columns = array('idaction_sku', 'idaction_name', 'idaction_category', 'idaction_category2', 'idaction_category3', 'idaction_category4', 'idaction_category5');
     $expectedResult = array(array('idaction_sku' => '1', 'idaction_name' => '2', 'idaction_category' => '5', 'idaction_category2' => '4', 'idaction_category3' => '5', 'idaction_category4' => '5', 'idaction_category5' => '5'), array('idaction_sku' => '2', 'idaction_name' => '5', 'idaction_category' => '5', 'idaction_category2' => '5', 'idaction_category3' => '8', 'idaction_category4' => '9', 'idaction_category5' => '5'));
     $actualResult = Db::fetchAll("SELECT " . implode(", ", $columns) . " FROM " . Common::prefixTable('log_conversion_item'));
     $this->assertEquals($expectedResult, $actualResult);
 }

 private function getLdapUserLogins()
 {
     $rows = Db::fetchAll("SELECT login from " . Common::prefixTable('user') . " WHERE password LIKE '{LDAP}%'");
     $result = array();
     foreach ($rows as $row) {
         $result[] = $row['login'];
     }
     return $result;
 }

 /**
  * Returns the list of all the website IDs registered.
  * Caller must check access.
  *
  * @return array The list of website IDs
  */
 public function getSitesId()
 {
     $result = Db::fetchAll("SELECT idsite FROM " . Common::prefixTable('site'));
     $idSites = array();
     foreach ($result as $idSite) {
         $idSites[] = $idSite['idsite'];
     }
     return $idSites;
 }

 public static function getUnreadConversations($login)
 {
     $conversations = array();
     $rows = Db::fetchAll("SELECT idvisitor, lastviewed, lastsent FROM " . Common::prefixTable('chat_history_admin') . " WHERE login = ? AND lastsent > lastviewed", array($login));
     $rows = ChatCommon::formatRows($rows);
     foreach ($rows as $row) {
         $conversations[$row['idvisitor']] = $row;
     }
     return $conversations;
 }

 public static function setUpBeforeClass()
 {
     parent::setUpBeforeClass();
     // add duplicates for every action
     $table = Common::prefixTable('log_action');
     foreach (Db::fetchAll("SELECT * FROM {$table}") as $row) {
         $insertSql = "INSERT INTO {$table} (name, type, hash, url_prefix)\n                               VALUES (?, ?, CRC32(?), ?)";
         Db::query($insertSql, array($row['name'], $row['type'], $row['name'], $row['url_prefix']));
     }
 }

 /**
  * Returns the list of column names for a table.
  *
  * @param string $table Prefixed table name.
  * @return string[] List of column names..
  */
 public function getColumns($table)
 {
     $table = str_replace("`", "", $table);
     $columns = Db::fetchAll("SHOW COLUMNS FROM `" . $table . "`");
     $columnNames = array();
     foreach ($columns as $column) {
         $columnNames[] = $column['Field'];
     }
     return $columnNames;
 }

 protected static function getTemporaryArchiveIdsOlderThan(Date $date, $purgeArchivesOlderThan)
 {
     $query = "SELECT idarchive\n                FROM " . ArchiveTableCreator::getNumericTable($date) . "\n                WHERE name LIKE 'done%'\n                    AND ((  value = " . ArchiveWriter::DONE_OK_TEMPORARY . "\n                            AND ts_archived < ?)\n                         OR value = " . ArchiveWriter::DONE_ERROR . ")";
     $result = Db::fetchAll($query, array($purgeArchivesOlderThan));
     $idArchivesToDelete = array();
     if (!empty($result)) {
         foreach ($result as $row) {
             $idArchivesToDelete[] = $row['idarchive'];
         }
     }
     return $idArchivesToDelete;
 }

 public function getArchiveIdAndVisits($numericTable, $idSite, $period, $dateStartIso, $dateEndIso, $minDatetimeIsoArchiveProcessedUTC, $doneFlags, $possibleValues)
 {
     $bindSQL = array($idSite, $dateStartIso, $dateEndIso, $period);
     $timeStampWhere = '';
     if ($minDatetimeIsoArchiveProcessedUTC) {
         $timeStampWhere = " AND ts_archived >= ? ";
         $bindSQL[] = $minDatetimeIsoArchiveProcessedUTC;
     }
     $sqlWhereArchiveName = self::getNameCondition($doneFlags, $possibleValues);
     $sqlQuery = "SELECT idarchive, value, name, date1 as startDate FROM {$numericTable}\n                     WHERE idsite = ?\n                         AND date1 = ?\n                         AND date2 = ?\n                         AND period = ?\n                         AND ( ({$sqlWhereArchiveName})\n                               OR name = '" . ArchiveSelector::NB_VISITS_RECORD_LOOKED_UP . "'\n                               OR name = '" . ArchiveSelector::NB_VISITS_CONVERTED_RECORD_LOOKED_UP . "')\n                         {$timeStampWhere}\n                     ORDER BY idarchive DESC";
     $results = Db::fetchAll($sqlQuery, $bindSQL);
     return $results;
 }

 static function update()
 {
     try {
         $dashboards = Db::fetchAll('SELECT * FROM `' . Common::prefixTable('user_dashboard') . '`');
         foreach ($dashboards as $dashboard) {
             $idDashboard = $dashboard['iddashboard'];
             $login = $dashboard['login'];
             $layout = $dashboard['layout'];
             $layout = html_entity_decode($layout);
             $layout = str_replace("\\\"", "\"", $layout);
             Db::query('UPDATE `' . Common::prefixTable('user_dashboard') . '` SET layout = ? WHERE iddashboard = ? AND login = ?', array($layout, $idDashboard, $login));
         }
         Updater::updateDatabase(__FILE__, self::getSql());
     } catch (\Exception $e) {
     }
 }

 public function doUpdate(Updater $updater)
 {
     try {
         $dashboards = Db::fetchAll('SELECT * FROM `' . Common::prefixTable('user_dashboard') . '`');
         foreach ($dashboards as $dashboard) {
             $idDashboard = $dashboard['iddashboard'];
             $login = $dashboard['login'];
             $layout = $dashboard['layout'];
             $layout = html_entity_decode($layout);
             $layout = str_replace("\\\"", "\"", $layout);
             Db::query('UPDATE `' . Common::prefixTable('user_dashboard') . '` SET layout = ? WHERE iddashboard = ? AND login = ?', array($layout, $idDashboard, $login));
         }
         $updater->executeMigrationQueries(__FILE__, $this->getMigrationQueries($updater));
     } catch (\Exception $e) {
     }
 }