<?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);
