PHP Connection::prepare方法代码示例

 /**
  * @test
  */
 public function it_saves_and_reads()
 {
     $schema = new Schema();
     SnapshotStoreSchema::create($schema, 'foo_snapshot');
     foreach ($schema->toSql($this->connection->getDatabasePlatform()) as $sql) {
         $this->connection->executeQuery($sql);
     }
     $adapter = new DoctrineSnapshotAdapter($this->connection);
     $aggregateType = AggregateType::fromString('foo');
     $aggregateRoot = new \stdClass();
     $aggregateRoot->foo = 'bar';
     $time = microtime(true);
     if (false === strpos($time, '.')) {
         $time .= '.0000';
     }
     $now = \DateTimeImmutable::createFromFormat('U.u', $time);
     $snapshot = new Snapshot($aggregateType, 'id', $aggregateRoot, 1, $now);
     $adapter->save($snapshot);
     $snapshot = new Snapshot($aggregateType, 'id', $aggregateRoot, 2, $now);
     $adapter->save($snapshot);
     $this->assertNull($adapter->get($aggregateType, 'invalid'));
     $readSnapshot = $adapter->get($aggregateType, 'id');
     $this->assertEquals($snapshot, $readSnapshot);
     $statement = $this->connection->prepare('SELECT * FROM foo_snapshot');
     $statement->execute();
     $snapshots = $statement->fetchAll();
     $this->assertCount(1, $snapshots);
 }

 public function restorePortfolioTitle()
 {
     $totalPortfolioProcessed = 0;
     $nbPortfolioProcessed = 0;
     if ($this->connection->getSchemaManager()->tablesExist(array('icap__portfolio_widget_title'))) {
         $this->log('Restoring portfolio titles...');
         $rowPortfolioTitles = $this->connection->query('SELECT * FROM icap__portfolio_widget_title');
         $sql = 'SELECT aw.id, aw.user_id FROM icap__portfolio_abstract_widget aw WHERE id = :id';
         $stmt = $this->connection->prepare($sql);
         foreach ($rowPortfolioTitles as $rowPortfolioTitle) {
             $stmt->bindValue('id', $rowPortfolioTitle['id']);
             $stmt->execute();
             foreach ($stmt->fetchAll() as $rowAbstractWidget) {
                 $this->connection->update('icap__portfolio', ['title' => $rowPortfolioTitle['title'], 'slug' => $rowPortfolioTitle['slug']], ['id' => $rowAbstractWidget['user_id']]);
             }
             $this->connection->delete('icap__portfolio_abstract_widget', ['id' => $rowPortfolioTitle['id']]);
             ++$nbPortfolioProcessed;
             if ($nbPortfolioProcessed >= 10) {
                 $totalPortfolioProcessed += $nbPortfolioProcessed;
                 $nbPortfolioProcessed = 0;
                 $this->log('    processing portfolio...');
             }
         }
         $this->log(sprintf('  %d portfolio processed', $totalPortfolioProcessed + $nbPortfolioProcessed));
         $this->connection->delete('icap__portfolio_widget_type', ['name' => 'title']);
         $this->connection->getSchemaManager()->dropTable('icap__portfolio_widget_title');
     }
 }

 private function applyChanges($areas, $rules, $reverseRules)
 {
     $stmt = $this->conn->prepare('UPDATE `' . CoreTables::AREA_TBL . '` SET `statusId` = :statusId, `lastUpdatedAt` = :lastUpdated WHERE `id` = :id');
     $count = 0;
     foreach ($areas as $area) {
         foreach ($rules as $rule) {
             if ($this->isMatching($area, $rule)) {
                 $stmt->bindValue(':id', $area['id']);
                 $stmt->bindValue(':statusId', $rule['newStatusId']);
                 $stmt->bindValue(':lastUpdated', time());
                 $stmt->execute();
                 $count++;
                 continue 2;
             }
         }
         foreach ($reverseRules as $rule) {
             if ($this->isNotMatching($area, $rule)) {
                 $stmt->bindValue(':id', $area['id']);
                 $stmt->bindValue(':statusId', $rule['prevStatusId']);
                 $stmt->bindValue(':lastUpdated', time());
                 $stmt->execute();
                 $count++;
                 continue 2;
             }
         }
     }
     return $count;
 }

 /**
  * @param $todoId
  * @return null|\stdClass containing userData
  */
 public function findUserOfTodo($todoId)
 {
     $stmt = $this->connection->prepare(sprintf("SELECT u.* FROM %s as u JOIN %s as t ON u.id = t.assignee_id  where t.id = :todo_id", Table::USER, Table::TODO));
     $stmt->bindValue('todo_id', $todoId);
     $stmt->execute();
     return $stmt->fetch();
 }

 /**
  * @param Backlog[] $backlogs
  */
 private function writeBacklog(array $backlogs)
 {
     $statement = $this->connection->prepare("\n            INSERT IGNORE INTO s_es_backlog (`event`, `payload`, `time`)\n            VALUES (:event, :payload, :time);\n        ");
     foreach ($backlogs as $backlog) {
         $statement->execute([':event' => $backlog->getEvent(), ':payload' => json_encode($backlog->getPayload()), ':time' => $backlog->getTime()->format('Y-m-d H:i:s')]);
     }
 }

 protected function buildOptions(ReportParameter $parameter)
 {
     $options = array();
     if ($parameter->getType() == ReportParameter::TYPE_QUERY) {
         $stmt = $this->connection->prepare($parameter->getChoices());
         $stmt->execute();
         $results = $stmt->fetchAll();
         $values = array(null => '');
         $keySet = array_keys(current($results));
         $key = current(array_slice($keySet, 0, 1));
         $value = current(array_slice($keySet, 1, 1));
         foreach ($results as $result) {
             $values[$result[$value]] = $result[$key];
         }
         $options = array_merge($options, array('choices' => $values));
         $parameter->setType(ChoiceType::class);
     }
     $normalizer = new \Symfony\Component\Serializer\Normalizer\GetSetMethodNormalizer();
     $normalizer->setIgnoredAttributes(array('id', 'name', 'type', 'created', 'modified', 'active'));
     $serializer = new \Symfony\Component\Serializer\Serializer(array($normalizer));
     $options = array_merge($serializer->normalize($parameter), $options);
     foreach ($options as $key => $value) {
         if (is_null($value)) {
             unset($options[$key]);
         }
     }
     $this->handleConstraints($options);
     return $options;
 }

 /**
  * @param $userId
  * @return null|\stdClass containing userData
  */
 public function findById($userId)
 {
     $stmt = $this->connection->prepare(sprintf("SELECT * FROM %s where id = :user_id", Table::USER));
     $stmt->bindValue('user_id', $userId);
     $stmt->execute();
     return $stmt->fetch();
 }

 /**
  * @param string $todoId
  * @return \stdClass of todoData
  */
 public function findById($todoId)
 {
     $stmt = $this->connection->prepare(sprintf("SELECT * FROM %s where id = :todo_id", Table::TODO));
     $stmt->bindValue('todo_id', $todoId);
     $stmt->execute();
     return $stmt->fetch();
 }

 /**
  * Execute
  */
 public function execute(array $criteria = null)
 {
     $stmt = $this->connection->prepare($this->buildQuery());
     $stmt->execute(array('mds' => $criteria['start_date']->format('m/d/Y'), 'mde' => $criteria['end_date']->format('m/d/Y')));
     $results = $stmt->fetchAll();
     $stmt->closeCursor();
     return $results;
 }

 /**
  * {@inheritDoc}
  */
 public function getStreamIds()
 {
     $statement = $this->connection->prepare('SELECT DISTINCT uuid FROM ' . config('broadway.event-store.table'));
     $statement->execute();
     return array_map(function ($row) {
         return $row['uuid'];
     }, $statement->fetchAll());
 }

 /**
  * @return ReadModelInterface[]
  */
 public function findAll()
 {
     $statement = $this->connection->prepare(sprintf('SELECT serialized FROM %s WHERE class = :class', static::TABLE));
     $statement->execute(['class' => $this->class]);
     return array_map(function ($row) {
         return $this->serializer->deserialize(json_decode($row['serialized'], true));
     }, $statement->fetchAll(\PDO::FETCH_ASSOC));
 }

 /**
  * Execute
  */
 public function execute(array $criteria = null)
 {
     $stmt = $this->connection->prepare($this->buildQuery());
     $stmt->execute(array('first_id' => $criteria['first_id'], 'last_id' => $criteria['last_id']));
     $results = $stmt->fetchAll();
     $stmt->closeCursor();
     return $results;
 }

 public function __construct(Connection $con)
 {
     $this->con = $con;
     $this->platform = $con->getDatabasePlatform();
     $this->phpType = Type::getType(PhpTypeType::NAME);
     $this->fStmt = $con->prepare(self::INSERT_FUNC_SQL);
     $this->mStmt = $con->prepare(self::INSERT_METHOD_SQL);
 }

 public function getCategoryPaintings($catId)
 {
     $sql = 'SELECT * FROM pinturas where categorias_pintura_id = :catId';
     $stmt = $this->db->prepare($sql);
     $stmt->bindValue('catId', $catId);
     $stmt->execute();
     return $stmt->fetchAll();
 }

 public function getCategoryPerformances($catId)
 {
     $sql = 'SELECT * FROM teatros where categorias_teatro_id = :catId';
     $stmt = $this->db->prepare($sql);
     $stmt->bindValue('catId', $catId);
     $stmt->execute();
     return $stmt->fetchAll();
 }