Admin viewer to access all users

2 posts by 2 authors in: Forums > CMS Builder
Last Post: March 4, 2019   (RSS)

By bkerns - March 4, 2019

How do I get at all records from accounts for admin only reporting reporting? Does the Website Membership limit to only the owner's records?

Id like to Left Join two tables and do something like this on a webpage that loops through and formats the results if user is admin:

SELECT
cmsb_accounts.DistrictName,
cmsb_accounts.email,
cmsb_accounts.Address,
cmsb_accounts.City,
cmsb_accounts.State,
cmsb_accounts.Zip,
cmsb_accounts.County,
cmsb_accounts.Area,
cmsb_accounts.DistrictContact,
cmsb_accounts.Phone,
cmsb_accounts.Acres,
cmsb_accounts.Population,
cmsb_dataform.Biennium,
cmsb_dataform.RecordStatus,
cmsb_dataform.TotalFT,
cmsb_dataform.TotalPT,
cmsb_dataform.TotalTemp,
cmsb_dataform.OpCash,
cmsb_dataform.SpecUse,
cmsb_dataform.MillsRcv,
cmsb_dataform.MillsFullReq,
cmsb_dataform.MillsNum,
cmsb_dataform.AddlFunds,
cmsb_dataform.AddlFundsUse
FROM cmsb_accounts LEFT JOIN cmsb_dataform ON cmsb_accounts.createdByUserNum = cmsb_dataform.createdByUserNum
WHERE NOT cmsb_accounts.isAdmin OR cmsb_dataform.Biennium ="2019-2021"
ORDER BY cmsb_dataform.RecordStatus DESC,
cmsb_dataform.OpCash DESC,
cmsb_dataform.MillsRcv DESC,
cmsb_dataform.AddlFunds DESC,
cmsb_dataform.TotalFT ASC,
cmsb_dataform.TotalPT ASC,
cmsb_dataform.TotalTemp ASC;

By gregThomas - March 4, 2019

Hey Bkerns,

So this is for the front end of the website, and you want to show admin users all records, but filter non-admin users to their own records only?

If so, you could do something like this:

<?php

#1) Include the viewer functions
include_once 'cmsb/lib/viewer_functions.php';

#2) Redirect the user to the login page if they're not logged in.
if (!$CURRENT_USER) { websiteLogin_redirectToLogin(); }

#3) Create a where statement that checks if the user is an admin, if they're not filter the records to only display their own.
$where = "";
if(!$CURRENT_USER['isAdmin']) {
$where = "`createdByUserNum` = ".intval($CURRENT_USER['num']) ;
}

#4) Get the account records and filter using the where statement.
list($accountsRecords, $accountsMetaData) = getRecords(array(
'tableName' => 'accounts',
'where' => $where,
'loadUploads' => true,
'allowSearch' => false,
));

Note: This is example code, so you might need to make a few changes to get it working.

So the code above will create a where filter statement in the variable $where that only shows the user their own records if they're not an admin account. This is then passed into the getRecords function that will return an array of account records.

If you don't want to add this where statement into the getRecords function and would prefer to use the MySQL statement in your previous post, you'll just need to add the where statement into it instead.

Thanks,

Greg

Greg Thomas







PHP Programmer - interactivetools.com