<?php /** @var $adapter Zend\Db\Adapter\Adapter */ $adapter = (include file_exists('bootstrap.php') ? 'bootstrap.php' : 'bootstrap.dist.php'); refresh_data($adapter); use Zend\Db\TableGateway\TableGateway, Zend\Db\TableGateway\Feature\FeatureSet, Zend\Db\TableGateway\Feature\RowGatewayFeature; $artistTable = new TableGateway('artist', $adapter, new RowGatewayFeature('id')); // also written as: // $artistTable = new TableGateway('artist', $adapter, new FeatureSet(array(new RowGatewayFeature('id')))); // also written as: // $artistTable = new TableGateway('artist', $adapter, array(new RowGatewayFeature('id'))); $rows = $artistTable->select(array('id' => 2)); $rows->getDataSource()->buffer(); // buffer result set (only does something for mysqli) /** @var $row \Zend\Db\RowGateway\RowGateway */ $row = $rows->current(); assert_example_works($row->name == 'Linkin Park' && $row instanceof \Zend\Db\RowGateway\RowGateway, true); // update the row $row['name'] = 'New Artist'; $row->save(); unset($row, $rows, $artistTable); // ensure, separately, that that worked $statement = $adapter->query('SELECT * FROM ' . $adapter->platform->quoteIdentifier('artist') . ' WHERE id = ' . $adapter->driver->formatParameterName('id')); $result = $statement->execute(array('id' => 2)); $row = $result->current(); assert_example_works($row['name'] == 'New Artist' && is_array($row));
<?php /** * Issue DELETE command through adapter using array paramaterization (default) */ $adapter = (include file_exists('bootstrap.php') ? 'bootstrap.php' : 'bootstrap.dist.php'); refresh_data($adapter); $qi = function ($name) use($adapter) { return $adapter->platform->quoteIdentifier($name); }; $fp = function ($name) use($adapter) { return $adapter->driver->formatParameterName($name); }; $sql = 'DELETE FROM ' . $qi('artist') . ' WHERE ' . $qi('id') . ' = ' . $fp('id'); /* @var $statement Zend\Db\Adapter\DriverStatementInterface */ $statement = $adapter->query($sql); $parameters = array('id' => 1); $statement->execute($parameters); // DATA INSERTED, NOW CHECK /* @var $statement Zend\Db\Adapter\DriverStatementInterface */ $statement = $adapter->query('SELECT * FROM ' . $qi('artist') . ' WHERE id = ' . $fp('id')); /* @var $results Zend\Db\ResultSet\ResultSet */ $results = $statement->execute(array('id' => 1)); $row = $results->current(); assert_example_works($row == false);
<?php /** * Issue SELECT command through adapter using name based container paramaterization */ $adapter = (include file_exists('bootstrap.php') ? 'bootstrap.php' : 'bootstrap.dist.php'); refresh_data($adapter); $artistTable = new Zend\Db\TableGateway\TableGateway('artist', $adapter); $rowset = $artistTable->select(array('id' => 2)); $row = $rowset->current(); $name = $row['name']; $name2 = $row->name; assert_example_works($name == 'Linkin Park' && $name2 == 'Linkin Park');
<?php /** @var $adapter Zend\Db\Adapter\Adapter */ $adapter = (include file_exists('bootstrap.php') ? 'bootstrap.php' : 'bootstrap.dist.php'); refresh_data($adapter); $where = new Zend\Db\Sql\Where(); $where->equalTo('id', 1)->OR->equalTo('id', 2); $where->OR->NEST->like('name', 'Ralph%')->OR->greaterThanOrEqualTo('age', 30)->AND->lessThanOrEqualTo('age', 50)->UNNEST->literal('foo = ?', 'bar'); $target = <<<EOS SELECT "foo".* FROM "foo" WHERE "id" = '1' OR "id" = '2' OR ("name" LIKE 'Ralph%' OR "age" >= '30' AND "age" <= '50') AND foo = 'bar' EOS; $select = new Zend\Db\Sql\Select('foo'); $select->where($where); assert_example_works($target == $select->getSqlString());
<?php /** * Issue INSERT command through adapter using array paramaterization (default) */ /* @var Zend\Db\Adapter\Adapter $adapter */ $adapter = (include file_exists('bootstrap.php') ? 'bootstrap.php' : 'bootstrap.dist.php'); refresh_data($adapter); $qi = function ($name) use($adapter) { return $adapter->platform->quoteIdentifier($name); }; $fp = function ($name) use($adapter) { return $adapter->driver->formatParameterName($name); }; $sql = 'INSERT INTO ' . $qi('artist') . ' (' . $qi('name') . ', ' . $qi('history') . ') VALUES (' . $fp('name') . ', ' . $fp('history') . ')'; /* @var $statement Zend\Db\Adapter\Driver\StatementInterface */ $statement = $adapter->query($sql); $parameters = array('name' => 'New Artist', 'history' => 'This is the history'); $result = $statement->execute($parameters); $id = (int) $result->getGeneratedValue(); // DATA INSERTED, NOW CHECK /* @var $statement Zend\Db\Adapter\Driver\StatementInterface */ $statement = $adapter->query('SELECT * FROM ' . $qi('artist') . ' WHERE ' . $qi('id') . ' = ' . $fp('id')); /* @var $results Zend\Db\ResultSet\ResultSet */ $results = $statement->execute(array('id' => $id)); $row = $results->current(); $name = $row['name']; assert_example_works($name == 'New Artist');
$adapter = (include file_exists('bootstrap.php') ? 'bootstrap.php' : 'bootstrap.dist.php'); refresh_data($adapter); use Zend\Db\Sql\Sql, Zend\Db\ResultSet\ResultSet; $sql = new Sql($adapter); $select = $sql->select(); $select->from('artist')->columns(array())->join('album', 'artist.id = album.artist_id', array('title', 'release_date'))->order(array('release_date', 'title'))->limit(2)->offset(0)->where->like('artist.name', '%Brit%'); // prepare statement in a platform specific way $statement = $sql->prepareStatementForSqlObject($select); $container = $statement->getParameterContainer(); // create iterable result set $resultSet = new ResultSet(); // as we iterate bind the new offset to the existing statement foreach (array(0, 2, 4) as $offset) { $container->offsetSet('offset', $offset); $resultSet->initialize($statement->execute()); $output = ''; foreach ($resultSet->toArray() as $row) { $output .= '|' . $row['title'] . '|'; } switch ($offset) { case 0: assert_example_works($output === '|...Baby One More Time||Oops!... I Did It Again|', true); break; case 2: assert_example_works($output === '|Britney||Blackout|', true); break; case 4: assert_example_works($output === '|Circus||Femme Fatale|'); break; } }
<?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; use Zend\Db\ResultSet\ResultSet; $sql = new Sql\Sql($adapter); $subselect = $sql->select(); $subselect->from('artist')->columns(array('name'))->join('album', 'artist.id = album.artist_id', array())->where->greaterThan('release_date', '2005-01-01'); $select = $sql->select(); $select->from('artist')->order(array('name' => Sql\Select::ORDER_ASCENDING))->where->like('name', 'L%')->AND->in('name', $subselect); $statement = $sql->prepareStatementForSqlObject($select); $result = $statement->execute(); $rows = array_values(iterator_to_array($result)); assert_example_works(count($rows) == 2 && $rows[0]['name'] == 'Lady Gaga' && $rows[1]['name'] == 'Linkin Park');
<?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'));
<?php /** * Issue UPDATE command through adapter using array paramaterization (default) */ $adapter = (include file_exists('bootstrap.php') ? 'bootstrap.php' : 'bootstrap.dist.php'); refresh_data($adapter); $qi = function ($name) use($adapter) { return $adapter->platform->quoteIdentifier($name); }; $fp = function ($name) use($adapter) { return $adapter->driver->formatParameterName($name); }; $sql = 'UPDATE ' . $qi('artist') . ' SET ' . $qi('name') . ' = ' . $fp('name') . ' WHERE ' . $qi('id') . ' = ' . $fp('id'); /* @var $statement Zend\Db\Adapter\DriverStatementInterface */ $statement = $adapter->query($sql); $parameters = array('name' => 'Updated Artist', 'id' => 1); $statement->execute($parameters); // DATA INSERTED, NOW CHECK /* @var $statement Zend\Db\Adapter\DriverStatementInterface */ $statement = $adapter->query('SELECT * FROM ' . $qi('artist') . ' WHERE id = ' . $fp('id')); /* @var $results Zend\Db\ResultSet\ResultSet */ $results = $statement->execute(array('id' => 1)); $row = $results->current(); $name = $row['name']; assert_example_works($name == 'Updated Artist');
<?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; use Zend\Db\ResultSet\ResultSet; $sql = new Sql\Sql($adapter); $subselect = $sql->select(); $subselect->from('artist')->columns(array())->join('album', 'artist.id = album.artist_id', array('title', 'release_date'))->where->like('artist.name', '%Brit%'); $select = $sql->select(); $select->from(array('t' => $subselect))->columns(array('c' => new Sql\Expression('COUNT(1)'))); $statement = $sql->prepareStatementForSqlObject($select); $result = $statement->execute(); $row = $result->current(); assert_example_works($row['c'] == 7);