2. PHPRunner General questions
  3. [SOLVED]  Filter list page based on several fields from user table
This topic is locked
[SOLVED]

 Filter list page based on several fields from user table

 		1/17/2012 3:09:20 PM
PHPRunner General questions
C
 copper21 author

Good Afternoon,
For some reason, I can not figure this out; I am assuming that this is a relatively easy thing to do. I need to filter the list page of the master table based on a couple different values. I have my main_event table which is the master table. The main_event table has a checkbox field called "restricted" I have a users table which contains the fields: username and work_location.
Right now the master table, main_event, shows all events regardless of who is logged in. I am thinking that I need a List Page: Before SQL Query event so that when a user logs in, I can restrict what events are listed based on their work_location AND if "restricted" is checked off or not.
So if I log in and I am from work_location "00" and "restricted" is checkmarked, I can see the event because work_location "00" can see "restricted" events, but someone who is not part of work_location "00" can not see it. If "restricted" is not checkmarked, everyone can still see the event. There are about 8 locations that will have access to see "restricted" events. Because the field "restricted" is a checkbox, the value will be 1 for restricted and 0 for not restricted.
Thanks in advance...this is the last thing I need to do for this project....almost there!
Brian
PHP 6.0 9948 SQL Database
C
 		cgphp 1/17/2012

Keep a session in the "After successful login" where storing the work location. You need also a table where storing permissions for work locations on restricted records.
C
 		copper21 author 1/17/2012

Cristian, thanks for the guidance. I was able to get this working and will post what I did hopefully to help someone else out... I did not have to make the additional table.
I was able to set the work location as a session variable in the List Page: Before Process of the master table
// Save Work Location as a Session variable
$str = "select userid, work_location from events_users where userid = '".$_SESSION["UserID"]."'";

$rs = CustomQuery($str);

$data = db_fetch_array($rs);
$_SESSION["work_location"] = $data["work_location"];
I then added the following code to List Page: Before SQL query from the master table
//Work locations with access to restricted records
if ($_SESSION["work_location"]!= '00')

if ($_SESSION["work_location"]!= '11')

if ($_SESSION["work_location"]!= '12')

if ($_SESSION["work_location"]!= '13')

if ($_SESSION["work_location"]!= '14')

if ($_SESSION["work_location"]!= '16')

if ($_SESSION["work_location"]!= '28')

if ($_SESSION["work_location"]!= '37')
$strWhereClause = whereAdd($strWhereClause, "restricted = '0'");
This shows all events to the people from the listed work_locations and does not show records to people not from the listed work locations.
C
 		cgphp 1/18/2012

I was able to set the work location as a session variable in the List Page: Before Process of the master table

// Save Work Location as a Session variable
$str = "select userid, work_location from events_users where userid = '".$_SESSION["UserID"]."'";

$rs = CustomQuery($str);

$data = db_fetch_array($rs);
$_SESSION["work_location"] = $data["work_location"];


If you move this piece of code in the "After successful login" event, it will be executed only one time. In the "List Page: Before Process" event it will be executed every time the list page is loaded.

I then added the following code to List Page: Before SQL query from the master table

//Work locations with access to restricted records
if ($_SESSION["work_location"]!= '00')

if ($_SESSION["work_location"]!= '11')

if ($_SESSION["work_location"]!= '12')

if ($_SESSION["work_location"]!= '13')

if ($_SESSION["work_location"]!= '14')

if ($_SESSION["work_location"]!= '16')

if ($_SESSION["work_location"]!= '28')

if ($_SESSION["work_location"]!= '37')
$strWhereClause = whereAdd($strWhereClause, "restricted = '0'");
This shows all events to the people from the listed work_locations and does not show records to people not from the listed work locations.


A more compact version of this:

$locations = array('00','11','12','13','14','16','28','37');

if( ! in_array($_SESSION["work_location"], $locations))

$strWhereClause = whereAdd($strWhereClause, "restricted = '0'");
C
 		copper21 author 1/18/2012

Thanks!
I placed the condensed code in the Before SQL on the list page. I then put the code in the after successful login and it works, but only if I add it before another code I have in that event. The problem is only the code listed first works; I need both to work. I am sure it is something easy to fix, but for some reason I cant figure that out. Here is the other code that I need in the after successful login:
All users will be set with a generic password "abc" and when they log in for the first time, they will be directed to the "My Account" page which is an edit page of the users table and will be forced to change password.
//Redirect to My Account Edit Page After Login
if ($data["password"]=="abc")
{
header("Location: My_Account_edit.php?editid1=".$data["userid"]);
exit();
}
else
{
header("Location: Public_Events_View1_list.php");
exit();
}
I need to combine both of them.
Thanks a million!