info from two tables on one page

5 posts by 2 authors in: Forums > CMS Builder
Last Post: December 14, 2018   (RSS)

By willydoit - December 11, 2018

Hi all,

I have a small issue in that I have a table with a field called category, the value of which is populated from a drop down list from another table, using the demo code the page looks like the code below and all works as expected except! the value I want to filter against is the field "category" and I cannot get that to work.

I want to filter by test-1.php?category=History but it doesnt work, if I run the page without filters the list is produced correctly and the page shows two values for category which are in the format

Category (value): 5
Category (label): Airport Routes

If I try  test-1.php?category=History I get no results found and if I try  test-1.php?category=2 the record displayed is filtered on the "record number" not the category value

What I need to be able to do is to apply a search filter in the html link that filters against the value held in Category (label)

I suspect it is something to do the fact that the field "category" is holding a value from another table but I don't know what to do to turn it into a value I can filter against. In the field editor for category I have selected "num" for the field option values and for field option labels I have title if the helps

Thanks in advance for any help provided

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
  /* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */
  
  // load viewer library
  $libraryPath = 'cmsb/lib/viewer_functions.php';
  $dirsToCheck = array('/home/sites/4b/1/128ffc9a32/public_html/','','../','../../','../../../');
  foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
  if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

  // load record from 'information_section_headers'
  list($information_section_headersRecords, $information_section_headersMetaData) = getRecords(array(
    'tableName'   => 'information_section_headers',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => false,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $information_section_headersRecord = @$information_section_headersRecords[0]; // get first record
  if (!$information_section_headersRecord) { dieWith404("Record not found!"); } // show error message if no record found

?>
 </head>


<body>

  

  <!-- STEP2: Display Record (Paste this where you want your record to appear) -->
    <h1>Multiple Data List Headers - Detail Page Viewer</h1>
      Record Number: <?php echo htmlencode($information_section_headersRecord['num']) ?><br/>
      Title: <?php echo htmlencode($information_section_headersRecord['title']) ?><br/>
     
      Content: <?php echo $information_section_headersRecord['content']; ?><br/>
      Category (value): <?php echo $information_section_headersRecord['category'] ?><br/>
      Category (label): <?php echo $information_section_headersRecord['category:label'] ?><br/>
     
</body>
</html>

By daniel - December 12, 2018

Hi willydoit,

Changing the 'where' and 'allowSearch' options in your getRecords() call to something like this may get you on the right track:

  list($information_section_headersRecords, $information_section_headersMetaData) = getRecords(array(
    'tableName'   => 'information_section_headers',
    'where'       => '',
    'loadUploads' => false,
    'allowSearch' => true,
    'limit'       => '1',
  ));

With your current setup, this should allow URLs that use the num (e.g. "test-1.php?category=2") to work. If you would like to filter by the title instead, you'll also need to change the option values field to "title." However, if you do this, note that it could cause unexpected results if two categories ever share the same title, so it may be worthwhile to update the title field's settings to be "Unique" and remove that possibility.

Hope that helps! Let me know if you have any further questions.

Thanks,

Daniel
Technical Lead
interactivetools.com

By willydoit - December 13, 2018

Hi Daniel,

The search filter works if I use the title field, however to expand a little.

I am using two tables to provide information on the list page and I wanted both tables to be filtered on the same field ie "category"

so

table 1 = "information_pages" contains lots of records that each have a category field to allow me to filter and list records with the same category value ie all records relating to Beaches

table 2 - "information_section_headers" which contains  a number of records (1 for each category value) containg title and description text relevant to that category.

both those tables use a third table "categories_list" to provide a drop down selection for the category value. ie Beaches, Harbour, shopping etc

I have done this to ensure that any new categories created are automatically available when creating records in tables 1 & 2

What I am looking to achieve is to create a menu link such as "information-pages.php?category=Beaches" and have the page display the listings of all records where the category value matches Beaches in table 1 and also provide the title and description text from table 2 from the record which has the category value Beaches.

For whatever reason the records from table 1 "information_pages" display correctly but I cannot get any content from table 2 to display on the page.  I am sure it is something simple that I am doing wrong or missing due to my lack of knowledge in php and sql so any help provided in the simplest form so even I can understand would be very much appreciated.

At the moment my head code is

 list($information_section_headersRecords, $information_section_headersMetaData) = getRecords(array(
    'tableName'   => 'information_section_headers',
    'where'       => '',
    'loadUploads' => false,
    'allowSearch' => true,
    'limit'       => '1',
  ));
  


  // load records from 'information_pages'
  list($information_pagesRecords, $information_pagesMetaData) = getRecords(array(
    'tableName'   => 'information_pages',
    'orderBy'     => 'RAND()',
    'loadUploads' => true,
    'allowSearch' => true,
  ));

and my display code is

                    <?php foreach ($information_section_headersRecords as $record): ?>
    title here <?php echo htmlencode($record['title']) ?><br/>
      
     content here <?php echo $record['content']; ?><br/>
		
      <?php endforeach ?>  




          <?php foreach ($information_pagesRecords as $record): ?>
            <li>
            <?php foreach ($record['uploads'] as $index => $upload): ?>
          <?php if ($index >= 1) { continue; } // limit uploads shown ?>
            
             
                <a data-description="data-description" data-largesrc="<?php echo htmlencode($upload['thumbUrlPath2']) ?>" data-title="<?php echo htmlencode($record['title']) ?>" href="#">
                    <img alt="<?php echo htmlencode($record['title']) ?>" src="<?php echo htmlencode($upload['thumbUrlPath2']) ?>" />
                </a>
                <?php endforeach ?>
                <h3>
                    <?php echo htmlencode($record['title']) ?> <br>
					<?php echo htmlencode($record['telephone']) ?>
                </h3>  
                <div class="gallery1-content">
                    <div>
                        <h3>
                           <?php echo htmlencode($record['title']) ?>
                        </h3>
                      
                        <p>
                            <?PHP echo maxWords($record['content'],50) ?> <br>
							<?php echo htmlencode($record['telephone']) ?><br>
							<?php echo htmlencode($record['contact_email']) ?>
                        </p>
                        <a href="<?php echo $record['_link'] ?>"class="btn btn-primary">Read More..</a>
                            
                            
                            
                        <p></p>
                    </div>
                </div>
            </li>
            <?php endforeach ?> 

By daniel - December 13, 2018

Hi Willydoit,

It looks like the code you have is generally correct for what you're trying to do, so the issue might be based on the CMS configuration.

Does Table 1 filter as you expect it to based on the category URLs? (e.g. "information-pages.php?category=Beaches") If so, then I would first double check that the category list field is set up exactly the same for Table 2 as well.

If neither table is filtering properly with the category URLs, make sure that in the list fields' option configuration (Section Editor > click "modify" for the table > click "modify" for the category field > Field Options > List Options), you have "Use this field for option values" set to "title".

Let me know if that helps!

Thanks,

Daniel
Technical Lead
interactivetools.com

By willydoit - December 14, 2018

Hi Daniel,

Thanks for the tips, issue is now fixed.

Thanks again