In this example, we implement basic queries: insert, update, deletion, and fetching.
Components used in this example
Implementation of the queries
- The database has one table.
- We define a set of initial data.
- We define the set of the available methods and the available fetching modes.
class MyDbQuery
{
// The database has one table.
private $_columns = array('id', 'description', 'status', 'reported_by', 'created_on');
// We define a set of initial data.
private $_data = array(
array('description' => 'fly', 'status' => 'buzzing', 'reported_by' => 'john'),
array('description' => 'bee', 'status' => 'gone', 'reported_by' => 'jane'),
array('description' => 'mosquito', 'status' => 'dead', 'reported_by' => 'john'),
);
// We define the set of the available methods and the available fetching modes.
private $_methods = array('insert', 'delete', 'update');
private $_fetchMethods = array('fetchAll', 'fetchAssoc', 'fetchCol', 'fetchPairs', 'fetchRow');
private $_fetchModes = array(
'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 get the method, the row details, and the fetching mode from the GET request. Or we get the request to restore the database.
- We create an empty database in memory.
- If the user requested to restore the database, we load the initial data.
- If the user is running a query, we import the existing data into the database if they are available. Otherwise, we load the initial data.
- We run the query.
- If the user requested to fetch data, we return the selected rows.
- If the user requested to insert, update or delete data, we return the number of affected rows.
- We export the database.
- If we catch an exception, we return the error message.
public function process()
{
// We get the method, the row details, and the fetching mode from the GET request.
// Or we get the request to restore the database.
list($method, $row, $mode, $restore) = $this->_getParameters();
$data = array();
try {
// We create an empty database in memory.
$db = $this->_create();
if ($restore) {
// If the user requested to restore the database, we load the initial data.
$data = $this->_import($db, true);
$message = 'Database restored!';
} else {
// If the user is running a query, we import the existing data into the database
// if they are available. Otherwise, we load the initial data.
$data = $this->_import($db);
// We run the query.
if (in_array($method, $this->_fetchMethods)) {
// If the user requested to fetch data, we return the selected rows.
$message = $this->_fetch($db, $row, $method, $mode);
} else if (in_array($method, $this->_methods)) {
// If the user requested to insert, update or delete data,
// we return the number of affected rows.
$call = "_$method";
$nb = $this->$call($db, $row);
$message = "$nb row(s) were {$method}ed!";
} else {
$message = 'Please select a method and fill in values as needed!';
}
// We export the database.
$data = $this->_export($db);
}
} catch (Exception $e) {
// If we catch an exception, we return the error message.
$message = $e->getMessage();
}
return array($data, $message);
}
- We extract the name of the method. We ignore invalid methods.
- We extract the row details and we ignore invalid columns.
- We extract the fetching mode. We ignore invalid modes.
private function _getParameters()
{
// We extract the name of the method. We ignore invalid methods.
isset($_GET['method']) and $method = $_GET['method'] and
(in_array($method, $this->_methods) or in_array($method, $this->_fetchMethods)) or
$method = null;
// We extract the row details and we ignore invalid columns.
$row = array_intersect_key($_GET, array_flip($this->_columns));
$row = array_filter($row);
// We extract the fetching mode. We ignore invalid modes.
isset($_GET['mode']) and $mode = $_GET['mode'] and
isset($this->_fetchModes[$mode]) and $mode = $this->_fetchModes[$mode] or
$mode = null;
$restore = !empty($_GET['restore']);
return array($method, $row, $mode, $restore);
}
- We create the table named bug. The description is mandatory and must be unique. The other fields have a default value.
- We return the database connection.
private function _create()
{
$db = new Zend_Db_Adapter_Pdo_Sqlite(array('dbname' => ':memory:'));
// We create the table named bug.
// The description is mandatory and must be unique.
// The other fields have a default value.
$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 return the database connection.
return $db;
}
- We import the data stored inside a cookie if available and valid. Or we use the initial data.
- We load the table named bug.
- We return the data. It is necessary to fetch the data from the database in order to get the entries with their default values.
private function _import($db, $restore = false)
{
// We import the data stored inside a cookie if available and valid.
// Or we use the initial data.
!$restore and isset($_COOKIE['db-query']) and
$data = unserialize(stripcslashes($_COOKIE['db-query'])) and is_array($data) or
$data = $this->_data;
// We load the table named bug.
foreach($data as $row){
$row = array_intersect_key($row, array_flip($this->_columns));
$db->insert('bugs', $row);
}
// We return the data.
// It is necessary to fetch the data from the database
// in order to get the entries with their default values.
return $db->fetchAssoc('SELECT * FROM bugs');
}
- We fetch the data.
- We serialize the data and we store the data inside a cookie for one hour.
private function _export($db)
{
// We fetch the data.
$data = $db->fetchAssoc('SELECT * FROM bugs');
// We serialize the data and we store the data inside a cookie for one hour.
setcookie('db-query', serialize($data), time() + 3600, '/');
return $data;
}
- We ignore the query if the entry is empty.
- We return the number of inserted rows.
private function _insert($db, $row)
{
$nb = 0;
// We ignore the query if the entry is empty.
empty($row) or $nb = $db->insert('bugs', $row);
// We return the number of inserted rows.
return $nb;
}
- We ignore the query if the row id is empty.
- We return the number of updated rows.
private function _update($db, $row)
{
$nb = 0;
// We ignore the query if the row id is empty.
isset($row['id']) and $where['id = ?'] = $row['id'] and
$nb = $db->update('bugs', $row, $where);
// We return the number of updated rows.
return $nb;
}
- We return the number of deleted rows.
private function _delete($db, $row)
{
// We return the number of deleted rows.
return $db->delete('bugs', $this->_setWhere($row));
}
- We set the fetching mode if requested.
- We build the query using arrays.
- We fetch and we return the data.
private function _fetch($db, $row, $method, $mode)
{
// We set the fetching mode if requested.
$mode and $db->setFetchMode($mode);
// We build the query using arrays.
$query = 'SELECT * from bugs';
list($where, $bind) = $this->_setWhereForFetch($row);
$where and $query .= " WHERE $where";
// We fetch and we return the data.
return $db->$method($query, $bind);
}
private function _setWhere($row)
{
$where = array();
foreach($row as $column => $value){
$where["$column = ?"] = $value;
}
return $where;
}
private function _setWhereForFetch($row)
{
$where = $this->_setWhere($row);
$bind = array_values($where);
$where = implode(' AND ', array_keys($where));
return array($where, $bind);
}
}
No comments:
Post a Comment