2. PHPRunner General questions
  3. [SOLVED]   Restric records list - based on NT login
This topic is locked
[SOLVED]

  Restric records list - based on NT login

 		8/22/2011 08:52:14
PHPRunner General questions
M
 marimach77 author

Dear Colleagues,
This is my first post here, so welcome everybody <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=17591&image=1&table=forumtopics' class='bbc_emoticon' alt=':rolleyes:' /> and sorry for my not so good english.
I'm really newbie in PHP and I'm still learning it. I used CodeCharge Studio in the past to create simple PHP pages but today I found PHPRunner. After few hours I can create a much more than in CCS. It's really nice tool. Unfortunately, now I need to create some PHP coding part which is too dificult for me. What I need to have?
I have 2 SQL tables Desktop and User2Site.

Table User2Site has fields:

UserLogin

SiteID
Value example:

User1, Country1

User1, Country2

User2, Country3
Table Desktop has fields:

ID

SiteID

Brand

Model

Owner
Value example:

1, Country1, Dell, Latitude, BBBBB
The SiteID field creates the relation between the both tables.
How it should work?
When User1 open a page, his NT login name should be detected by PHP function. Then, it should be compared with User2Site table. If detected login is User1, he should see only these records matching relation SiteID to UserLogin. I hope this is clear. IF not, please let me know, I will try to explain it better.
Thanks

Brgds

Mariusz
C
 		cgphp 8/22/2011

Hi Mariusz,
your explanation is clear. Check this article http://xlinesoft.com/phprunner/docs/implementing_single_sign_on.htm to detect the NT user login.
Then to filter out the records matching relation SiteID, you have to enter in the "Before SQL query" event of the list page the following statement:

global $conn;

$rs = CustomQuery("SELECT SiteID FROM User2Site WHERE UserLogin='".$_SESSION['UserID']."'",$conn);

$sites = "(";

while($record = db_fetch_array($rs))

{

$sites .= "'".$record['SiteID']."',";

}

$sites .= "'')";
$strWhereClause = whereAdd($strWhereClause,"SiteID in ".$sites);
M
 		marimach77 author 8/22/2011

Hi Cristian,
Can I just use the code below in the "Before SQL query" event of the list page:

global $conn;

$rs = CustomQuery("SELECT SiteID FROM User2Site WHERE UserLogin='".$_SERVER["REMOTE_USER"]."'",$conn);

$sites = "(";

while($record = db_fetch_array($rs))

{

$sites .= "'".$record['SiteID']."',";

}

$sites .= "'')";
$strWhereClause = whereAdd($strWhereClause,"SiteID in ".$sites);


Brgds

Mariusz
C
 		cgphp 8/22/2011

It is intended to be used in the "Before SQL query" event of the Desktop list page. Where do you want to use it ?
M
 		marimach77 author 8/22/2011



It is intended to be used in the "Before SQL query" event of the Desktop list page. Where do you want to use it ?


As I see, my version of code is working fine <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=60287&image=1&table=forumreplies' class='bbc_emoticon' alt=':rolleyes:' /> but anyway, thanks a lot for the tip.
Then, I would like to make it a little bit advanced. I added new field RoleName to User2Site table. New field will have only 2 values: LocalAdmin and MasterAdmin.
So, current content of User2Site table is:
Value example:

User1, Country1, LocalAdmin

User1, Country2, LocalAdmin

User2, Country3, MasterAdmin
If any user has MasterAdmin role, he should see ALL records in Desktop table. If any user has LocalAdmin role, he should see only records related to User2Site table.
C
 		cgphp 8/22/2011
global $conn;

$rs = CustomQuery("SELECT RoleName FROM User2Site WHERE UserLogin='".$_SERVER["REMOTE_USER"]."' LIMIT 1",$conn);

$data = db_fetch_array($rs);

if($data['RoleName'] == 'LocalAdmin')

{

$rs = CustomQuery("SELECT SiteID FROM User2Site WHERE UserLogin='".$_SERVER["REMOTE_USER"]."'",$conn);

$sites = "(";

while($record = db_fetch_array($rs))

{

$sites .= "'".$record['SiteID']."',";

}

$sites .= "'')";

$strWhereClause = whereAdd($strWhereClause,"SiteID in ".$sites);

}
M
 		marimach77 author 8/23/2011


$rs = CustomQuery("SELECT RoleName FROM User2Site WHERE UserLogin='".$_SERVER["REMOTE_USER"]."' LIMIT 1",$conn);




Thanks a lot Cristian. It seems to be working OK but I have to remove "LIMI 1" string. I got an error message about the script incorrect syntax.
C
 		cgphp 8/23/2011

Sorry, I assumed that you were working with mysql. You are using SQL server 2005 instead. Try this:

global $conn;

$rs = CustomQuery("SELECT TOP 1 RoleName FROM User2Site WHERE UserLogin='".$_SERVER["REMOTE_USER"]."'",$conn);

$data = db_fetch_array($rs);

if($data['RoleName'] == 'LocalAdmin')

{

$rs = CustomQuery("SELECT SiteID FROM User2Site WHERE UserLogin='".$_SERVER["REMOTE_USER"]."'",$conn);

$sites = "(";

while($record = db_fetch_array($rs))

{

$sites .= "'".$record['SiteID']."',";

}

$sites .= "'')";

$strWhereClause = whereAdd($strWhereClause,"SiteID in ".$sites);

}