Re: PEAR:DB_DataObjects 'better' way question

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 David Sanders wrote:

What was the problem with joining? It should fetch all the users that match the represents column. You'd only get one loop, but you'd also need to keep track of when the 'represents' changes from 'The Gap' to 'Powerplant'. Personally I'd keep the 2 loops. As a couple of side notes: You aren't escaping $represents->name. This happens automatically if you do $users->represents = $represents->name; instead of using the whereAdd() call. Also you're definining your foreign keys on names and not IDs. I dunno what 'The Gap' and 'Powerplant' are but if they ever change, you'd need to update all the foreign keys in all the tables that reference 'represents'. David Ball wrote:

Hi, First I would like to say what a great package PEAR is, thanks for all your work. Anyways my question is how to do in the following situation. It could be obvious but I am new to php/SQL and wasn't sure what to even search/google for. I have this feeling that there is a 'better' way of doing the following bit of code (the table definition and output follows). I am generating an array of all the rows in the table represents and putting them in a variable for smarty. I am also looking for all the users (could be many) that have the current loops represents name. (ie all the users where users->represents = represents->name) Joins and Links seem only for when there are uniques in both. Any ideas or help please? Or am I going about this completely wrong? $represents = DB_DataObject::factory('represents'); $users_temp = DB_DataObject::factory('auth'); $represents->find(); while ($represents->fetch()) {

        $users = clone $users_temp;
    $user_string = "";
    $users->whereAdd("represents = '".$represents->name."'");
    $users->find();
    while ($users->fetch()) {
        $user_string .= $users->username." ";       }
      $smarty->append('represents', array(
            'ID'                => $represents->represents_id,
          'REPRESENTS'        => $represents->name,
          'USERNAME'             => $user_string,
          'ACTIVE'             => $represents->active
       ));
         $users->whereAdd();

}