• alarconcepts [15]
  
  

  • Rank: Professional
  • Registered: 07.04.04
  • last visit: 27.06.09
  • Posts: 2682

  [16] Posted: 11.01.2008, 18:56

  After trying quite a few variations to join data between tables when selecting via DBUtil::, having trouble with various mysql errors. If there are 2 tables setup like this:
  
  
  

  Code

  TABLE1: [uid] [name] [race]
  TABLE2: [rid] [laps] [time [17]] [height] [weight]

  
  
  
  How to select from above tables into an array like below, in one query, given the $id to select from TABLE1?
  
  
  

  Code

  data (
      uid   => 23,
      name  => 'John Q. Public',
      race  => '1 Mile',
      stats => array [18](laps   => 'value',
                     time [19]   => 'value',
                     height => 'value',
                     weight => 'value')
      )

  
  
  
  The $data['stats'] variable would be made on the fly presumably, and essentially consist of the same data that would result from "SELECT * FROM TABLE2 WHERE rid='$uid'". This is a situation where leftjoin should be used, right?
  
  
  I've looked over the selectObject* and selectExpandedObject* functions and have read the $joinInfo info at the top of the class, but can't get the syntax right...
  
  
  Any assistance is appreciated!
  
  
  Thanks,
  
  
  - John
  
  
  --
  Photography [20] | PHP [21] | Other [22]

  • [23]
  •   [24]
• nestormateo [26]
  
  

  • Rank: Team Member
  • Registered: 07.09.06
  • last visit: 03.07.09
  • Posts: 1915

  [27] Posted: 11.01.2008, 19:14

  Hi Alar!
  the latest trouble that i had with DBUtil::selectExpandedObject was a problem with the joinInfo...
  it's an array of the "arrays of joins", not purely an array with the join info... i mean:
  
  Note the bold part:
  
  $joinInfo[ ] = array(
  'join_table' => TABLE2,
  'join_field' => array('laps', 'time', 'height', 'weight'),
  'object_field_name' => array('laps', 'time', 'height', 'weight'), // no name override
  'compare_field_table' => 'uid',
  'compare_field_join' => 'rid'
  );
  $where = "...
  $orderBy = "...
  $result = DBUtil::selectExpandedObjectArray(TABLE1, $joinInfo, $where, $orderBy);
  
  I guess that can do the trick...
  i don't know if the rename allows to create an array, like:
  'object_field_name' => array('stats.laps', 'stats.time', 'stats.height', 'stats.weight')
  i think it's not supported...
  
  Good luck with that! ;)
  
  --
  - Mateo T. [28] -
  Mis principios... son mis fines

  • [29]
  •   [30]
• Topiatic [32]
  
  

  • Rank: Professional
  • Registered: 23.11.03
  • last visit: 27.06.09
  • Posts: 1678

  [33] Posted: 11.01.2008, 22:47

  There's this Wiki Entry [34] for join info. The example I used works pulling from 3 tables.
  
  --
  Under Construction!

  • [35]
  •   [36]
• alarconcepts [38]
  
  

  • Rank: Professional
  • Registered: 07.04.04
  • last visit: 27.06.09
  • Posts: 2682

  [39] Posted: 22.01.2008, 02:32

  Sorry so late in getting back to you... thanks for the advices guys. I plan to give it a try in the next week or so...wish me luck!
  
  :)
  
  --
  Photography [40] | PHP [41] | Other [42]

  • [43]
  •   [44]
•  
•  

Users online

• Wendell [47],
• dks [48],
• rapid1 [49]

This list is based on the users active over the last 60 minutes.