Desperately need help with Join
3 posts by 2 authors in: Forums > CMS Builder
Last Post: August 8, 2017 (RSS)
By dlsweb - August 7, 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>
By Dave - August 8, 2017
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
interactivetools.com