In this example, we prepare and execute SQL statements.
Components used in this example
Preparing and executing a SQL statement
- We define the set of the available fetching modes.
- We define the set of the available cursor modes.
class MyDBStatement
{
// We define the set of the available fetching modes.
private $_fetchModes = array(
null => null,
'FETCH_ASSOC' => Zend_Db::FETCH_ASSOC,
'FETCH_NUM' => Zend_Db::FETCH_NUM,
'FETCH_BOTH' => Zend_Db::FETCH_BOTH,
'FETCH_COLUMN' => Zend_Db::FETCH_COLUMN,
'FETCH_OBJ' => Zend_Db::FETCH_OBJ,
);
// We define the set of the available cursor modes.
private $_cursor = array(
null => null,
'FETCH_ORI_ABS' => Zend_Db::FETCH_ORI_ABS,
'FETCH_ORI_FIRST' => Zend_Db::FETCH_ORI_FIRST,
'FETCH_ORI_LAST' => Zend_Db::FETCH_ORI_LAST,
'FETCH_ORI_NEXT' => Zend_Db::FETCH_ORI_NEXT,
'FETCH_ORI_PRIOR' => Zend_Db::FETCH_ORI_PRIOR,
'FETCH_ORI_REL' => Zend_Db::FETCH_ORI_REL,
);
- We get the method, the row details, and the fetching parameters from the GET request.
- We create the database in memory. There is one table with 10 entries.
- We get the possible values of each column.
- We fetch the selected rows.
- If we catch an exception, we return the error message.
public function process()
{
// We get the method, the row details, and the fetching parameters from the GET request.
list($id, $description, $status, $reportedBy, $createdOn,
$method, $mode, $cursor, $offset, $skip) = $this->_getParameters();
$descriptionList = $statusList = $reportedByList = $createdOnList = array();
try {
// We create the database in memory. There is one table with 10 entries.
$db = $this->_create();
// We get the possible values of each column.
list($idList, $descriptionList, $statusList, $reportedByList, $createdOnList) =
$this->_fetchColumns($db);
// We fetch the selected rows.
$result = $this->_fetchRows($db,
$id, $description, $status, $reportedBy, $createdOn,
$method, $mode, $cursor, $offset, $skip);
} catch (Exception $e) {
// If we catch an exception, we return the error message.
$result = $e->getMessage();
}
return array(
$id, $description, $status, $reportedBy, $createdOn,
$method, $mode, $cursor, $offset, $skip,
$idList, $descriptionList, $statusList, $reportedByList, $createdOnList,
$result);
}
private function _getParameters()
{
$id = isset($_GET['id'])? $_GET['id'] : null;
$description = isset($_GET['description'])? $_GET['description'] : null;
$status = isset($_GET['status'])? $_GET['status'] : null;
$reportedBy = isset($_GET['reported_by'])? $_GET['reported_by'] : null;
$createdOn = isset($_GET['created_on'])? $_GET['created_on'] : null;
$method = isset($_GET['method'])? $_GET['method'] : null;
$mode = isset($_GET['mode'])? $_GET['mode'] : null;
$cursor = isset($_GET['cursor'])? $_GET['cursor'] : null;
$offset = empty($_GET['offset'])? 0 : $_GET['offset'];
$skip = empty($_GET['skip'])? 0 : $_GET['skip'];
$skip >= 0 and $skip < 100 or $skip = 0;
return array($id, $description, $status, $reportedBy, $createdOn,
$method, $mode, $cursor, $offset, $skip);
}
- We create the table named bug.
- We add 10 rows to the table.
- We return the database connection.
private function _create()
{
$db = new Zend_Db_Adapter_Pdo_Sqlite(array('dbname' => ':memory:'));
// We create the table named bug.
$query = 'CREATE TABLE bugs ('
. 'id INTEGER NOT NULL PRIMARY KEY, '
. 'description TEXT NOT NULL UNIQUE, '
. 'status TEXT DEFAULT "flying", '
. 'reported_by TEXT DEFAULT "unknown", '
. 'created_on TEXT DEFAULT CURRENT_DATE)';
$db->query($query);
// We add 10 rows to the table.
$sql = "INSERT INTO bugs (description, status, reported_by, created_on) VALUES (?, ?, ?, ?)";
$stmt = new Zend_Db_Statement_Pdo($db, $sql);
$stmt->execute(array('fly', 'flying', 'john', '2009'));
$stmt->execute(array('bee', 'home', 'jane', '2008'));
$stmt->execute(array('mosquito', 'dead', 'john', '2007'));
$stmt->execute(array('ant', 'crawling', 'jane', '2006'));
$stmt->execute(array('spider', 'crawling', 'jane', '2009'));
$stmt->execute(array('wasps', 'home', 'john', '2008'));
$stmt->execute(array('mite', 'dead', 'jane', '2007'));
$stmt->execute(array('beetle', 'flying', 'john', '2006'));
$stmt->execute(array('earwig', 'crawling', 'jane', '2009'));
$stmt->execute(array('tick', 'home', 'john', '2008'));
// We return the database connection.
return $db;
}
private function _fetchColumns($db)
{
$idList = $this->_fetchColumn($db, 'id');
$descriptionList = $this->_fetchColumn($db, 'description');
$statusList = $this->_fetchColumn($db, 'status');
$reportedByList = $this->_fetchColumn($db, 'reported_by');
$createdOnList = $this->_fetchColumn($db, 'created_on');
return array($idList, $descriptionList, $statusList, $reportedByList, $createdOnList);
}
private function _fetchColumn($db, $column)
{
$stmt = $db->query("SELECT DISTINCT $column FROM bugs ORDER BY $column");
return $stmt->fetchAll(Zend_Db::FETCH_COLUMN, 0);
}
- We build and we run the query.
- We would normally set the scrollable cursor at this point. But setting attributes with SQLite triggers an exception.
- If the user requested to fetch only one table row, we attempts to fetch the rows that meet the criteria and we return the first row. If the user requested to skip a given number of rows, we return the row following those rows.
- We use a custom fetch method because the scrollable cursor is not available.
- If the user requested to fetch all the rows, we fetch the rows that meet the criteria and we return them.
private function _fetchRows($db,
$id, $description, $status, $reportedBy, $createdOn,
$method, $mode, $cursor, $offset, $skip)
{
// We build and we run the query.
$sql = 'SELECT * FROM bugs';
list($where, $bind) =
$this->_setWhere($id, $description, $status, $reportedBy, $createdOn);
empty($where) or $sql .= ' WHERE ' . implode(' AND ', $where);
$stmt = $db->query($sql, $bind);
// We would normally set the scrollable cursor at this point.
// But setting attributes with SQLite triggers an exception.
// $stmt->setAttribute(Zend_Db::ATTR_CURSOR, Zend_Db::CURSOR_SCROLL);
$fetchMode = $this->_fetchModes[$mode];
if ($method == 'fetch') {
// If the user requested to fetch only one table row, we attempts to fetch the rows
// that meet the criteria and we return the first row.
// If the user requested to skip a given number of rows,
// we return the row following those rows.
while($skip--) {
// We use a custom fetch method because the scrollable cursor is not available.
// $stmt->fetch();
$this->_myfetch($stmt, $fetchMode);
}
// $rows = $stmt->fetch($fetchMode, $this->_cursor[$cursor], $offset);
$rows = $this->_myfetch($stmt, $fetchMode, $this->_cursor[$cursor], $offset);
} else {
// If the user requested to fetch all the rows,
// we fetch the rows that meet the criteria and we return them.
$rows = $stmt->fetchAll($fetchMode);
}
return $rows;
}
private function _setWhere($id, $description, $status, $reportedBy, $createdOn)
{
$where = array();
$bind = array();
if ($id) {
$where[]= 'id = :id';
$bind[':id'] = $id;
}
if ($description) {
$where[]= 'description = :description';
$bind[':description'] = $description;
}
if ($status) {
$where[]= 'status = :status';
$bind[':status'] = $status;
}
if ($reportedBy) {
$where[]= 'reported_by = :reported_by';
$bind[':reported_by'] = $reportedBy;
}
if ($createdOn) {
$where[]= 'created_on = :created_on';
$bind[':created_on'] = $createdOn;
}
return array($where, $bind);
}
- We fetch all the table rows on the first call.
- We fetch a table row based on its absolute position.
- Or we fetch the first row of the table.
- Or we fetch the last row pf the table.
- Or we fetch the previous row.
- Or we fetch the table row relative to the current position.
- Or we fetch the next row.
private function _myfetch($stmt, $mode = null, $cursor = null, $offset = 0)
{
// We fetch all the table rows on the first call.
static $rows;
empty($rows) and $rows = $stmt->fetchAll($mode);
if (empty($rows)) {
return array();
}
switch($cursor) {
case Zend_Db::FETCH_ORI_ABS:
// We fetch a table row based on its absolute position.
reset($rows);
$row = $this->_myfetch($stmt, $mode, Zend_Db::FETCH_ORI_REL, $offset);
break;
case Zend_Db::FETCH_ORI_FIRST:
// Or we fetch the first row of the table.
$row = reset($rows);
break;
case Zend_Db::FETCH_ORI_LAST:
// Or we fetch the last row pf the table.
$row = end($rows);
break;
case Zend_Db::FETCH_ORI_PRIOR:
// Or we fetch the previous row.
$row = prev($rows) or $this->_exception();
break;
case Zend_Db::FETCH_ORI_REL:
// Or we fetch the table row relative to the current position.
if ($offset > 0) {
while($offset--) {
$row = next($rows) or $this->_exception();
}
} else if ($offset < 0) {
$offset = -$offset;
while($offset--) {
$row = prev($rows) or $this->_exception();
}
} else {
$row = current($rows) or $this->_exception();
}
break;
case Zend_Db::FETCH_ORI_NEXT:
default:
// Or we fetch the next row.
$row = current($rows) or $this->_exception();
next($rows);
}
return $row;
}
- We return an exception if the cursor is out of bound. We use the same error message identified in the source file: php/ext/pdo/pdo_sqlstate.c.
private function _exception()
{
// We return an exception if the cursor is out of bound.
// We use the same error message identified in the source file:
// php/ext/pdo/pdo_sqlstate.c.
throw new Zend_Db_Statement_Exception('SQLSTATE[HY109]: Invalid cursor position');
}
}
No comments:
Post a Comment