Permalinks trouble - listing from mulitple tables on a detail page

Sigh.

I answered my own question by looking in permalinks_dispatcher.php.

In case anyone else is suffering from this problem, you need to add the following after your initial call to load the detail record:

unset($_GET['num']);
unset($_REQUEST['num']);

Hey Rez,

There are a couple of things that are causing this issue, first off I'll explain how the permalinks plugin works:

1. You go to a URL that doesn't exist, like /this-is-my-blog-post.
2. The htaccess see's that there isn't a file called this, and redirects to the permalinks plugin.
3. The permalinks plugin checks if a URL like that is in the permalinks database, and gets the appropriate record if it is.
4. It loads the viewer file for the section that's linked to the permalink record.
5. It sets $_REQUEST['num'] to the record number stored in the permalink record.

So instead of the URL of /blog.php?this-is-my-blog-post 32, the permalinks plugin sets /blog.php?num=32.

This change has no effect on on the function whereRecordNumberInUrl(), as the number 32 is still the last item in the URL.

But it does have an impact on the default functionality of the getRecords function, as allowSearch is set to true by default. So the getRecords calls for $stateRecords and $global_postsRecords are seeing the num in the URL and are searching on it, which means they'd only return records with a num of 32. 

One way around this is to unset the num (as you've already done). The second option is to set allowSearch to false in your other getRecord calls:

  // load record from 'locations'

  list($locationsRecords, $locationsMetaData) = getRecords(array(
    'tableName'   => 'locations',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $locationsRecord = @$locationsRecords[0]; // get first record
  if (!$locationsRecord) { dieWith404("Record not found!"); } // show error message if no record found
  

  list($stateRecords, $stateMetaData) = getRecords(array(
    'tableName'   => 'state',
    'orderBy'     => 'name ASC',
    'allowSearch' => false,
  ));

  // if location selected, make the multi selections
  if ($locationsRecords){
    list($local_postsRecords, $local_postsMetaData) = getRecords(array(
      'tableName' => 'local_posts',
      'where' => "locations LIKE '%\t{$locationsRecord['num']}\t%'",
      //'debugSql' =>'true',
      'allowSearch' => false,
    ));
  }

  // if location selected, make the multi selections
  if ($locationsRecords){
    list($global_postsRecords, $global_postsMetaData) = getRecords(array(
      'tableName' => 'global_posts',
      'where' => "locations NOT LIKE '%\t{$locationsRecord['num']}\t%'",
      //'debugSql' =>'true',
      'allowSearch' => false,
    ));
  }

  list($page_headersRecords, $page_headersMetaData) = getRecords(array(
    'tableName'   => 'page_headers',
    'where'       => "`num` = '9'",
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));


Let me know if you have any questions.

Thanks,

Greg

Thanks for posting this Greg.

Is there a public reference for the CMSB library functions somewhere?  I feel like I'd get more out of the system if I fully understood what was going on under the hood...

Gord

Hey Gord,

We have some CMSB docs that go over the getRecords function in detail:

http://www.interactivetools.com/docs/cmsbuilder/index.html

Another option is to search through the files in the /lib/ directory of your CMSB install for a particular function, as this is where all of core functions are, and they're well documented. Here is the getRecords function notes:

/*
  list($records, $details) = getRecords(array(
    'tableName'           => 'listings', // REQUIRED, error if not specified, tableName is prefixed with $TABLE_PREFIX
    'where'               => '',         // optional, defaults to blank
    'orderBy'             => '',         // optional, defaults to $_REQUEST['orderBy'], or table sort order
    'limit'               => '',         // optional, defaults to blank
    'offset'              => '',         // optional, defaults to blank (if set but no limit then limit is set to high number as per mysql docs)
    'perPage'             => '',         // optional, number of records to show per page
    'pageNum'             => '',         // optional, page number to display defaults to $_REQUEST['page'] or 1
    'allowSearch'         => '',         // optional, defaults to yes, adds search info from query string
    'requireSearchMatch'  => '',         // optional, don't show any results unless search keyword submitted and matched
    'requireSearchSuffix' => '',         // optional, search fields must end in a suffix such as _match or _keyword, original field=value match search is ignored
    'loadUploads'         => '',         // optional, defaults to yes, loads upload array into upload field
    'loadCreatedBy'       => '',         // optional, defaults to yes, adds createdBy. fields for created user
    'loadListDetails'     => '',         // optional, defaults to yes, adds $details with prev/next page, etc info
    'loadPseudoFields'    => false,      // optional, defaults to yes, adds additional fields for :text, :label, :values, etc
    'orWhere'             => '',         // optional, adding " OR ... " to end of where clause
    'useSeoUrls'          => false,      // optional, use SEO urls, defaults to no

    'leftJoin'      => array(        // Note: leftJoins require you to use fully qualified fieldnames in WHERE and ORDER BY, such as tablename.fieldname
      'grocery_aisle' => 'aisleNum', // foreign table => local field (that matches num in foreign table)
      'brands'        => 'brandNum',
      'otherTable'    => 'ON mainTable.foreignKey = foreignTable.num',
    ),

    'ignoreHidden'            => false,  // don't hide records with hidden flag set
    'ignorePublishDate'       => false,  // don't hide records with publishDate > now
    'ignoreRemoveDate'        => false,  // don't hide records with removeDate < now
    'includeDisabledAccounts' => true,   // include records that were created by disabled accounts.  See: Admin > Section Editors > Advanced > Disabled Accounts

    'addSelectExpr'           => 'NOW() as _currentDate',   // add expression to SELECT, useful for generating pseudo-fields or calculated values
    'groupBy'                 => '',                        // optional, defaults to blank
    'having'                  => '',                        // optional, defaults to blank

    'useCache'      => true,       // use cache - requires cache plugin
    'debugSql'      => false,      // optional, display SQL query, defaults to no
  ));
*/

I'd also recommend having a look into the following functions which I use regularly in projects:

• mysql_select($tableName, $whereStatement); - Select records from a specific table (simplified version of getRecords)
• mysql_get($tableName, $recordNum); - Get a single record from a section.
• array_groupBy($recordList, $indexField); - Group an array of records together on one field.
• mysql_delete($tableName, $recordNum);  - Delete a single record.
• mysql_insert($tableName, $insertArray); - Insert a record into a section.
• mysql_update($tableName, $recordNum, $whereStatement, $insertArray); - Update a record for a particular section.

Cheers,

Greg

Useful info. Thank you!