Home > Zend_Db > Implementation of Relations using Zend_Db_Table

Implementation of Relations using Zend_Db_Table

intro
This article illustrates how to implement relations using Zend_Db_Table. You can download the source code of this article from here.

1.Create bootstrap.php and config.in
Create the file application/bootstrap.php with the following content.

<?php
set_include_path('../library' . PATH_SEPARATOR . get_include_path());
define('APP_BASE', '../application');
define('CONFIG_PATH', APP_BASE . '/config.ini');
 
require_once 'Zend/Controller/Front.php';
require_once 'Zend/Layout.php';
require_once 'Zend/Db.php';
require_once 'Zend/Config/Ini.php';
require_once 'Zend/Db/Table/Abstract.php';
 
$layout = Zend_Layout::startMvc();
//$layout->getView()->baseUrl = '/yourBaseUrl';
 
$config = new Zend_Config_Ini(CONFIG_PATH, 'staging');
$params = $config->database->params->toArray();
$params['options'][Zend_Db::CASE_FOLDING] = Zend_Db::CASE_LOWER;
$dbAdapter = Zend_Db::factory($config->database->adapter, $params);
Zend_Db_Table_Abstract::setDefaultAdapter($dbAdapter);
 
Zend_Controller_Front::run(APP_BASE . '/controllers');

Create the file application/config.ini with the following content.
Please modify database.params.* properly.

[staging]
database.adapter         = pdo_mysql
database.params.host     = localhost
database.params.username = db_user
database.params.password = db_password
database.params.dbname   = db_name

In this article, we will use the following sql.

CREATE TABLE users
(
    id integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    username varchar(20) NOT NULL,
    password varchar(20) NOT NULL
);
 
CREATE TABLE profiles
(
    id integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    user_id integer         NOT NULL,
    first_name varchar(100) NOT NULL,
    last_name varchar(100)  NOT NULL,
    email varchar(200)      NOT NULL,
    url varchar(200)        NOT NULL
);
 
INSERT INTO users (username, password)
    VALUES ('testuser', 'testpassword');
 
INSERT INTO profiles (user_id, first_name, last_name, email, url)
    VALUES (1, 'Myfirstname', 'Mylastname', 'my@emailaddress',
        'http://myurl/my/page');
2.Create Models
Create the file application/models/Users.php with the following content.

<?php
require_once 'Zend/Db/Table/Abstract.php';
 
class Users extends Zend_Db_Table_Abstract
{
    protected $_name = 'users';
    protected $_dependentTables = array('Profiles');
}

The property $_dependentTables is array of class names of tables that are children of current table.
Create the file application/models/Profiles.php with the following content.

<?php
require_once 'Zend/Db/Table/Abstract.php';
 
class Profiles extends Zend_Db_Table_Abstract
{
    protected $_name = 'profiles';
    protected $_referenceMap = array(
        'Account' => array(
            'columns'           => 'user_id',
            'refTableClass'     => 'Users',
            'refColumns'        => 'id'
        )
    );
}

The property $_referenceMap is an array of reference rules. Each key is a name for one reference rule.
Each value is also an associative array, with the following keys:

Key Description
columns name(s) of column(s) in the child table
refTableClass class name of the parent table
refColumns name(s) of column(s) in the parent table
onDelete 'cascade' or self::CASCADE means that a delete in the parent table also causes a delete of referencing rows in the child table.
Default is not cascade.
onUpdate 'cascade' or self::CASCADE that an update of primary key values in the parent table also causes an update of referencing rows in the child table.
Default is not cascade.
3.Create Controller
Create the file application/constorllers/IndexController.php with the following content.

<?php
require_once 'Zend/Controller/Action.php';
require_once APP_BASE . '/models/Users.php';
require_once APP_BASE . '/models/Profiles.php';
 
class IndexController extends Zend_Controller_Action
{
    protected $_user_id = 1;
 
    public function indexAction()
    {
        $users = new Users();
        $user = $users->find($this->_user_id)->current();
        if ($user)
        {
            $profile = $user->findProfiles()->current();
            if ($profile)
            {
                $this->view->profile = $profile->toArray();
            }
            $this->view->user = $user->toArray();
        }
    }
}

The code $user->findProfiles() returns the rows of Profiles. The following is an alternative code.

$user->findDependentRowset('Profiles')

Or you can fetch the row of parent table from the row of child table. The following is about it.

$profile->findParentUsers()
$profile->findParentUsersByAccount()
$profile->findParentRow('Users')
$profile->findParentRow('Users', 'Account')
4.Create View
Create the file application/views/scripts/index/index.phtml with the following content.

 
<h1><?= $this->translate('Table Relation Sample') ?></h1>
<h2><?= $this->translate('Your User Account') ?></h2>
 
<?php if (!$this->user) : ?>
<span><?= $this->translate('No User Account Found') ?></span>
<?php else : ?>
<dl>
<?php foreach($this->user as $key => $value) : ?>
<dt><?= $this->translate($key) ?></dt>
<dd><?= $this->escape($value) ?></dd>
 
<?php endforeach; ?>
</dl>
 
<?php endif; ?>
<h2><?= $this->translate('Your Profile') ?></h2>
 
<?php if (!$this->profile) : ?>
<span><?= $this->translate('No Profile Found') ?></span>
<?php else : ?>
<dl>
<?php foreach($this->profile as $key => $value) : ?>
<dt><?= $this->translate($key) ?></dt>
<dd><?= $this->escape($value) ?></dd>
 
