Displaying results based on 'status'

Hi Carole,

Here's a recipe from my CMSB Cookbook http://www.thecmsbcookbook.com that might help...

Best,

Jerry Kornbluth

SORTING BY GROUP AND INSERTING GROUP HEADINGS IN A LIST VIEWER (ANOTHER APPROACH) - Mar 23rd, 2012

This approach seems to be a bit easier to implement but it only worked with a bit of help from Jason Sauchuk, from
Interactive Tools.

I wanted to allow for an expanding list of categories (actually board of director positions in an organization) that
could be managed by my client from the management interface, without resorting to any coding changes.

The first thing I did was to create a multi-record editor called board_of_director_positions with only one text field
called position. I set the sort to dragSortOrder DESC so that the positions would appear in the correct order.

Then, in the accounts editor, since some board members held 2 positions, I populated 2 single value list fields
(board_of_director_position_1 and board_of_director_position_2)  using the board_of_director_positions table as the
source, the num field for the option values and the position field for the label values.

Then I used array_pluck after the list records call to create a variable containing a list of the positions so that I
could display their names in my viewer 

<?php

 list($accountsRecords, $accountsMetaData) = getRecords(array(
    'tableName'   => 'accounts',

  ));

 list($board_of_director_positionsRecords, $board_of_director_positionsMetaData) = getRecords(array(
    'tableName'   => 'board_of_director_positions',
   ));

  $directorposition = array_filter(array_pluck($board_of_director_positionsRecords, 'position')); ?>

In the body of the viewer, I looped through and listed all of the positions, and then looped through all of the accounts
editor records for matches to those positions.

After much consternation, Jason suggested adding the pseudo field :label to the accounts record fields, since otherwise
I was trying to compare a record number to a text label. And that popped the office holders names into their correct
slots.

?php foreach ($directorposition as $position): ?> 

<h3 class="your_class_1"><?php echo strtoupper($position); ?></h3>

<?php foreach ($accountsRecords as $record): ?>

<?php if ($record['board_of_director_position_1:label'] == $position || $record['board_of_director_position_2:label'] ==
$position) : ?>

