Listing connected information inside a list

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

By hiroko - January 31, 2015

Hello,

I cannot figure out how to do this. Please help.

I am listing the accounts information and I would like to show the 'event_genre' field in a different table called 'event'.

So I tried using left join like this.

  // load records from 'accounts'
  list($accountsRecords, $accountsMetaData) = getRecords(array(
    'tableName'   => 'accounts',
    'loadUploads' => true,
    'allowSearch' => false,
'leftJoin'      => array(  
  // foreign table => local field (that matches num in foreign table)  
'event' => 'num',
 ), 
  ));

and on my html,

<div class="galleryList">
            <h2><a href="/en/galleryDetail.php?gallery=<?php echo $record['num'] ?>"><?php echo htmlencode($record['fullname_e']) ?></a></h2>
        <?php if($record['event.event_genre']  == 'tour'): ?>
        <div class="galleryDetail_event">
          <p>&raquo;Gallery Tour</p>
        </div>
        <?php endif ?>
        <?php if($record['event.event_genre'] == 'talk'): ?>
        <div class="galleryDetail_event">
        <p>&raquo;Gallery Talks / Lectures</p>
        </div>
        <?php endif ?>
        <?php if($record['event.event_genre'] == 'workshop'): ?>
        <div class="galleryDetail_event">
        <p>&raquo;Workshop</p>
        </div>
        <?php endif ?>
        <?php if($record['event.event_genre'] == 'exhibition'): ?>
        <div class="galleryDetail_event">
        <p>&raquo;Exhibition</p>
        </div>
        <?php endif ?>
        <?php if($record['event.event_genre'] == 'service'): ?>
        <div class="galleryDetail_event">
        <p>&raquo;Other Services</p>
        </div>
        <?php endif ?>
</div>

I am not getting the right result. The output becomes 'event' record num = 'account' record num.

What I wanted to do was to refer to the 'gallery' field's value 'num', which is a pulldown list on the 'event' section. 

Then, list the 'event_genre' field on the viewer.

The 'gallery' field's value is 'num' and the label is 'fullname'. 

How can I make this work?

Thank you,

Hiroko

By hiroko - February 4, 2015

Hello Claire,

Thank you for your reply.

I’m not sure if I can explain this clear.

I think I am dealing with 3 tables.

Accounts table: contains informations of galleries

event table : exhibition information

event_genre table : category list of event types

In event’ table, I have a 2 multi pulldown list field. One is named ‘gallery’, which refers to ‘accounts’ table, and the other is ‘event_genre’, which refers to the ‘event_genre’ category table.

What I need to do is to make a list of all the galleries (accounts) and include information about there event_genre of the 'event' table. 

Maybe it is easier to show you the page.

http://www.tokyoartantiques.com/en/gallery-list/

for now, I have a check box in the accounts table to show this, but it would be easier if I could refer to the event table.

Hiroko

By claire - February 11, 2015

Hi Hiroko

I think you should probably do this using multiple getRecords or mysql_select calls to keep it simple. Do you know how to do this?

--------------------

Claire Ryan
interactivetools.com

Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/