Permalinks trouble - listing from mulitple tables on a detail page
7 posts by 3 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: April 18, 2015 (RSS)
By gord - April 8, 2015
Hi,
I am having trouble getting the Permalinks plugin to work with a detail page on which I am using getRecords() to list records from several related tables.
For some reason, it seems to be appending 'num = ' (number of the detail record first loaded) to the SQL, and is failing. Here is an excerpt of my code:
// load viewer library
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('/home/aporia/public_html/','','../','../../','../../../');
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 artist details
list($artistsRecords, $artistsMetaData) = getRecords(array(
'tableName' => 'artists',
'where' => whereRecordNumberInUrl(1),
'limit' => '1',
'debugSql' => true,
));
$artistsRecord = @$artistsRecords[0]; // get first record
// show error message if no matching record is found
if (!$artistsRecord) { dieWith404("Record not found!"); }
// load youtube video records
list($youtube_linksRecords, $youtube_linksMetaData) = getRecords(array(
'tableName' => 'youtube_links',
'where' => sprintf("artist = '%s'", $artistsRecord['num']),
'debugSql' => true,
));
Here is the debug output/SQL that is being produced:
<xmp>SELECT SQL_CALC_FOUND_ROWS `artists`.* FROM `cms_artists` as `artists` WHERE ((num = '34') AND ((`num` = '34'))) ORDER BY active_artist DESC, name ASC LIMIT 1</xmp>
<xmp>tableName = 'artists' AND fieldName IN ('photo') AND recordNum IN (34) ORDER BY `order`, num</xmp>
<xmp>SELECT SQL_CALC_FOUND_ROWS `accounts`.* FROM `cms_accounts` as `accounts` WHERE (`num` IN (1)) ORDER BY fullname, username</xmp>
<xmp>SELECT SQL_CALC_FOUND_ROWS `youtube_links`.* FROM `cms_youtube_links` as `youtube_links` WHERE ((artist = '34') AND ((`num` = '34'))) ORDER BY artist, release_date</xmp>
It seems that whereRecordNumberInUrl() is now completely unnecessary - which makes me suspect there is a global variable being set by Permalinks??
Everything was working before I installed Permalinks...
Is there something I can unset before calling getRecords() again?
Any help would be greatly appreciated!!
Gord
By gord - April 8, 2015
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']);
By rez - April 16, 2015 - edited: April 16, 2015
Thank you for following up with your solution. This drove me crazy ALL day and I wouldn't have figured it out.
For me, the page was loading as expected but the elements that were coming from other tables (called local_posts and global_posts below) were not loading onto the page. I discovered much later that this was happening at the permalink URLs and not the original details.php?33 URLs; they were fine. Then I finally realized being the permalink situation, I had something to search here.
The following appears to have fixed it:
// 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
unset($_GET['num']);
unset($_REQUEST['num']);
list($stateRecords, $stateMetaData) = getRecords(array(
'tableName' => 'state',
'orderBy' => 'name ASC'
));
// 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'
));
endif;
// 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'
));
endif;
list($page_headersRecords, $page_headersMetaData) = getRecords(array(
'tableName' => 'page_headers',
'where' => "`num` = '9'",
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
?><!DOCTYPE html>
So that is where you meant to add the solution, right? it seems to be working for me now. I don't understand what it is going on yet. Care to explain? It's frustrating that the plugin can't handle this.
Also, what is odd is that I have a duplicate page set up for Canada locations (the above is the detail page for USA locations) and the problem isn't happening. Literally a copied page with a different detail page name and changed the name of the tables. However, I guess I better add the fix anyway.
// load record from 'locations'
list($ca_locationsRecords, $ca_locationsMetaData) = getRecords(array(
'tableName' => 'ca_locations',
'where' => whereRecordNumberInUrl(0),
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
$ca_locationsRecord = @$ca_locationsRecords[0]; // get first record
if (!$ca_locationsRecord) { dieWith404("Record not found!"); } // show error message if no record found
list($ca_provinceRecords, $ca_provinceMetaData) = getRecords(array(
'tableName' => 'ca_province',
'orderBy' => 'name ASC'
));
// if location selected, make the multi selections
if ($ca_locationsRecords):
list($local_postsRecords, $local_postsMetaData) = getRecords(array(
'tableName' => 'local_posts',
'where' => "ca_locations LIKE '%\t{$ca_locationsRecord['num']}\t%'",
//'debugSql' =>'true'
));
endif;
// if location selected, make the multi selections
if ($ca_locationsRecords):
list($global_postsRecords, $global_postsMetaData) = getRecords(array(
'tableName' => 'global_posts',
'where' => "ca_locations NOT LIKE '%\t{$ca_locationsRecord['num']}\t%'",
//'debugSql' =>'true'
));
endif;
list($page_headersRecords, $page_headersMetaData) = getRecords(array(
'tableName' => 'page_headers',
'where' => "`num` = '9'",
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
?><!DOCTYPE html>
By gregThomas - April 17, 2015
Hey Rez,
There are a couple of things that are causing this issue, first off I'll explain how the permalinks plugin works:
- You go to a URL that doesn't exist, like /this-is-my-blog-post.
- The htaccess see's that there isn't a file called this, and redirects to the permalinks plugin.
- The permalinks plugin checks if a URL like that is in the permalinks database, and gets the appropriate record if it is.
- It loads the viewer file for the section that's linked to the permalink record.
- 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
PHP Programmer - interactivetools.com
By gord - April 17, 2015
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
By gregThomas - April 17, 2015
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
PHP Programmer - interactivetools.com