<span class="your_class_2"><?php echo strtoupper($record[full_name']); ?></span>   
<?php endif ?>
                                             
<?php endforeach ?> <hr />   
<?php endforeach ?> 

This concept is easily applied to something like an FAQ page where there are multiple categories and multiple questions
in each category.

Create a multi-record FAQ CATEGORIES editor with one text field called category, a dragSortOrder field and an optional
hidden checkbox.
Create a multi-record FAQ editor with a single value pull down list field populated using the faq_categories table as
the source, the num field for the option values and the position field for the label values.

Then, as above, I used array_pluck after the list records call to create a variable containing a list of the categories
so that I could display their names in my viewer 

  $faqgroup = array_filter(array_pluck($faq_categoriesRecords, 'category'));

Again as above, In the body of the viewer, I looped through and listed all of the categories, and then looped through
all of the FAQ editor records for matches to those categories.

<?php foreach ($faqgroup as $group): ?> 
<h1 class="your_class_1"><?php echo strtoupper($group); ?></h1>
<?php foreach ($faqRecords as $record): ?>
<?php if ($record['category:label'] == $group) : ?>
<?php $question = htmlspecialchars($record['question']); ?> <span class="your_class_2"><?php echo strtoupper($question);
?></span>
<div align="left" class="your_class_3"><?php echo maxWords($record['answer'], 25); ?>
<?php if (wordCount($record['answer']) > 25) : ?><a class="special" href="<?php echo $record['_link']; ?>"><span
class="body-text-bold-10">... read more</span></a><?php endif; ?>
</div>   
<?php endif ?>
                                             
<?php endforeach ?>
<hr />   
<?php endforeach ?> 

Since I'm using a detail page for the complete answers, I included functions called maxWords and wordCount to show a
"read more" link only if there were more than 25 words in the answer. These functions are defined in the head section of
my viewer, with:

<?PHP
function maxWords($textOrHtml, $maxWords) {
$text = strip_tags($textOrHtml);
$words = preg_split("/\s+/", $text, $maxWords+1);
if (count($words) > $maxWords) { unset($words[$maxWords]); }
$output = join(' ', $words);

return $output;
}
?>

<?PHP
function wordCount($textOrHtml) {
$text = strip_tags($textOrHtml, "<b></b><i></i>");
$words = preg_split("/\s+/", $text);

return count($words);
}
?>

Hi Jerry

Thanks for this.  It looks very confusing, but I'll try and wade thru it.

Hi Carole,

One way to do it is to have a query for each of the status and add where clause to select specific status.
For example:

// new on market
$newOnMarketPropertiesRecords = mysql_select('properties', 'status = "available" ORDER BY createdDate LIMIT 4');

// under offer
$underOfferPropertiesRecords  = mysql_select('properties', 'status = "under offer" ORDER BY createdDate LIMIT 4');

// leased
$leasedPropertiesRecords      = mysql_select('properties', 'status = "leased" ORDER BY createdDate LIMIT 4');

And then use foreach loop to display the contents under their designated headings:

// for "new on market" heading
foreach ($newOnMarketPropertiesRecords as $newOnMarketPropertiesRecord){
  echo htmlencode($newOnMarketPropertiesRecord['name']);
}

Hope this helps!

Cheers,

Hi Daryl

I think this is assuming each of the status types are a different section within the cmsb (they aren't) - a property can be any of the available, under offer or leased (drop down) which changes the status on the single file.

I've tried the below and am getting endless errors.  

Hi Carole,

See if you can use something from this code from my FAQ page.

It uses a list field called category

You can use sort order to determine how the records are presented, so "newest first", would be 'orderBy' => 'createdDate DESC', as below.

Good luck,

Jerry Kornbluth

<?php
     // load viewer library
  $libraryPath = 'cmsAdmin/lib/viewer_functions.php';
 $dirsToCheck = array('/path_to_your_server/','','../','../../','../../../');
  foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
  if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

<?php
list($faqRecords, $faqMetaData) = getRecords(array(
    'tableName'   => 'faq',
    'orderBy' => 'createdDate DESC',
  ));
  ?>
  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>FAQ</title>
</head>

<body>
<table width="100%" border="0" cellpadding="0">
  <tr>
    <td align="center" class="heading_font"><br />
      FAQ </td>
  </tr>
  <tr>
    <td align="center" class="text_font"> CLICK/TAP FOR ANSWERS... </td>
  </tr>
  <?php $old_group = ''; // init blank var.
foreach ($faqRecords as $record):
$group = $record['category:label']; // load sub-group value from record. ?>
  <tr>
    <td align="left" ><span class="text_font">
      <?PHP
if ($group != $old_group) {echo "<h2>$group</h2>";}?>
      </span>
      <?php   

if  (!preg_match("/^http:\/\//i", $record['url'])) {
 $record['url'] = "http://" . $record['url'];   }

?>
      <a class="special-link" href="
                            <?php echo $record['_link'] ?>" target="_blank">
      <?php $question = ($record['question']); ?>
      <?php $link_text = ($record['link_text']); ?>
      <span class="text_font-bold">- - <?php echo ucwords($question); ?></span><br />
      </a>
      <hr align="center" color="#<?php echo $nawa_colorsRecord['menu_background_color'] ?>" /></td>
  </tr>
  <?PHP $old_group = $group; // retain sub-group name before moving to new record. ?>
  <?php endforeach; ?>
</table>
</body>
</html>

Hi Carole,

I wrote my example assuming that the properties are in one cmsb section, ie: "properties" and the status types are in a drop-down list field (not from separate sections) in that "properties" section.
And a property/record in "properties" section can have one of these status types: available, under offer or leased
Is that correct?

So for this line from my example:

// new on market
$newOnMarketPropertiesRecords = mysql_select('properties', 'status = "available" ORDER BY createdDate LIMIT 4');

We're pulling 4 records (LIMIT 4) from 'properties' section where 'status' is 'available'.
While the one below pulls 4 records from the same 'properties' section but, this time, the status of the records that we're pulling is 'under offer'

// under offer
$underOfferPropertiesRecords  = mysql_select('properties', 'status = "under offer" ORDER BY createdDate LIMIT 4');

As for the errors, if after replacing my example section/table name 'properties' with the actual section where you store your properties and the actual status type still returns some errors, can you please post the errors you've received?

Thanks,

Hi Carole,

The code that I posted are just examples so you might need to replace some variables and values.

I'm guessing that your properties are stored in 'lease_warehouse' table/section so you need to replace my example code's table name 'properties' with 'lease_warehouse' (replace it with correct table/section name if my guess is wrong).
Here's how the "new on market" MySql query and code to display it might look like:

<?php
// new on market
$newOnMarketPropertiesRecords = mysql_select('lease_warehouse', 'status = "available" ORDER BY createdDate LIMIT 4');
?>

<?php if (!$newOnMarketPropertiesRecords): ?>
   No records were found!<br/><br/>
<?php else; ?>
   <?php foreach ($newOnMarketPropertiesRecords as $record): ?> 
        Some code here..
   <?php endforeach ?>
<?php endif; ?>

Let me know if that works or post any errors you get if it didn't.

Thanks,