Viewer Options

List Pages have a number of options that can be set at the top of the viewer code. The Code Generator only includes the options you need, so the code in your viewer may be shorter than the complete example below.

Code Example

<?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 = ['','../','../../','../../../','../../../../']; // add if needed: '/www/htdocs/'
  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 records from 'news'
  list($newsRecords, $newsMetaData) = getRecords(array(
    'tableName'          => 'news',     // REQUIRED, error if not specified, tableName is prefixed with $TABLE_PREFIX
    'limit'              => '3',        // optional, defaults to blank (specifies max number of records to show)
    'offset'             => '1',        // optional, defaults to blank (if set but no limit then limit is set to high number as per mysql docs)
    'perPage'            => '1',        // optional, number of records to show per page - loads page number from url as viewer.php?page=1
    'allowSearch'        => true,       // optional, defaults to true, adds search info from query string
    'requireSearchMatch' => false,      // optional, don't show any results unless search keyword submitted and matched
    'loadUploads'        => true,       // optional, defaults to true, loads upload array into upload field
    'loadCreatedBy'      => true,       // optional, defaults to true, adds createdBy. fields for created user
    'loadListDetails'    => true,       // optional, defaults to true, adds $details with prev/next page, etc info
    'useSeoUrls'         => false,      // deprecated, changes _link format only, defaults to no
    'where'              => '',         // optional, defaults to blank
    'orWhere'            => '',         // optional, adding " OR ... " to end of where clause
    'orderBy'            => '',         // optional, defaults to table sort order if undefined
    'joinTable'          => 'tablename',// optional, add results from another table that are created by the same users
    'debugSql'           => false,      // optional, display SQL query, defaults to no
  ));
?>

Loading the viewer library

The first lines locate and load viewer_functions.php by trying a series of relative paths, so viewers keep working when the website is moved between servers. If your page is nested more than four folders deep, add more '../' entries to $dirsToCheck, or add the absolute path shown in the code comment.

Option Reference

tableName

The MySQL database table name the viewer loads records from. Update this value if you modify the Database Table in CMS Setup > Database Editor.

limit

Specifies the maximum number of records to display. Setting 'limit' => '3' shows only the first 3 records. By default all records are displayed.

offset

Skips a number of records. Use an offset of 1 with a limit of 2 to display the 2nd and 3rd results.

perPage

Specifies the number of records per page when displaying paginated results with previous/next links. The current page number is loaded from the page URL query string value. Example: viewer.php?page=3

perPage can’t be combined with limit or offset. Setting both displays an error. Choose one approach.

pageNum

The page to show when using perPage. Defaults to the page URL query string value, or 1. Set this to override the page number from the URL. If the page number is past the last page, the viewer loads the last page instead.

allowSearch

Automatic searching is enabled by default. If you have multiple viewers on one page, set this to false to make some viewers display all results while ignoring search keywords.

requireSearchMatch

Viewers display matching records by default, even when no search keywords were entered. Setting this to true prevents results from displaying unless a non-blank search keyword was entered and matched.

requireSearchSuffix

By default, plain query string parameters such as ?color=blue are treated as exact-match searches. Set this to true to only accept search parameters with an explicit suffix (?color_match=blue, ?title_keyword=news, etc.) and ignore unsuffixed ones. Useful when your page uses query string parameters for other purposes. See Search for the suffix list.

loadUploads

Uploads are loaded by default so they can be displayed. If an upload field exists but isn’t displayed in your viewer, set this to false to reduce database queries.

loadCreatedBy

User account information for the user who created each record is added to results as createdBy. fields (createdBy.email, createdBy.fullname, etc.) by default. Set this to false to reduce database queries.

loadListDetails

The second variable returned by getRecords() ($newsMetaData in the example) contains information about the total results and page links. Set this to false to avoid loading this data. See List details below for the available keys.

getRecords() also returns a third element containing the section’s schema array, for advanced use: list($records, $metaData, $schema) = getRecords(...).

loadPseudoFields

Extra :text, :label, :values, :labels, and :unixtime pseudo-fields are added to list, checkbox, and date fields by default. Set this to false to skip generating them. See Pseudo-fields.

ignoreHidden

Records with the special hidden field checked are excluded by default. Set this to true to show hidden records as well.

ignorePublishDate

Records with a publishDate in the future are excluded by default. Set this to true to show unpublished records as well.

ignoreRemoveDate

Records with a removeDate in the past are excluded by default (unless their neverRemove checkbox is checked). Set this to true to show expired records as well.

includeDisabledAccounts

Only has an effect when the section’s option to hide records from disabled accounts is enabled under CMS Setup > Database Editor > Advanced. With that setting on, records created by disabled or expired user accounts are excluded; set this to true to include them anyway.

where

If you’re comfortable with MySQL, you can specify a custom MySQL WHERE clause. This clause works alongside automatic search parameters.

orWhere

