PHP Select::prepareStatement方法代码示例

 /**
  * @param AdapterInterface $adapter
  * @param StatementContainerInterface $statementContainer
  */
 public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
 {
     // localize variables
     foreach (get_object_vars($this->select) as $name => $value) {
         $this->{$name} = $value;
     }
     parent::prepareStatement($adapter, $statementContainer);
 }

 /**
  * @covers Zend\Db\Sql\Select::prepareStatement
  */
 public function testPrepareStatement()
 {
     $mockDriver = $this->getMock('Zend\\Db\\Adapter\\Driver\\DriverInterface');
     $mockAdapter = $this->getMock('Zend\\Db\\Adapter\\Adapter', null, array($mockDriver));
     $mockStatement = $this->getMock('Zend\\Db\\Adapter\\Driver\\StatementInterface');
     $mockStatement->expects($this->at(0))->method('setSql')->with($this->equalTo('SELECT "bee", "baz", "zac".* FROM "foo" INNER JOIN "zac" ON "m" = "n"'));
     $mockStatement->expects($this->at(3))->method('setSql')->with($this->equalTo(' WHERE x = y'));
     $this->select->from('foo')->columns(array('bee', 'baz'))->join('zac', 'm = n')->where('x = y');
     $this->select->prepareStatement($mockAdapter, $mockStatement);
 }

 /**
  * @param AdapterInterface $adapter
  * @param StatementContainerInterface $statementContainer
  */
 public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
 {
     // localize variables
     foreach (get_object_vars($this->select) as $name => $value) {
         $this->{$name} = $value;
     }
     if ($this->limit === null && $this->offset !== null) {
         $this->specifications[self::LIMIT] = 'LIMIT 18446744073709551615';
     }
     parent::prepareStatement($adapter, $statementContainer);
 }

 public function fetchAll()
 {
     $select = new Select();
     $select->from('person')->columns(array('f_name' => 'person.firstname', 'l_name' => 'person.lastname', 'comp_name' => 'contact.companyname', 'cont_name' => 'contactstate.name'))->join('contact', 'contactstate', 'person.id = contact.person_id', 'contact.state_contactstate_id = contactstate.id');
     $statement = $dbAdapter->createStatement();
     $select->prepareStatement($dbAdapter, $statement);
     $driverResult = $statment->execute();
     $resultset = new ResultSet();
     $resultset->setDataSource($driverResult);
     foreach ($resultset as $row) {
         // $row is an ArrayObject
     }
 }

 /**
  * Prepare statement
  *
  * @param  AdapterInterface $adapter
  * @param  StatementContainerInterface $statementContainer
  * @return void
  */
 public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
 {
     $driver = $adapter->getDriver();
     $platform = $adapter->getPlatform();
     $parameterContainer = $statementContainer->getParameterContainer();
     if (!$parameterContainer instanceof ParameterContainer) {
         $parameterContainer = new ParameterContainer();
         $statementContainer->setParameterContainer($parameterContainer);
     }
     $table = $this->table;
     $schema = null;
     // create quoted table name to use in insert processing
     if ($table instanceof TableIdentifier) {
         list($table, $schema) = $table->getTableAndSchema();
     }
     $table = $platform->quoteIdentifier($table);
     if ($schema) {
         $table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table;
     }
     $columns = array();
     $values = array();
     if (is_array($this->values)) {
         foreach ($this->columns as $cIndex => $column) {
             $columns[$cIndex] = $platform->quoteIdentifier($column);
             if (isset($this->values[$cIndex]) && $this->values[$cIndex] instanceof Expression) {
                 $exprData = $this->processExpression($this->values[$cIndex], $platform, $driver);
                 $values[$cIndex] = $exprData->getSql();
                 $parameterContainer->merge($exprData->getParameterContainer());
             } else {
                 $values[$cIndex] = $driver->formatParameterName($column);
                 if (isset($this->values[$cIndex])) {
                     $parameterContainer->offsetSet($column, $this->values[$cIndex]);
                 } else {
                     $parameterContainer->offsetSet($column, null);
                 }
             }
         }
         $sql = sprintf($this->specifications[static::SPECIFICATION_INSERT], $table, implode(', ', $columns), implode(', ', $values));
     } elseif ($this->values instanceof Select) {
         $this->values->prepareStatement($adapter, $statementContainer);
         $columns = array_map(array($platform, 'quoteIdentifier'), $this->columns);
         $columns = implode(', ', $columns);
         $sql = sprintf($this->specifications[static::SPECIFICATION_SELECT], $table, $columns ? "({$columns})" : "", $statementContainer->getSql());
     } else {
         throw new Exception\InvalidArgumentException('values or select should be present');
     }
     $statementContainer->setSql($sql);
 }

 /**
  * @param Sql\Select $select
  * @return null|ResultSet
  * @throws \RuntimeException
  */
 public function selectWith(Sql\Select $select)
 {
     $selectState = $select->getRawState();
     if ($selectState['table'] != $this->tableName || $selectState['schema'] != $this->schema) {
         throw new Exception\RuntimeException('The table name and schema of the provided select object must match that of the table');
     }
     if ($selectState['join'] != array()) {
         throw new Exception\RuntimeException('The Select object provided to TableRowGateway cannot include join statements.');
     }
     $statement = $this->adapter->createStatement();
     $select->prepareStatement($this->adapter, $statement);
     $result = $statement->execute();
     $resultSet = clone $this->selectResultPrototype;
     $resultSet->setDataSource($result);
     return $resultSet;
 }

 /**
  * @param AdapterInterface $adapter
  * @param StatementContainerInterface $statementContainer
  */
 public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
 {
     // localize variables
     foreach (get_object_vars($this->select) as $name => $value) {
         $this->{$name} = $value;
     }
     // set specifications
     unset($this->specifications[self::LIMIT]);
     unset($this->specifications[self::OFFSET]);
     $this->specifications['LIMITOFFSET'] = null;
     parent::prepareStatement($adapter, $statementContainer);
     //set statement cursor type
     if ($statementContainer instanceof Statement) {
         $statementContainer->setPrepareOptions(array('Scrollable' => \SQLSRV_CURSOR_STATIC));
     }
 }

 /**
  *
  * @param Select $select
  * @param $entityPrototype
  */
 public function selectSingle(Select $select, $entityPrototype = null, HydratorInterface $hydrator = null)
 {
     $adapter = $this->getDbAdapter();
     $statement = $adapter->createStatement();
     $select->prepareStatement($adapter, $statement);
     $result = $statement->execute();
     if (!$hydrator) {
         $hydrator = $this->getHydrator();
     }
     if (!$entityPrototype) {
         $entityPrototype = $this->getEntityPrototype();
     }
     $current = $result->current();
     if (!is_array($current)) {
         return null;
     }
     return $hydrator->hydrate($current, $entityPrototype);
 }

 public function selectWith(Select $select, $scrollable = true)
 {
     // Get the data
     $adapter = $this->getReadAdapter();
     $statement = $adapter->createStatement();
     $select->prepareStatement($adapter, $statement);
     $result = $statement->execute();
     if ($scrollable) {
         // Convert data to an array so we can iterate more than once
         $resultArray = array();
         foreach ($result as $row) {
             $resultArray[] = $this->toScalarValueArray($row);
         }
         $result = $resultArray;
     }
     // Create the ResultSet
     $resultSet = new ResultSet();
     $resultSet->setRowObjectPrototype($this->getModelPrototype());
     $resultSet->setDataSource($result);
     return $resultSet;
 }

 protected function processSubSelect(Select $subselect, PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
 {
     if ($driver) {
         $stmtContainer = new StatementContainer();
         // Track subselect prefix and count for parameters
         $this->processInfo['subselectCount']++;
         $subselect->processInfo['subselectCount'] = $this->processInfo['subselectCount'];
         $subselect->processInfo['paramPrefix'] = 'subselect' . $subselect->processInfo['subselectCount'];
         // call subselect
         $subselect->prepareStatement(new \Zend\Db\Adapter\Adapter($driver, $platform), $stmtContainer);
         // copy count
         $this->processInfo['subselectCount'] = $subselect->processInfo['subselectCount'];
         $parameterContainer->merge($stmtContainer->getParameterContainer()->getNamedArray());
         $sql = $stmtContainer->getSql();
     } else {
         $sql = $subselect->getSqlString($platform);
     }
     return $sql;
 }

 protected function _authenticateQuerySelect(Select $dbSelect)
 {
     $statement = $this->zendDb->createStatement();
     $dbSelect->prepareStatement($this->zendDb, $statement);
     $resultSet = new ResultSet();
     try {
         $resultSet->initialize($statement->execute(array($this->identity)));
         $resultIdentities = $resultSet->toArray();
     } catch (\Exception $e) {
         throw new Exception\RuntimeException('The supplied parameters to DbTable failed to ' . 'produce a valid sql statement, please check table and column names ' . 'for validity.', 0, $e);
     }
     return $resultIdentities;
 }

 /**
  * @testdox unit test: Test prepareStatement() will produce expected sql and parameters based on a variety of provided arguments [uses data provider]
  * @covers Zend\Db\Sql\Select::prepareStatement
  * @dataProvider providerData
  */
 public function testPrepareStatement(Select $select, $expectedSqlString, $expectedParameters, $unused1, $unused2, $useNamedParameters = false)
 {
     $mockDriver = $this->getMock('Zend\\Db\\Adapter\\Driver\\DriverInterface');
     $mockDriver->expects($this->any())->method('formatParameterName')->will($this->returnCallback(function ($name) use($useNamedParameters) {
         return $useNamedParameters ? ':' . $name : '?';
     }));
     $mockAdapter = $this->getMock('Zend\\Db\\Adapter\\Adapter', null, array($mockDriver));
     $parameterContainer = new ParameterContainer();
     $mockStatement = $this->getMock('Zend\\Db\\Adapter\\Driver\\StatementInterface');
     $mockStatement->expects($this->any())->method('getParameterContainer')->will($this->returnValue($parameterContainer));
     $mockStatement->expects($this->any())->method('setSql')->with($this->equalTo($expectedSqlString));
     $select->prepareStatement($mockAdapter, $mockStatement);
     if ($expectedParameters) {
         $this->assertEquals($expectedParameters, $parameterContainer->getNamedArray());
     }
 }

 public function dailyGrowth()
 {
     $subSelect = new \Zend\Db\Sql\Select();
     $subSelect->from('resource')->columns(array('id', 'created_date'));
     $subSelect->where->notEqualTo('node_status', \Application\Model\Resource::NODE_STATUS_DISABLED);
     $select = new \Zend\Db\Sql\Select();
     $select->from('calendar_table')->columns(array('*', new Expression('COUNT(resource.id) AS total'), new Predicate\Expression('YEAR(calendar_table.calendar_date) AS year'), new Predicate\Expression('MONTH(calendar_table.calendar_date) AS month'), new Predicate\Expression('MONTHNAME(calendar_table.calendar_date) AS monthname'), new Predicate\Expression('DAY(calendar_table.calendar_date) AS day')));
     $select->join(array('resource' => $subSelect), 'resource.created_date = calendar_table.calendar_date', array(), Select::JOIN_LEFT);
     $select->group('calendar_date');
     $statement = $this->getAdapter()->createStatement();
     $select->prepareStatement($this->getAdapter(), $statement);
     $resultset = new ResultSet();
     return iterator_to_array($resultset->initialize($statement->execute()));
 }

<?php

/** @var $adapter Zend\Db\Adapter\Adapter */
$adapter = (include file_exists('bootstrap.php') ? 'bootstrap.php' : 'bootstrap.dist.php');
refresh_data($adapter);
use Zend\Db\Sql\Select, Zend\Db\ResultSet\ResultSet;
$select = new Select();
$select->from('artist')->columns(array())->join('album', 'artist.id = album.artist_id', array('title', 'release_date'))->order(array('release_date', 'title'))->where->like('artist.name', '%Brit%');
$statement = $adapter->createStatement();
$select->prepareStatement($adapter, $statement);
$resultSet = new ResultSet();
$resultSet->initialize($statement->execute());
$albums = array();
foreach ($resultSet as $row) {
    $albums[] = $row->title . ' released on: ' . date('Y-m-d', strtotime($row->release_date));
}
assert_example_works($albums == array(0 => '...Baby One More Time released on: 1999-02-14', 1 => 'Oops!... I Did It Again released on: 2000-10-10', 2 => 'Britney released on: 2001-04-06', 3 => 'Blackout released on: 2007-10-10', 4 => 'Circus released on: 2008-11-23', 5 => 'Femme Fatale released on: 2011-10-10', 6 => 'In the Zone released on: 2011-10-10'));