Membership "filter"

17 posts by 4 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: February 9, 2011   (RSS)

Re: [Rusty] Membership Filter

By Jason - November 4, 2010

Hi Rusty,

You're query is almost correct. You just need to modify your quotes a little. Try this one:


list($customerRecords, $customerMetaData) = getRecords(array(
'tableName' => 'customer',
'where' => whereRecordNumberInUrl(1). " AND createdByUserNum= '". $CURRENT_USER['num'] ."'",
'limit' => 1,
));

In this query, if there is a record number in the url, it will retrieve that record IF it was created by the user currently logged in, otherwise it will return nothing. If there is NO record number in the url, it will return the first record in the customer table created by that user.

As for your second question, what are you using to tell which manager created the record and which user they created it for? Let me know and we'll work out a solution.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Membership Filter

By Rusty - February 8, 2011 - edited: February 8, 2011

Is there a way to filter a record so that multiple users could see the same record?

Within the section editor that I'm trying to configure for multiple users (who must be logged in) I have a mutli-select list called permission that populates from the User Accounts with a label of User Full Name and a value of UserNum.

When an admin selects items from the multi select list, those values (UserNums) are written into the DB and it's written as Tab separated.

Right now, I have it setup so that the where function is like this:
'where' => 'permission = ' . @$CURRENT_USER['num'],


I did try a having instead of the where but it still broke when I added a second user to the permissions list to see that page.