For advanced MySQL users: specify a custom WHERE clause added after the search and where conditions as an “OR” condition. Use this when you want to show results matching a search query OR something else.

orderBy

Viewers use the Order By setting from the section’s List Page tab in CMS Setup > Database Editor by default. Override it with a comma-separated field list (example: author, title). Special sorting commands include:

Command	Effect
`fieldname DESC`	Sorts in descending order. With date fields, shows newest records first.
`fieldname+0`	Sorts as a numeric value. Use when numeric sorting produces unusual order (1, 2, 20, 21, 3, 4, 45, etc.).
`RAND()`	Randomizes results every time the viewer loads.

The orderBy field is a standard MySQL ORDER BY clause. If you’re comfortable with MySQL you can add any conditions you need.

A common combination shows the newest records first, such as the three most recent news articles on a homepage:

list($newsRecords, $newsMetaData) = getRecords(array(
  'tableName' => 'news',
  'limit'     => '3',
  'orderBy'   => 'publishDate DESC',
));

The Code Generator’s Record Sorting option only offers Default and Random, so add orderBy to the generated code by hand, or set the section’s Order By on its List Page tab and leave orderBy unset.

When no orderBy option is set, visitors can override the sort order from the URL with ?orderBy=fieldname or ?orderBy=fieldname DESC. Only those two formats are accepted, and the fieldname is validated against the section’s fields. Anything else is ignored.

selectExpr advanced

Overrides the SELECT clause of the query, replacing the default tablename.*. Example: 'selectExpr' => 'num, title'.

addSelectExpr advanced

Appends an extra expression to the SELECT clause without replacing the default field list, useful for calculated values. Example: 'addSelectExpr' => 'NOW() AS currentTime' makes $record['currentTime'] available.

useIndex advanced

Adds a MySQL USE INDEX (indexname) hint after the FROM clause to suggest which index the query should use.

groupBy advanced

Adds a MySQL GROUP BY clause for aggregating results. Example: 'groupBy' => 'category'.

having advanced

Adds a MySQL HAVING clause, used together with groupBy. Example: 'having' => 'COUNT(*) > 5'.

leftJoin advanced

Joins one or more other tables to the query with LEFT JOIN. Each array entry is either 'foreignTable' => 'localField', which joins the local field to the foreign table’s num column, or 'foreignTable' => 'ON custom clause' for a custom join condition:

'leftJoin' => [
  'brands'  => 'brandNum',                            // LEFT JOIN brands ON listings.brandNum = brands.num
  'authors' => 'ON listings.authorNum = authors.id',  // custom ON clause
],

Joined columns are returned under qualified keys such as $record['brands.name'], and can be searched from the query string by the same qualified names (see Search). When a column name exists in both tables, qualify it in where and orderBy (listings.status instead of status) to avoid ambiguous-column errors.

joinTable advanced

Loads records from another section created by the same user. If a viewer for a ‘listings’ section has 'joinTable' => 'homepage', each listing loads the first homepage record created by that user, making those fields available as homepage. fields.

debugSql advanced

For troubleshooting, set this to true to output all SQL queries as text before they are executed.

useCache deprecated

Deprecated. It enabled result caching through a caching plugin that is no longer available. Has no effect.

List details ($metaData)

When loadListDetails is enabled (the default), the second variable returned by getRecords() contains these keys:

Key	Description
`totalRecords`	Total number of matching records, regardless of paging.
`totalPages`	Total number of pages (1 when `perPage` isn’t set).
`page`	The current page number.
`perPage`	Records per page, from the `perPage` option.
`pageResultsStart` / `pageResultsEnd`	Record numbers of the first and last result on the current page (e.g. “Showing 11 to 20 of 45”).
`prevPage` / `nextPage`	Previous and next page numbers, or `''` when there is no previous/next page.
`prevPageLink` / `nextPageLink`	Ready-made URLs for the previous and next pages. Current query string arguments (search keywords, etc.) are carried forward.
`firstPageLink` / `lastPageLink`	Ready-made URLs for the first and last pages.
`noRecordsFound`	True when no records matched (on page 1).
`invalidPageNum`	True when a page number past the last page was requested.
`_listPage` / `_detailPage`	The List Page Url and Detail Page Url from the section’s Viewer Urls settings.

Pseudo-fields

getRecords() adds computed values to each record, such as :label and :unixtime variants of list, checkbox, and date fields, a ready-made _link detail page URL, and createdBy.* account fields. See the Pseudo-field Reference.

Searching from the URL

Query string parameters filter viewer results automatically, with suffixes selecting the match type (?title_keyword=news, ?date_year_min=2005). See Search for the suffix list, search forms, and multi-field searches.

Special fieldnames

Fields named hidden, publishDate, removeDate, and neverRemove control whether records appear in viewer results (see the ignore* options above). See Special Fieldnames for the full list of reserved names.

Documents CMS Builder 3.83