<?php endforeach; ?>
</dl>
 
<?php endif; ?>

5.Check
Access the web server and check the rows of Users and Profiles

Comments:6

Tim Sawyer 08-07-08 (Tue) 4:43

Hi, Thank you for your informative Zend Framework articles. I am new to Zend Framework and just learning how to do many things.

I would like to do something similar to what you have done with the multiselect. I have a many-to-many table similar to you. Could you explain how the values in the multiselect are highlighted, based on database data, when on the project/update page? In other words, how do the database values populate and select the members in the multiselect.

I have downloaded the db_rel_table file and am looking at the code. But I am too new to figure it out. Thank you for helping.

Tim

oplabo 08-07-09 (Wed) 15:21

Hello. Thank you for your comment and sorry for late.

The values populate in the function _executeFind in application/controllers/ProjectControllers.php.
And to display the form, I used the following 2 forms(one is sub form).
application/models/forms/ProjectForms.php
application/models/forms/ProjectMemberSubForm.php

Please point it out, if this comment is irrelevant.

Thank you.

PS: this comment is about the article Using Relation Table with Zend Framework.

Tim Sawyer 08-07-11 (Fri) 4:46

That is helpful, thank you. And I have been working my way through that. I’m studying your outputFilter now. It looks like that is how the project numbers determine the project names. But usage of outputFilter is not clear to me yet.

Tim

oplabo 08-07-11 (Fri) 15:22

Hello.
Output Filter is just for converting values(ex. converting values of select element to corresponding labels).
It uses Zend_Input_Filter. The following document is about it.
Programmer’s Reference Guide(14.5. Zend_Filter_Input)

And Output Filter is used in views. Like the following file:
application/views/scripts/project/detail.phtml(line:3-4)

Thank you.

Tim Sawyer 08-07-18 (Fri) 0:01

There are alot of hurtles to jump over for a newbee, either that or I’m slow. I’ve very slowly working my was through each problem I encounter along the way. My current problem is with the ArrayValue plugin. I’m getting this message:

Opps! An error occurred!Plugin by name ArrayValue was not found in the registry.

#0 /home/mssapps/ZendFramework-1.5.2/library/Zend/Filter/Input.php(908): Zend_Loader_PluginLoader->load(’ArrayValue’)
#1 /home/mssapps/ZendFramework-1.5.2/library/Zend/Filter/Input.php(880): Zend_Filter_Input->_getFilterOrValidator(’filter’, Array)
#2 /home/mssapps/ZendFramework-1.5.2/library/Zend/Filter/Input.php(576): Zend_Filter_Input->_getFilter(Array)
#3 /home/mssapps/ZendFramework-1.5.2/library/Zend/Filter/Input.php(658): Zend_Filter_Input->_filter()
#4 /home/mssapps/ZendFramework-1.5.2/library/Zend/Filter/Input.php(331): Zend_Filter_Input->_process()
#5 /var/www/application/views/scripts/san/show.phtml(26): Zend_Filter_Input->getEscaped()
#6 /home/mssapps/ZendFramework-1.5.2/library/Zend/View.php(46): include(’/var/www/applic…’)
#7 /home/mssapps/ZendFramework-1.5.2/library/Zend/View/Abstract.php(769): Zend_View->_run(’../../applicati…’)
#8 /home/mssapps/ZendFramework-1.5.2/library/Zend/Controller/Action/Helper/ViewRenderer.php(895): Zend_View_Abstract->render(’san/show.phtml’)
#9 /home/mssapps/ZendFramework-1.5.2/library/Zend/Controller/Action/Helper/ViewRenderer.php(916): Zend_Controller_Action_Helper_ViewRenderer->renderScript(’san/show.phtml’, NULL)
#10 /home/mssapps/ZendFramework-1.5.2/library/Zend/Controller/Action/Helper/ViewRenderer.php(955): Zend_Controller_Action_Helper_ViewRenderer->render()
#11 /home/mssapps/ZendFramework-1.5.2/library/Zend/Controller/Action/HelperBroker.php(161): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#12 /home/mssapps/ZendFramework-1.5.2/library/Zend/Controller/Action.php(512): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#13 /home/mssapps/ZendFramework-1.5.2/library/Zend/Controller/Dispatcher/Standard.php(293): Zend_Controller_Action->dispatch(’showAction’)
#14 /home/mssapps/ZendFramework-1.5.2/library/Zend/Controller/Front.php(914): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
#15 /var/www/html/sandb/index.php(53): Zend_Controller_Front->dispatch()
#16 {main}

How do I register ArrayValue?

Thank you,

Tim

oplabo 08-07-18 (Fri) 17:39

Hello.
You can register ArrayValue plugin using the method addFilterPrefixPath of Zend_Input_Filter. The following file shows an example.
application/models/Projects.php(line:23)

Please make sure that the file ArrayValue.php is in your library directory({your_library}/My/Filter/ArrayValue.php).

Thank you.

Comment Form
Remember personal info

Trackbacks:0

Trackback URL for this entry
http://www.oplabo.com/article/20/trackback
Listed below are links to weblogs that reference
Implementation of Relations using Zend_Db_Table from Open Programming Laboratory

Home > Zend_Db > Implementation of Relations using Zend_Db_Table

Japanese
Pages
Categories
Recent Posts
Recent Comments
Archives
Tag Cloud
Search
Feeds

Return to page top