mysql column names

i have been having a issue with getting the right code to display a list of mysql column names depending on the data thats in the column.

e.g.

i have a bunch of users.

some can view pages that contain sales info others can view IT info.

so in the mysql i have setup a table that goes

user_id | sales | IT | other |
1 | 1 | 0 | 1 |
2 | 0 | 1 | 0 |

so now i want to make a list of pages to view for the users according to the true's in the DB( true = 1 )

so user one will login and only see a link to sales and other.
then user 2 will login and see only a link to IT.

and help would be great.
even if i have to change my logic.

 

I would use an array of three arrays of links, like

PHP:

$a=array('sales'=>array("sales.php","memos.php"),'IT'=>array("generalIT.php","test.php","debug.php","ITcp.php","ITmsgs.php"),'other'=>array("other.php","news.php"))

The keys should be the same as the names you use to access the db table. (Alternatively, you could have an array of the raw html to display, but giving just the urls allows you to change the html easier later.) The inside arrays correspond to the three flags in the db. You can then iterate through the flags, like

PHP:

for(i in $a)if(f[i]==1)echo('<a href="'+$a[i]+'">'+$a[i]+'</a>')

Here, f represents the function of getting the table contents of column i for the current user.

 

Sorry, I accidentally mixed some JavaScript in there. lol. It would be better as

PHP:

$a=array('sales'=>array('Sales Invoices'=>'invoices.php'),'IT'=>array('IT Control Panel'=>'itcp.php'),''=>array('News'=>'news.php','Other'=>'other.php'))

foreach($i in $a){
 if(f[$i]==1){//function to check database
  foreach($j in $a[$i]){
   echo('<a href="'+$a[$i][$j]+'">'+$j+'</a>');
  }
 }
}

The keys for the inner arrays will be displayed as the clickable text for that link.

 

awsome! ill give this a try. although i am struggling to grasp it now but im sure if i work with it ill get it down.

although the reason i asked about getting the column names is that i might have about 30 -40 different permissions that i would need to assign.

its not really the contents with in the page that im worried about but more the simple fact that is that user allows in that section.

i was also thinking that if i could get the column names i could make it dynamically update. if i wish to add another section add the permission into the DB then have it show on the users login page with out having to hardcode more in to the array.

i was thinking more along the lines of

<mind my trivial SQL here but im not sure how to code this>
GET *column name* WHERE *data for this column* = 1

this would be added to the top of a page to see if the use is allows to be there. using an if statement to with diplay a notification that they dont have rights and exit; or continue with the page.

 

I don't actually use SQL, but I imagined my function f($i) using a single value retrieval, but you can also use the way you gave in the last post to retrieve an array containing only the fields with a 1, which would be handy in making the links. You could then iterate that array, displaying the links in the array for each one

PHP:

//$r=array retrieved from db
//$a=the array I gave in the other post
foreach($i in $r){
 foreach($j in $a[$i]){
  echo(' <a href="'+$a[$i][$j]+'">'+$j+'</a>');
 }
}

You can see your method eliminates the if statement from the PHP, doing it in SQL instead.

As for the per page check, besides hard coding, I have solved this by making an include script that uses a second db that maps page urls to titles and required access types, and perhaps descriptions or other info. This is then included in each page which is to be protected. It exits if permission is denied the current user. This would mean a change to the above function, using another SQL table instead of $a, so that it could hold many fields, including url, title, and accesstype (although an array could be used, I guess.) You can then list the links by getting the pages of each accesstype that the user has. In checking a specific page, you get that pages' access type, and check the user's info to see if they have that access. If you don't want a second db, you can sometimes use an accesstype as part of the url, as in putting all the files for an accesstype in the same subdirectory, or appending the access type code to the filename. Then you just need to extract the access type from the url, and check to see if the user has that field set to 1. If there are few files, you could do this on a per url basis, so that there is a column for each file, but this could get very hard to manage as your site grows.