Help! Confused...

Hi,

No problem, we'll just need a little extra information to help get us started.

What field from Table 1 (I'm assuming the squadron table) are you using as the value in your dropdown in table 2?  What is the name of your drop down?  Is it a single or multi-select drop down?  How are you trying to set up your search on the front end?

Let me know and we'll see what we can work out.

thanks,

I am using the record number, num, as the value and the 'squadron' field as the label in table 1.

I called the drop down "squad".  It is a single select drop down.

There is no search really.  When a record is created in table 2, the user decides which squadron to assign it to.  There may be more than one record assigned to a squadron.

What I want is for the user to access the front end website, select a squadron from the drop down, and have all the matching records from table 2 displayed.  I'm doing this with a combo page, Witt the drop down in the sidebar, and the records in the main content area.

Hi,

Okay, probably the easiest  way to do this would be to allow CMSB to do the searching for you.  What you can do is have a <select> box on your page with all the values from the "squad" drop down in your section.

For example:

<select name = "squad">
  <option value = "">-Select-</option>
  
  <?php foreach (getListOptions("news", "squad") as $value => $label): ?>
    <option value = "<?php echo $value;?>" <?php selectedIf($value, @$_REQUEST['squad']);?>><?php echo htmlspecialchars($label);?></option>
  <?php endforeach ?>
  
</select>


You'll need to replace "news" with the name of your news articles section.  Have the form post to the same page.  As long as you haven't turned off "allowSearch" on your getRecords call to the news section, CMS Builder should automatically retrieve all the records where the squad value in the drop down has been selected.

Hope this helps get you started.

Jason:  I keep getting "Record not found".  Below is my actual code:

   // load detail record from 'squadron_news'
  list($squadron_newsRecords, $squadron_newsMetaData) = getRecords(array(
    'tableName'   => 'squadron_news',
    'where'       => whereRecordNumberInUrl(1), // If no record # is specified then latest record is shown
    'loadUploads' => true,
    'allowSearch' => true,
    'limit'       => '1',
  ));
  $detailRecord = @$squadron_newsRecords[0]; // get first record
  if (!$detailRecord) { dieWith404("Record not found!"); } // show error message if no record found

  // load list records from 'squadron_news'
  list($squadron_newsRecords, $squadron_newsMetaData) = getRecords(array(
    'tableName'   => 'squadron_news',
    'loadUploads' => true,
    'allowSearch' => true,
  )); 


and the html:

            <div id="contentContainer"><!-- Start main content wrapper -->
                <div id="content"><!-- Start content -->
                
                <h2>Squadron News</h2>
                    
                        
                        <b>Record Detail</b><br/>
      Record Number: <?php echo htmlencode($detailRecord['num']) ?><br/>
      Squadron (value): <?php echo $detailRecord['squadron'] ?><br/>
      Squadron (label): <?php echo $detailRecord['squadron:label'] ?><br/>
      Title: <?php echo htmlencode($detailRecord['title']) ?><br/>
      Content: <?php echo $detailRecord['content']; ?><br/> 
                        
                                            
                </div><!-- End content -->
                <div class="clearer"></div>
            </div><!-- End main content wrapper -->
            <div id="sidebarContainer">
                <div id="subMenu"></div>
                <div class="clearer"></div>
                <div id="sidebarHeader"></div>
                <div id="sidebar">
             

<form name="choose" action="" method="get">
Choose a squadron:
<select name="squadron" size="1" style="width: 160px;">
<option value="">&lt;select a squadron&gt;</option>
  <?php foreach (getListOptions("squadron_news", "squadron") as $value => $label): ?>
    <option value = "<?php echo $value;?>" <?php selectedIf($value, @$_REQUEST['squadron']);?>><?php echo htmlspecialchars($label);?></option>
<?php endforeach; ?>
</select>
<input type="submit" value="Submit">
</form>

        </div>
        </div>

Hi,

It looks like the problem here is that you have 2 getRecords calls at the top of the page, both retrieving records from squadron_news into a variable called $squadron_newsRecords.  However, the first call is using "whereRecordNumberInUrl".  Since your form using the GET method, a record number is being put up in the URL that isn't matching the search.  If you use "debugSql => true," you'll see exactly what's happening.

The solution here would be to remove the first getRecords call and the "Record not found!" check.  Also in your HTML, you'll want to use a foreachloop to output the results, as more than 1 record could be retrieved.

Hope this helps,

Jason:  I just noticed something.  When you first load the page, without choosing the squadron from the dropdown, all the records are displayed. 

Is there a way to not display any records until they choose a squadron from the dropdown?

Hi,

Sure.  The easiest way to do this is to put an if statement around your foreach loop so that the loop won't execute until the form has been submitted.

For example:

<?php if (@$_REQUEST['squadron']): ?>

Hope this helps

Thanks Jason!

That worked really well.