Simple Join of two tables for

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

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

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 ross - February 17, 2017

Hi Twocans

Glad you got that going.

I wanted to add a comment about a really handy function we built into CMS Builder.

It's called "showme()" and what it does is output the contents of an array so you can see what is available.

For example, if you put it inside your "foreach loop" like this:

<?php foreach ($test_table_1Records as $record): ?>
<?php showme($record);
<?php endforeach ?>

you would get a list of all the values available in $record.

It's really handy for figuring out what you have and what you can use.

Keep in mind that the output is not going to be pretty so it's definitely strictly a debugging tool.  

Let me know any questions.

-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

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