PHP Connection::fetchAll方法代码示例

 /**
  * @param QueryInterface $query
  * @return array
  */
 public function fetch(QueryInterface $query)
 {
     $query->checkReplacements();
     $plainQuery = $query->getPlainQuery();
     list($parameters, $plainQuery) = $this->modifyParametersFromArrayToScalar($query->getParameters(), $plainQuery);
     return $this->connection->fetchAll($plainQuery, $parameters);
 }

 protected function getRecentFrequent(User $user, array $criteria = null, $limit = 10)
 {
     $sql = '
         SELECT text, MAX(createdAt) AS recency, COUNT(*) AS frequency
         FROM stress_log_factors
         JOIN stress_logs ON stress_log_id = stress_logs.id
         WHERE user_id = ?
     ';
     $params = array($user->getId());
     if (is_array($criteria)) {
         foreach ($criteria as $key => $val) {
             $op = '=';
             if (is_array($val)) {
                 reset($val);
                 $op = key($val);
                 $val = current($val);
             }
             $sql .= 'AND `' . $key . '` ' . $op . ' ? ';
             $params[] = $val;
         }
     }
     $sql .= 'GROUP BY text ';
     $sql .= 'ORDER BY recency DESC, frequency DESC ';
     $sql .= 'LIMIT ' . (int) $limit;
     $result = $this->conn->fetchAll($sql, $params);
     return array_column($result, 'text');
 }

 /**
  * Получить продукты по ID категории
  *
  * @param $categoryId ID категории
  *
  * @return array
  */
 public function getProductsByCategoryId($categoryId)
 {
     return $this->db->fetchAll('SELECT `products`.* FROM `products`
             INNER JOIN `products_categories`
                 ON `products`.`id` = `products_categories`.`product_id`
         WHERE `products_categories`.`category_id` = ?', array($categoryId));
 }

 public function exportData($export, $reporter)
 {
     $this->conn->beginTransaction();
     try {
         $lastExportedAt = (int) $export['lastExportedAt'];
         $areas = $this->conn->fetchAll('SELECT a.`id`, a.`name`, t.`id` AS `territoryId`, t.`name` AS `territoryName`, a.`customData`, a.`lastUpdatedAt` ' . 'FROM `' . CoreTables::AREA_TBL . '` a ' . 'INNER JOIN `' . CoreTables::TERRITORY_TBL . '` t ON t.`id` = a.`territoryId` ' . 'WHERE a.`projectId` = :projectId AND a.`statusId` = :statusId', [':projectId' => $export['projectId'], ':statusId' => $export['areaStatusId']]);
         $block = new ExportBlock();
         foreach ($areas as $area) {
             $block->addId($area['id']);
             if ($area['lastUpdatedAt'] > $lastExportedAt) {
                 $area['customData'] = json_decode($area['customData']);
                 $block->addUpdatedId($area['id']);
                 $block->addUpdate($area);
             }
         }
         $event = new ExportEvent($export['projectId'], $export['lastExportedAt'], $reporter);
         $event->addBlock('area', $block);
         $event = $this->eventDispatcher->dispatch(ExportEvents::EXPORT_ONGOING, $event);
         $this->conn->executeQuery('UPDATE `' . ExportTables::DATA_EXPORT_TBL . '` SET `lastExportedAt` = :time WHERE `id` = :id', [':time' => time(), ':id' => $export['id']]);
         $this->conn->commit();
         return $event->output();
     } catch (Exception $ex) {
         $this->conn->rollBack();
         throw $ex;
     }
 }

 public function findAll()
 {
     $result = $this->db->fetchAll("select * from " . $this->table . ";");
     $event = new GenericEvent($result);
     $this->eventDispatcher->dispatch("post-findall-" . $this->entityName, $event);
     return $event->getSubject();
 }

 /**
  * Dispatches magic methods (findBy[Property]())
  *
  * @param string $methodName The name of the magic method
  * @param string $arguments The arguments of the magic method
  * @throws \Exception
  * @return mixed
  * @api
  */
 public function __call($methodName, $arguments)
 {
     if (substr($methodName, 0, 6) === 'findBy' && strlen($methodName) > 7) {
         $propertyName = lcfirst(substr($methodName, 6));
         $sql = "select * from " . $this->tableName . " where " . $propertyName . " = :propertyValue order by id desc";
         $result = $this->db->fetchAll($sql, array('propertyValue' => $arguments[0]));
         // Convert query result to an array of objects
         $objectList = array();
         foreach ($result as $row) {
             $id = $row['id'];
             $objectList[$id] = $this->buildDomainObject($row);
         }
         return $objectList;
     } elseif (substr($methodName, 0, 9) === 'findOneBy' && strlen($methodName) > 10) {
         $propertyName = lcfirst(substr($methodName, 9));
         $sql = "select * from " . $this->tableName . " where id = :id";
         $row = $this->db->fetchAssoc($sql, array('id' => $arguments[0]));
         if ($row) {
             return $this->buildDomainObject($row);
         } else {
             throw new \Exception("No " . $this->objectName . " matching  " . $propertyName . " " . $arguments[0]);
         }
     } elseif (substr($methodName, 0, 7) === 'countBy' && strlen($methodName) > 8) {
         $propertyName = lcfirst(substr($methodName, 7));
         $sql = "select COUNT(id) from " . $this->tableName . " where " . $propertyName . " = :propertyValue order by id desc";
         $result = current($this->db->fetchAssoc($sql, array('propertyValue' => $arguments[0])));
         return $result;
     }
     throw new \Exception('The method "' . $methodName . '" is not supported by the repository.', 1233180480);
 }

 public function indexAction()
 {
     //        $this->init($app);
     $posts = $this->db->fetchAll('SELECT * FROM post');
     //var_dump($posts);
     return $this->twig->render('post/index.twig', array('posts' => $posts));
 }

 /**
  * @param LoggerInterface $logger
  * @param bool $dryRun
  */
 protected function doExecute(LoggerInterface $logger, $dryRun = false)
 {
     $duplicateEntitiesQuery = 'SELECT
             DISTINCT t2.id
         FROM
             orocrm_campaign_email_stats AS t1
         LEFT JOIN orocrm_campaign_email_stats AS t2
             ON t1.email_campaign_id = t2.email_campaign_id
             AND t1.marketing_list_item_id = t2.marketing_list_item_id
             AND t2.id > t1.id
         WHERE t2.id IS NOT NULL';
     // Done in 2 queries for cross DB support.
     $idsToRemove = array_map(function ($item) {
         if (is_array($item) && array_key_exists('id', $item)) {
             return $item['id'];
         }
         return null;
     }, $this->connection->fetchAll($duplicateEntitiesQuery));
     if ($idsToRemove) {
         $query = 'DELETE FROM orocrm_campaign_email_stats WHERE id IN (?)';
         $logger->notice($query);
         if (!$dryRun) {
             $this->connection->executeQuery($query, [$idsToRemove], [Connection::PARAM_INT_ARRAY]);
         }
     }
 }

 public function collectData(IdentifiableInterface $root)
 {
     $routes = $this->conn->fetchAll('SELECT `routeAscent` ' . 'FROM `' . EdkTables::ROUTE_TBL . '` r ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON r.`areaId` = a.`id` ' . 'WHERE a.`projectId` = :projectId AND r.`routeType` = 0', [':projectId' => $root->getId()]);
     if (sizeof($routes) == 0) {
         return false;
     }
     foreach ($routes as &$row) {
         $row['normalized'] = $this->step($row['routeAscent']);
     }
     $this->data = [];
     $min = 10000;
     $max = 0;
     foreach ($routes as $row) {
         if ($row['normalized'] < $min) {
             $min = $row['normalized'];
         }
         if ($row['normalized'] > $max) {
             $max = $row['normalized'];
         }
     }
     if ($min >= $max) {
         return false;
     }
     for ($i = $min; $i <= $max; $i++) {
         $this->data[$i] = 0;
     }
     foreach ($routes as $row) {
         $this->data[$row['normalized']]++;
     }
     return true;
 }

 /**
  * Returns all records from this repository's table
  *
  * @param integer $limit
  *
  * @return array
  */
 public function findAll($limit = null)
 {
     if (null === $limit) {
         return $this->db->fetchAll(sprintf('SELECT * FROM %s', $this->getTableName()));
     }
     return $this->db->fetchAll(sprintf('SELECT * FROM %s LIMIT %d', $this->getTableName(), $limit));
 }

 /**
  * @param $messageHandlerId
  * @return array of task data
  */
 public function findTasksOfMessageHandler($messageHandlerId)
 {
     $tasks = $this->connection->fetchAll('SELECT * FROM ' . Tables::TASK . ' WHERE message_handler_id = :mhid', ['mhid' => $messageHandlerId]);
     foreach ($tasks as &$task) {
         $this->fromDatabase($task);
     }
     return $tasks;
 }

 /**
  * @return Exercise[]
  */
 public function getAll()
 {
     $exercises = [];
     foreach ($this->connection->fetchAll('SELECT id, title, description FROM exercises') as $data) {
         $exercises[] = new Exercise($data['id'], $data['title'], $data['description']);
     }
     return $exercises;
 }

 private function getTableNames()
 {
     $tableNames = [];
     foreach ($this->connection->fetchAll('SHOW TABLES') as $row) {
         $tableNames[] = $row['Tables_in_' . $this->connection->getDatabase()];
     }
     return $tableNames;
 }

 /**
  * @param string $organisation
  * @param string $project
  *
  * @return array
  */
 public function getProjects($organisation, $project)
 {
     if (false === empty($project)) {
         return [$project];
     }
     $projects = $this->connection->fetchAll('SELECT project FROM events WHERE organisation = ? GROUP BY project', [$organisation]);
     return array_column($projects, 'project');
 }

 /**
  * @param Contract $streamContract
  * @param Identifier $streamId
  * @return EventEnvelope[]
  */
 public function fetch(Contract $streamContract, Identifier $streamId)
 {
     $records = $this->connection->fetchAll(Query\Select::from(self::TABLE_NAME), ['streamContract' => $streamContract, 'streamId' => $streamId]);
     $eventEnvelopes = array_map(function (array $record) {
         return EventEnvelope::reconstitute(EventId::fromString($record['eventId']), Contract::with($record['eventContract']), $record['eventPayload']);
     }, $records);
     return $eventEnvelopes;
 }