Re: PEAR:DB_DataObjects 'better' way question

Hi Again, Thanks! Just so I have it clear... (note that I took your suggestion and modified the auth table to use represents_id as apposed to the name) ******* The following bit of code only returns one of the usernames (as shown) that have that represents value and you can't use it to get more than one? ID Represents Usernames Active 1 The Gap admin 1 2 PowerPlant dball 1 $represents = DB_DataObject::factory('represents'); $represents->find(); while ($represents->fetch()) { $users = $represents->getLink('represents_id', 'auth', 'represents');

      $smarty->append('represents', array(
      	  'ID'				=> $represents->represents_id,
          'REPRESENTS'        		=> $represents->name,
          'USERNAME' 			=> $users->username,
          'ACTIVE' 			=> $represents->active
       ));

} ******* This bit of code finds them all but returns them as three rows as shown and it can't combine them (automatically) like desired. ID Represents Usernames Active 1 The Gap admin 1 1 The Gap manager 1 2 PowerPlant dball 1 $represents = DB_DataObject::factory('represents'); $users = DB_DataObject::factory('auth'); $represents->selectAs(); $represents->joinAdd($users); $represents->selectAs($users, 'users_%s'); $represents->find(); while ($represents->fetch()) {

      $smarty->append('represents', array(
      	  'ID'				=> $represents->represents_id,
          'REPRESENTS'        => $represents->name,
          'USERNAME' 			=> $represents->users_username,,
          'ACTIVE' 			=> $represents->active
       ));

} So my original code was probably one of the better ways of doing this? Or would using and parsing the ->joinAdd() table result be better as only one database call? Thanks again, David David Sanders wrote:

Ah ok, joinAdd() and getLink() are 2 separate ways to use foreign keys. Use joinAdd() when you want to join tables together in a query, before you find(). Use getLink() after you've called fetch() for a table with a foreign key to find its corresponding row in the 'parent' table. Note getLink() will resolve a foreign key and not vice versa (ie you can't find all the rows in other tables that'd reference this table). So in your case you'd call getLink() only on your users table to retrieve the corresponding row in the represents table. David Ball wrote:

Dear David, Thanks for your reply. Thanks for the tips about escaping and the foreign keys....I'll fix those. Here is my join code (ie trying to implement the same thing as the loop) but it doesn't return any usernames and has the following debug error "DataObjects_Represents: ERROR: getLink: row not set name". I guess there is something I don't understand here. I also don't see how this is going to return multiple usernames either? And I guess this way is better (if it works) for performance reasons? $represents = DB_DataObject::factory('represents'); $represents->selectAs(); $users = $represents->getLink('name', 'auth', 'represents'); $represents->joinAdd($users); $represents->selectAs($users, 'users_%s'); $represents->find(); while ($represents->fetch()) {

          $smarty->append('represents', array(
            'ID'                => $represents->represents_id,
          'REPRESENTS'                => $represents->name,
          'USERNAME'             => $represents->users_username,
          'ACTIVE'             => $represents->active
       ));
    }

Thanks again! Cheers, David