Hey Stephen,
I had the same issue, so in order to simplify my code I had to create my own
class for this:
<?php
namespace Application\Service\Db;
use Zend\Db\Adapter\Adapter,
Zend\Db\ResultSet\ResultSet,
Zend\Db\ResultSet\Row,
Zend\Db\Sql\Insert,
Zend\Db\Sql\Update,
Zend\Db\Sql\Delete,
Zend\Db\Sql\Select;
class SqlGateway {
protected $dbAdapter;
protected $lastInsertId;
public function __construct(Adapter $dbAdapter) {
$this->dbAdapter = $dbAdapter;
}
public function getAdapter() {
return $this->dbAdapter;
}
public function select($tableName, array $columns = null){
$select = new Select($tableName);
if ( true === is_array($columns) ) {
$select->columns($columns);
}
return $select;
}
public function insert($tableName, array $values){
$insert = new Insert($tableName);
$insert->values($values);
return $insert;
}
public function update($tableName, array $values){
$update = new Update($tableName);
$update->set($values);
return $update;
}
public function delete($tableName, array $where = null){
$delete = new Delete($tableName);
if ( true === is_array($where) ) {
$delete->where($where);
}
return $delete;
}
public function fetchAll($sql) {
$statment = $this->dbAdapter->createStatement();
$sql->prepareStatement($this->dbAdapter, $statment);
$resultSet = $statment->execute();
if ( 0 >= $resultSet->count() ) {
return null;
}
$result = new \StdClass;
foreach ( $resultSet as $key => $value ) {
$result->{$key} = (object)$value;
}
unset($resultSet);
return $result;
}
public function fetchRow($sql) {
$statment = $this->dbAdapter->createStatement();
$sql->prepareStatement($this->dbAdapter, $statment);
$result = $statment->execute()->current();
return ( false === empty($result) ? (object)$result : null );
}
public function execute($sql) {
$statment = $this->dbAdapter->createStatement();
$sql->prepareStatement($this->dbAdapter, $statment);
$result = $statment->execute();
if ( $sql instanceof Insert ) {
$this->lastInsertId =
$this->dbAdapter->getDriver()->getConnection()->getLastGeneratedValue();
}
return $result;
}
public function getLastInsertId() {
return $this->lastInsertId;
}
}
Usage:
$result =
$this->sqlGateway->fetchRow($this->sqlGateway->select('affected_version',
array('count' => new
Expr('count(id)')))->where("something='true'"));
--
View this message in context:
http://zend-framework-community.634137.n4.nabble.com/Executing-manually-built-SQL-queries-Is-there-an-easier-way-tp4657944p4657953.html
Sent from the Zend Framework mailing list archive at Nabble.com.
--
List: [email protected]
Info: http://framework.zend.com/archives
Unsubscribe: [email protected]
