Help! Confused...

14 posts by 2 authors in: Forums > CMS Builder
Last Post: May 26, 2013   (RSS)

Hi Everyone

I have two multi-select tables.  Table 1 has unique records.  In Table 2, I have news articles each dealing with a different squadron, sometimes more than one article.  I use the values/labels of the 'squadron' field from Table 1 in a dropdown list.

What I am trying to do is to select a 'squadron' value and have it display all the records in table 2 that match.

Hope I explained this properly!

Tx!

--
northernpenguin
Northern Penguin Technologies

"Any sufficiently advanced technology
is indistinguishable from magic."
........Arthur C. Clarke

By Jason - May 6, 2013

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,

---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

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

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.

--
northernpenguin
Northern Penguin Technologies

"Any sufficiently advanced technology
is indistinguishable from magic."
........Arthur C. Clarke

By Jason - May 7, 2013

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 Sauchuk - Project Manager
interactivetools.com

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

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>

--
northernpenguin
Northern Penguin Technologies

"Any sufficiently advanced technology
is indistinguishable from magic."
........Arthur C. Clarke

By Jason - May 8, 2013

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 Sauchuk - Project Manager
interactivetools.com

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

Thanks Jason.  Everything is working properly now.

--
northernpenguin
Northern Penguin Technologies

"Any sufficiently advanced technology
is indistinguishable from magic."
........Arthur C. Clarke

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?

--
northernpenguin
Northern Penguin Technologies

"Any sufficiently advanced technology
is indistinguishable from magic."
........Arthur C. Clarke

By Jason - May 22, 2013

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

---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

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

Thanks Jason!

That worked really well.

--
northernpenguin
Northern Penguin Technologies

"Any sufficiently advanced technology
is indistinguishable from magic."
........Arthur C. Clarke