Login | Sign up | Toll-Free: 1-800-752-0455
 
 

Forum

 

3 posts by 2 authors in: Forums > CMS Builder
Last Post: August 8, 2017

I am lost using Join
Can someone please tell me how to do.

From accounts table, I want to show 'fullname' and 'email' and 'state'
and from festivals table, 'title' and 'begin_date'

accounts.num = festivals.createdByUserNum


// load records from 'accounts'
list($accountsRecords, $accountsMetaData) = getRecords(array(
'tableName' => 'accounts',
'perPage' => '25',
'loadUploads' => false,
'allowSearch' => false,
));

something like?
LEFT JOIN festivals ON accounts.num = festivals.createdByUserNum;


html
<?php echo htmlencode($record['fullname']) ?><br/>
<?php echo htmlencode($record['email']) ?><br/>
<?php echo htmlencode($record['state']) ?><br/>
??
<?php echo htmlencode($record['festivals.title']) ?><br>
<?php echo date("M j, Y", strtotime($record['festivals.begin_date'])) ?><br>

Hi dlsweb, 

This isn't an officially supported option, but it's not going away so you can try it out.  Try this: 

// load records from 'accounts'
list($accountsRecords, $accountsMetaData) = getRecords(array(
  'tableName' => 'accounts',
  'perPage' => '25', 
  'loadUploads' => false,
  'allowSearch' => false,
  'leftJoin' => array( // Note: leftJoins require you to use fully qualified fieldnames in WHERE and ORDER BY, such as tablename.fieldname
    'festivals' => 'ON accounts.num = festivals.createdByUserNum',
  ),
));

Let me know if that works for you.

An alternative approach is just to load the festivals and or account separately as in this post: 
https://www.interactivetools.com/forum/forum-posts.php?postNum=2240982#post2240982

Dave Edis - Senior Developer
interactivetools.com

Works great, Thanks so very much