Zend db
From EKiniWiki
Contents |
[edit] Inserting a Row
function myFunc()
{
$claimH = new ClaimHeader();
$data['invoice_no'] = trim($filter->filter($this->_request->getPost('invoice_no')));
$data['invoice_date'] = trim($filter->filter($this->_request->getPost('invoice_date')));
$claimH->insert($data);
}
invoice_no and invoice_date must be fieldnames found in the ClaimHeader table.
[edit] Deleting a Row
function myDeleteFunc()
{
$claimH = new ClaimHeader();
$db = $Separation->getAdapter();
$id = '5'; //or let's say that this variable came from a $_POST or $_GET
$where = $db->quoteInto('id= ?',$id);
//$where = 'id="'.$id.'"'; //Vulnerable to SQL Injection
$claimH->delete($where);
}
[edit] Updating a Row
function myFunc()
{
$claimH = new ClaimHeader();
$db = $Separation->getAdapter();
$id = 123; //this could be from a $_POST or $_GET
$data['invoice_no'] = trim($filter->filter($this->_request->getPost('invoice_no')));
$data['invoice_date'] = trim($filter->filter($this->_request->getPost('invoice_date')));
$where = $db->quoteInto('id= ?',$id); //prevents SQL Injection
//$where = 'id = '.$id; //vulnerable to SQL Injection - do the one above
$claimH ->update($data, $where);
}
invoice_no and invoice_date must be fieldnames found in the ClaimHeader table.
[edit] Fetching A Single Row
$claimH = new ClaimHeader();
if ($row = $claimH->fetchRow('invoice_no='.$invoice_no)) {
$data['invoice_date'] = $row->invoice_date;
}
[edit] Fetching All Rows
$claimD = new ClaimDetails();
$details = $claimD->fetchAll('invoice_no='.$invoice_no);
$this->dump_this($details);
//You can do a foreach() here.
You can check if fetchAll() returned rows by doing a count() on the returned rowset:
$resolution = new Resolution(); //the class
$this->view->resolution = $resolution->fetchAll('status="on"');
if (count($this->view->resolution)<=0) {
throw new Exception('Empty table: resolution');
}
[edit] Creating a method inside the Model
Useful in some cases:
<?php
class EmployeeGroup extends Zend_Db_Table
{
protected $_name = "employee_group";
/**
* returns rows matching the group_id passed
*
* @param int $group_id
*/
public function getUsernames($group_id)
{
return $this->fetchAll('group_id='.$group_id);
}
}
[edit] Writing Custom SQL Queries
This would be better to use - avoid SQL injection
$db = Zend_Registry::get(’dbAdapter’); $sql = $db->quoteInto(”SELECT * FROM Table1 WHERE id = ?”, $target_id);
A better example of safe SQL Queries:
$db = Zend_Registry::get(’dbAdapter’);
$select = $db->select()->
from(’Table1′, ‘*’)->
where(’id = ?’, $id)->
where(’url = ?’, $url);
$row = $db->fetchRow($select);
Source: http://blog.ekini.net/2008/02/12/what-is-the-safest-way-to-do-database-queries-in-zend-framework/
[edit] Table Relationships / Model Reference Mapping
[edit] Sample Table Structure
Employee Table
- username
- password
Employee Group Table (the class of this table is refTableClass)
- username
- group_id (this is refColumns)
Group Table
- group_id (this is columns)
- group_name
- group_desc
[edit] Model Classes
<?php
class Users extends Zend_Db_Table
{
protected $_name = "employee";
}
<?php
class EmployeeGroup extends Zend_Db_Table
{
protected $_name = "employee_group";
}
<?php
class Groups extends Zend_Db_Table
{
protected $_name = "groups";
protected $_referenceMap = array(
'GroupName' => array(
'columns' => 'group_id', //this column is found in this table (group)
'refTableClass' => 'EmployeeGroup', //this is the class name for employee_group table
'refColumns' => 'group_id' //this is the field found in employee_group table
)
);
}
[edit] Retrieving stuff - from the Group table
foreach($rows as $row) {
$gids .= $row->group_id.' ';
$gnames .= $row->findDependentRowset('Groups','GroupName')->current()->group_name.' ';
}
WHERE:
- Groups is the name of the Model - class name for the group table
- GroupName is the reference map name
[edit] Table Joins
$dbAdapter = Zend_Registry::get('dbAdapter'); //this is based on Akrabat's Tutorial - in the bootstrap file.
$joinedRows = $dbAdapter->fetchAll("SELECT e.*, g.* FROM employee_group e JOIN groups g USING (group_id) WHERE $where");
$joinedRows is an associative array. $dbAdapter->fetchAll() returns an associative array.
IMPORTANT Even though there is only one result for $joinedRows you must still do a foreach() to access the elements. For example:
//where $employee is a result of $dbAdapter->fetchAll() but has only ONE (1) row. foreach ($employee AS $row) $empDeptNo = $row['deptNo'];
It took me 2 hours to figure this out because I was assuming that if there was only one row in the result, I could right away get deptNo using $employee['deptNo'] - but I couldn't. Somehow, I had to do a foreach() and assign the deptNo to another variable.
UPDATE Actually it returns some sort of 2-Dimensional Array. So if you had only one (1) row in the result, you must specify the first index of the array - which is 0. So, instead of doing a foreach above, you could actually do this:
$empDeptNo = $employee[0]['deptNo'];
Take note of the [0] - the first element of the 2-D $employee array.
See Bill Karwin's reply from the Nabbles mailing list: Bill Karwin's Reply
[edit] Changing fetch mode in Zend_db
As stated above, the default return for Zend_db is an array of results. To return the result as an object, see below.
$db = Zend_Registry::get('db');
$db->setFetchMode(Zend_Db::FETCH_OBJ);
$result = $db->fetchAll("SELECT * FROM user_roles");
foreach($result AS $row) {
echo $row->fieldA; //instead of the usual $row['fieldA']
echo $row->fieldB; //instead of the usual $row['fieldA']
}
I love this, since I do not have to type [' and '] at the end of each field. Typing -> is easier!
[edit] Transactions - InnoDB
Please note that this is based on the Bootstrap File in the tutorial from Akrabat.com
$dbAdapter = Zend_Registry::get('dbAdapter');
$dbAdapter->beginTransaction();
try {
if (!$this->_request->isPost()) {
throw new Exception('Invalid request.');
}
/*
Rest of your code goes here.
*/
$dbAdapter->commit();
$this->render();
} catch (Exception $e) {
$dbAdapter->rollBack();
$this->_flashMessenger->addMessage($e->getMessage());
$this->_redirect('/');
}
[edit] Best Practices
[edit] What is the best way to do queries in Zend Framework?
Please see this blog post http://blog.ekini.net/2008/02/12/what-is-the-safest-way-to-do-database-queries-in-zend-framework/
//Roman Nestertsov's way of doing it.
$db = Zend_Registry::get(’dbAdapter’);
$sql = $db->quoteInto(”SELECT * FROM Table1 WHERE id = ?”, $target_id);
//Erik's way of doing it.
$db = Zend_Registry::get(’dbAdapter’);
$select = $db->select()->
from(’Table1′, ‘*’)->
where(’id = ?’, $id)->
where(’url = ?’, $url);
$row = $db->fetchRow($select);
[edit] Comments / Notes
[edit] Bill Karwin's Reply from Nabbles Mailing List
The way to do this with the table-relationships feature is by looping through the rowset returned from the EmployeeGroup table, and using findDependentRowset() to fetch rows from the Groups table.
$employeeGroupRowset = $empGroup->fetchAll($where); foreach ($employeeGroupRowset as $row) { $groupRowset = $row->findDependentRowset('Groups'); }
Personally, I would do a join instead:
$joinedRows = $db->fetchAll("SELECT e.*, g.* FROM employee_group e JOIN groups g USING (group_id) WHERE $where");
That method returns a plain array of arrays, not a Zend_Db_Table_Rowset.
The Zend_Db_Table classes have no support for joins.
Regards, Bill Karwin
[edit] Bill Karwin's Comment on SQL Injection Vulnerability
This is just an echo from the comment Bill Karwin left on my blog
Bill Karwin Says: August 31st, 2007 at 1:18 pm edit
Hi eKini,
I noticed in your Zend_Db cheatsheet, you show multiple examples of interpolating PHP integer variables into SQL expressions. In the specific cases you show, this is okay because the variable has only values defined in the same function. But in general, variables might contain data that comes from an untrusted source, such as user input. This introduces a risk of SQL injection vulnerability — either by accident or by a malicious attacker.
So you should use the Zend_Db quoteInto() method to help reduce this risk. For example, instead of this:
$where = 'id='.$id.; // Risk of SQL injection
You should do this:
$db = $claimH->getAdapter();
$where = $db->quoteInto('id= ?', $id);
The result is that $id is interpolated into the SQL expression with proper SQL quote delimiters, and escapes any special characters within the content of $id.
