Re: database scheme & relations

Ronald van Raaphorst wrote:

Hi all, I'm writing a browse/upadate package, which will handle both a browse and an update, and set the data ready to use for Smarty. I had this almost ready (with my own mysql query builder), before I discovered PEAR :( Well, as I see it would be much better to add this to pear, I want to use at least pear::DB or other packages. The problem is where to define database relations, so that my browse/form will generate the right queries. For example: I have a simple database with 2 tables: Article and Category:

Article                            Category
ID                          !->    ID
Name                     !       Name

CategoryID <<---- ! Article is the child of Category in a N:1 relation. What I want is to pass the Article DB object to the browse or the form. The browse will recognise that there's a related parent, and will, instead of displaying the Article->CategoryID, display the Category->Name, and the query should use a LEFT JOIN for the category: Select Article.ID as ArticleID, Article.Name as ArticleName, Category.Name as CategoryName LEFT JOIN category on Article.CategoryID = CategoryID ..... If my form recognizes the related parent (Category) from the passed child (Article), it should generate a query to build a html select statement (dropcombo), and place the ID (linked field) and the displayed field (Category.Name) in an array, so the form can then build the select statement. (But ofcourse I can set the form to create a lookuplink to another browse too). Now the question is: Is there a package in which these links can be defined? If not, would it be wise to load such a configuration into globals, so the scheme will not load every time? Regards, Ronald Compad Hi Ronald,

Have you looked at PEAR::DataObjects and PEAR::QuickForm? PEAR::Tree (for parent - child stuff) might also help. Ik think the functionality you want already exists in the combined use of these packages. In PEAR::DataObjects you can define table relationships. http://pear.php.net/manual/en/packages.database.db-dataobject.relations.php HTH Jeroen