Simple Join of two tables for

4 posts by 2 authors in: Forums > CMS Builder
Last Post: February 17, 2017   (RSS)

By Twocans - February 16, 2017

Hello,

I would be grateful for any input.
I have been testing the CMS trying to get a better understanding of it. I wanted to try completing a join.

I have a simple join  and it works, I attach an image showing both the full sql and the results.

// load records from 'test_table_1'

list($test_table_1Records, $test_table_1MetaData) = getRecords(array(
'tableName' => 'test_table_1',
'debugSql' => true,
'loadUploads' => true,
'allowSearch' => false,
'leftJoin' => array(
'test_table_2' => 'ON test_table_1.num = test_table_2.table2fk'
)
));

I have a little question about the foreach, I have the foreach below. and it works showing the info from the test_table_1 table, 

http://www.matesee.com/zjoined.php

-------------------------------------------------------//

<table width="760" border="0" cellpadding="10">
<tbody> <?php foreach ($test_table_1Records as $record): ?>
<tr>
<td><?php echo htmlencode($record['num']) ?></td>
<td> <?php echo htmlencode($record['title']) ?></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr> <?php endforeach ?>
</tbody>

-------------------------------------------------------//

My question is how do I also call from the other table, at present I get all from test_table_1 in the foreach statement, what would I have to do for the test_table_2, do I need a nested foreach.

I have many things so far even lol

<?php foreach ($test_table_1Records as $record) && ($test_table_2Records as $record2): ?>

on using the foreach waht do I need to also include the test_table_2

thanks for any help

Kenny

Attachments:

join.jpg 30K

By Twocans - February 17, 2017

Hi sorry, 

I got it to work. 

// load records from 'test_table_1'
list($test_table_1Records, $test_table_1MetaData) = getRecords(array(
'tableName' => 'test_table_1',
'debugSql' => true,
'loadUploads' => true,
'allowSearch' => false,
'leftJoin' => array(
'test_table_2' => 'ON test_table_1.num = test_table_2.table2fk'
)

<table width="760" border="0" cellpadding="10">
<tbody> <?php foreach ($test_table_1Records as $record): ?>
<tr>
<td><?php echo htmlencode($record['test_table_2.num']) ?></td>
<td> <?php echo htmlencode($record['test_table_2.title']) ?></td>
<td><?php echo htmlencode($record['title']) ?></td>
<td>&nbsp;</td>
</tr> <?php endforeach ?>
</tbody>
</table>

<?php if (!$test_table_1Records): ?>
No records were found!<br/><br/>
<?php endif ?>

By Twocans - February 17, 2017

Thank you very much Ross,

I will avail of that, 

And also make use of this other stuff that I was not aware of.

http://www.interactivetools.com/docs/cmsbuilder/viewer_options.html

Cheers

kenny