Membership: List CURRENT USER items only

Hi Guys.

I have a client area where;

1. Users table identifies which projects users have access to:
Table: project_list
Value: num
Lable: title

2. Project List Table, which has a number of projects (project_list)

3. An "Articles" table that has a number of fields including a multi-select called "project";
Table: project_list
Value: num
Lable: title

I want the page to display only articles where the user is identified as part of that project;

// load records
list($articlesRecords, $articlesMetaData) = getRecords(array(
'tableName' => 'articles',
'where' => mysql_escapef(" project LIKE ? ", "%\t".$CURRENT_USER['project']."\t%" ),
'debugSql' => true,
));

But this gives no results... the debug shows:
SELECT SQL_CALC_FOUND_ROWS `articles`.* FROM `cms_articles` as `articles` WHERE ( project LIKE '% 1 2 %' ) ORDER BY createdDate DESC

and when I remove the where statement totally, I get all projects, and the the lables are "Project A, Project B" etc I've tried changing the articles "project" field to
Table: project_list
Value: num
Lable: num

but this doesn't help...

any advice?

Hi Tim,

What we need to do here is to take the tab (\t) separated list stored in $CURRENT_USER['project'] and turn it into an array of numbers. We can then use this array to create a custom WHERE clause looking for each number individually.

Try this:

$where = "";
$projectNumArray = explode("\t", trim($CURRENT_USER['project'], "\t"));

foreach ($projectNumArray as $projectNum) {
$where .= " project LIKE '%\t".intval($projectNum)."\t%' OR";
}

// remove last OR from $where
$where = rtrim($where, "OR");

// if where is empty, return no records
if (!$where) { $where = "num = '0'"; }

// load records
list($articlesRecords, $articlesMetaData) = getRecords(array(
'tableName' => 'articles',
'where' => $where,
));

Hope this helps

Hi Jason.

Thanks Great... works a treat.

In another area, I need to do a similar thing, however I need to dispay the label, rather than the value...

$projectNumArray = explode("\t", trim($CURRENT_USER['project'], "\t"));

needs to be something like;

$projectNumArray = explode("\t", trim($CURRENT_USER['project:label'], "\t"));

Can you help?

Hi Tim,

Unfortunately, $CURRENT_USER doesn't use the :labels pseudo field on list fields. However, if you want to get an array of the selected list labels, you can try this:

$projectLables = getListLabels('accounts', 'project', $CURRENT_USER['project']);

Taking this one step further, if you wanted an array where the index was the selected values, and the and the value was the label, you can combine the two arrays into 1 like this:

$projectNums = explode("\t", trim($CURRENT_USER['project'], "\t"));
$projectLables = getListLabels('accounts', 'project', $CURRENT_USER['project']);
$selectedProjects = array_combine($projectNums, $projectLabels);

Hope this helps