[fw-db] I'm having a problem with one to many relationship.Please help.

Hello everyone,

I could really do with a bit of help here as I'm having trouble with my
table relationships. I have two database tables and one is a lookup table
for the other. They are as follows -

CREATE TABLE IF NOT EXISTS `categories` (
`id` int(11) NOT NULL auto_increment,
`category_names_id` int(11) NOT NULL,
`parent_id` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
KEY `category_names_id` (`category_names_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `category_names_id`, `parent_id`) VALUES
(1, 1, ''),
(2, 2, ''),
(3, 3, '1'),
(4, 4, '1'),
(5, 4, '2'),
(6, 5, '2';

CREATE TABLE IF NOT EXISTS `category_names` (
`id` int(11) NOT NULL,
`category_name` varchar(255) NOT NULL
PRIMARY KEY (`id`),
KEY `category_name` (`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `category_names`
--

INSERT INTO `category_names` (`id`, `category_name`) VALUES
(1, 'Harry Potter'),
(2, 'Batman'),
(3, 'Books'),
(4, 'Blu Ray'),
(5, 'DVD');

I'm using a category_names lookup table because some of my sub-categories
share the same name but I don't want to fill up my "category" table with
repeated data. So you can see from the above I have 2 main categories
(without parent_ids) - Harry Potter and Batman. Both have 2 sub-categories,
Harry Potter has 'Books' and 'Blu Ray' whereas Batman has 'Blu Ray' and
'DVD'.

Eventually my Products table will contain individual items with a category
id which will relate to the id field in category - but I won't get that far
ahead yet!

Anyway I have set up the following model classes -

<?php
class Categories extends Zend_Db_Table_Abstract
{

protected $_name = 'categories';
protected $_primary = 'id';
protected $_dependentTables = array('CategoryNames');
}

and

<?php
class CategoryNames extends Zend_Db_Table_Abstract
{

protected $_name = 'category_names';
protected $_primary = 'id';

protected $_referenceMap = array(
'Categories' => array(
'columns' => array('id'),
'refTableClass' => 'Categories',
'refColumns' => array('category_names_id')
)
);
}
}

When I try the following in my controller, I get a blank screen and I don't
know what is going on.

<?php
class IndexController extends Zend_Controller_Action
{
public function indexAction() {
$category = new Categories();
$batman = $category->fetchRow('id = 2');
$this->view->batman = $batman;
$this->view->catname =
$batman->findDependentRowset('CategoryNames')->current();
}
}

If anyone could help I would really appreciate it. I don't know if I'm doing
this right or what I am doing wrong as I've followed a couple of tutorials
on the web and I thought I was doing exactly the same as them.

Thanks

--
View this message in context: http://www.nabble.com/I%27m-having-a-problem-with-one-to-many-relationship.Please-help.-tp21835113p21835113.html
Sent from the Zend DB mailing list archive at Nabble.com.