• **unknown user**
  

  • Rank: Professional
  • Registered: 16.03.02
  • last visit: 21.10.09
  • Posts: 2334

  [15] 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 [16]] [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 [17](laps   => 'value',
                     time [18]   => '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
  

  • [19]
  •   [20]
• nestormateo [22]
  
  

  • Rank: Team Member
  • Registered: 06.09.06
  • last visit: 02.03.10
  • Posts: 2140

  [23] 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. [24] -
  Mis principios... son mis fines

  • [25]
  •   [26]
• Topiatic [28]
  
  

  • Rank: Professional
  • Registered: 23.11.03
  • last visit: 13.12.09
  • Posts: 1486

  [29] Posted: 11.01.2008, 22:47

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

  • [31]
  •   [32]
• **unknown user**
  

  • Rank: Professional
  • Registered: 16.03.02
  • last visit: 21.10.09
  • Posts: 2334

  [34] 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!
  
  :)

  • [35]
  •   [36]
•  
•  

Users online

• ccandreva [38],
• rmburkhead [39]

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