The issue with that is that it only functions properly if ONLY ONE user is selected in the list (whether it's a checkbox list, or a multi-select list). As soon as there are two values, it no longer functions.

How can we configure CMS so that an admin can have the ability to enable mutliple users to have persmission to see a page (contents of a page from a section editor), but not other users?

I know what's happening is that the permssion field is being filled with data (the UserNum) and that it's tab separated, so we're dealing with a tab-separated array.

I must the explode the array, and do a check (IF??to compare if UserNum is present in the array. Does this sound accurate?

I'm currently hacking up some of the bits of code that were posted over here
Rusty

Re: [Rusty] Membership Filter

By Rusty - February 8, 2011 - edited: February 8, 2011

This is the bit of code I had been using to filter pages based upon a user being logged in and allowed privileges to a section. As stated above this (exact code) will only work with one user. As soon as multiple users are assigned to the array, it hides any record with multiple users allowed to see it.

list($formsRecords, $formsMetaData) = getRecords(array(
'tableName' => 'forms',
'perPage' => '25',
'loadUploads' => '0',
'allowSearch' => '0',
'where' => 'permission = ' . @$CURRENT_USER['num'],


));

I have been working on re-purposing a bit of code that was originally designed to populate radio buttons within the membership plugin. I started to second guess myself after staring at what I had been working on...


<?php
// Assign The Fieldname we are looking to sort through to the disingenious var name: fieldname
Make sense
$fieldname = 'permission';
// Assign the key that we are looking for the var name: needle
Sure
$needle = @$CURRENT_USER['num'];
// Trim the fat from our array so we are left with just the values, assign it
// to the array fieldValues
For some reason I think this should happen AFTER a getListOptions.. what what do I know
$fieldValues = explode("\t",trim(@$_REQUEST[$fieldname],"\t"));
//Check to see if wasabi is in the array of fieldValues then perform what's in curly braces...
BUT I know I'm leaving this out:

(getListOptions('forms', $fieldname) as $value => $label):

You'll see I changed it from accounts to forms, because that's the section editor the data is stored in.
But the tricky thing is so what if it works, and it sees that it's in the array, further down where it's listing the information
I need to properly get the where statement to filter it....
More or less I need the WHERE to check to see if the needle is in the haystack and only get records that have the needle in stack.

if(in_array($needle,$fieldValues)): {


list($formsRecords, $formsMetaData) = getRecords(array(
'tableName' => 'forms',
'perPage' => '25',
'loadUploads' => '0',
'allowSearch' => '0',
'where' => 'permission = ' . @$CURRENT_USER['num'],


));
}
?>
<?php endif?>




What about doing a check For Each record to see if the current UserNum is in the array, and if true, returns a True output. Then have the MySQL query only pull records Where if UserNum is in_array = true....

list($formsRecords, $formsMetaData) = getRecords(array(
'tableName' => 'forms',
'perPage' => '25',
'loadUploads' => '0',
'allowSearch' => '0',
'where' => @$CURRENT_USER['num'] . " = " .


));
Rusty

Re: [Rusty] Membership Filter

By Rusty - February 8, 2011 - edited: February 8, 2011

So... I've been tinkering around with incorporating a way similar to how the Fav's pulls records... getting warmer.

Perhapsh I need to set $permissionsQuery to be the array of the trimmed Permissions fieldset...

// get forms record nums
$tableOrTag = mysql_escape('forms'); // update this with your table or tag name
$currentUserNum = mysql_escape( @$CURRENT_USER['num'] );
// $permissionsQuery = "SELECT permission FROM {$TABLE_PREFIX}forms WHERE tableOrTag = '$tableOrTag'";
$fieldname = 'permission';
$needle = @$CURRENT_USER['num'];
foreach (getListOptions('forms', $fieldname) as $value => $label):{
$haystack = explode("\t",trim(@$_REQUEST[$fieldname],"\t"));

}
// load matching records
list($formsRecords, $formsMetaData) = getRecords(array(
'tableName' => 'forms', // update this with your table or tag name
// 'perPage' => '10',
'where' => " $needle IN ($haystack) ",
'loadCreatedBy' => false,
'allowSearch' => false,
'debugSql' => true,
));

But it gives me this:
SELECT SQL_CALC_FOUND_ROWS `forms`.*
FROM `cms_forms` as `forms`
WHERE ( 1 IN (Array) ) AND forms.hidden = 0
ORDER BY dragSortOrder DESC, form_number, price_per_unit
MySQL Error: Unknown column 'Array' in 'where clause'
Rusty

Re: [Rusty] Membership Filter

By Jason - February 8, 2011

Hi Rusty,

I think you're first attempt was actually the closest. If I understand correctly, you have a section called forms and in each record you want to have a multi-value list that is populated from the accounts table. When you're selecting your forms records, you want to make sure that the current user is selected for all the records returned. Is that right?

In that case, we don't have to worry about creating arrays. In CMS Builder, multi-value lists are stored as a tab (\t) separated string. That means, we know that if our current user number is in the list it would be in the form \tNUM\t regardless of how many other elements are there. We can put this directly in the WHERE clause using LIKE.

Give this a try:

list($formsRecords, $formsMetaData) = getRecords(array(
'tableName' => 'forms',
'where' => "permissions LIKE '%\t".intval($CURRENT_USER['num'])."\t%'",
'loadCreatedBy' => false,
'allowSearch' => false,

));

This will look for records where the permissions string has the current user number with a tab character on either side. The % signs mean that any other number of characters can appear on either side of the string.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Membership Filter

By Rusty - February 9, 2011

Hi Rusty,

I think you're first attempt was actually the closest. If I understand correctly, you have a section called forms and in each record you want to have a multi-value list that is populated from the accounts table. When you're selecting your forms records, you want to make sure that the current user is selected for all the records returned. Is that right?

In that case, we don't have to worry about creating arrays. In CMS Builder, multi-value lists are stored as a tab (\t) separated string. That means, we know that if our current user number is in the list it would be in the form \tNUM\t regardless of how many other elements are there. We can put this directly in the WHERE clause using LIKE.

Give this a try:

list($formsRecords, $formsMetaData) = getRecords(array(
'tableName' => 'forms',
'where' => "permissions LIKE '%\t".intval($CURRENT_USER['num'])."\t%'",
'loadCreatedBy' => false,
'allowSearch' => false,

));

This will look for records where the permissions string has the current user number with a tab character on either side. The % signs mean that any other number of characters can appear on either side of the string.

Hope this helps.


That more than helped, that worked EXACTLY how I wanted it to. Thanks for your assistance, and the guiding hand.
Rusty