Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access database value passing and proper joining in ColdFusion

Posted on 2003-11-09
7
Medium Priority
?
311 Views
Last Modified: 2013-12-24
I am using Microsoft Access for my database. I have two tables, one for login info (username, password and roleCode), and another table that has roleCode, as well as other values that need to appear if the roleCode search upon matches. For example, the username is "John", the password is "x123z" and the roleCode is "Admin". In the second table, the roleCode will vary from "Admin" to "user" to "Editor", etc.

First - The access database is not joined properly (I have tried to make sure the the "roleCodes" from both tables are linked, but have not suceeded yet), so I think that is one area that is causes problems. Is it the only area?
Second - When a user enters his username and password, I need to have the roleCode variable information accessible in the display page. How do I do that?
0
Comment
Question by:msukow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 11

Expert Comment

by:hart
ID: 9712358
is the rolecode table have unique rolecodes then create a primarykey on the role code...

and make a relationship btween rolecode in login table and rolecode in rolecodes table.
since the login table rolecode is a foriegn key, it will not allow any other values other that from the rolecode table..

then u won't have a datamismatch problem...

then when a user tries to log in i would write this in the checklogin page

<cfquery name="qry_checklogin" datasource="ur dsn name">
       select username,rolecode from login
       where username = '#form.username#'
                 and password = '#form.userpassword#'
</cfquery>

<cfif qry_checklogin.recordcount gt 0>
    <CFCOOKIE NAME="LoginCookie"
        VALUE="#qry_checklogin.Username#~#qry_checklogin.rolecode#"
        EXPIRES="NOW">
    <CFHEADER NAME="Refresh" VALUE="0; URL=DisplayPage.cfm">
<cfelse>
    <!--- invalid user redirect accordingly --->
</cfif>


--------------------------------------------------------
now in the displaypage.cfm

u can access the rolecode of the logged in user from the cookie...
<Cfoutput>#ListGetAt(Cookie.LoginCookie,2,'~')#</cfoutput>

also u can get the corresponding values from rolecode table using this cookie value

<cfquery name="qry_rolecodes" datasource="ur dsn name">
       select rolecode,whatever from rolecodes
       where rolecode = '#ListGetAt(Cookie.LoginCookie,2,'~')#'
</cfquery>

Regards
Hart
0
 

Author Comment

by:msukow
ID: 9712385
The roleCode tabel doesn't have unique values (there are multiple users that can be admins, as well as editors). What I need to do is check the username and password, and then display records that the user has access to. Example, there are 100 records, of which user1 can access only 45 (his roleCode matches 45 of the records). I want to beable to have the table displayed on the displayPage of the 45 records he can see and edit, without access to the other 55.
0
 
LVL 11

Expert Comment

by:hart
ID: 9712410
ok u can do this
1. keep a unique id in the login table other than username [a numerical field]
2. now store this id in rolecode table as well. [thus this becomes a foreign key to login table]

or if u can't create a unique field now then store the username in the rolecode table..
this will help u to relte between the two tables easily..

so after login in the cookie store the username / unique id from login tabe

and in the display page hit a query like this
<cfquery name="qry_rolecodes" datasource="ur dsn name">
       select rolecode,whatever from rolecodes
       where username = '#ListGetAt(Cookie.LoginCookie,1,'~')#'
</cfquery>

this query will give u all the roles of that particluar user

hope u understand what i mean..

Regards
Hart
0
Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

 
LVL 4

Assisted Solution

by:procept
procept earned 1500 total points
ID: 9720754
Hi,

hart is quite right, although I would suggest using session variables instead of cookies:

<cfquery name="qry_checklogin" datasource="ur dsn name">
       select username,rolecode from login
       where username = '#form.username#'
                 and password = '#form.userpassword#'
</cfquery>


<cfif qry_checkLogin.recordCount>
   <cfset session.username = qry_checklogin.username>
   <cfset session.rolecode = qry_checklogin.rolecode>
</cfif>

on the display page you can use the session vars to select the records:
<cfquery name="qry_rolecodes" datasource="ur dsn name">
       select rolecode,whatever from rolecodes
       where rolecode = '#session.rolecode#'
</cfquery>

The downside to this: if you are using CF 5 or earlier, you need to lock each and every use of session vars or you run the risk of loosing data! (With CF MX you don't need to lock shared scope variables any longer.)

One word of advice: make a proper relational database model, that's much better...

- make each table have a numerical primary key (can be a combined key of two values, too!)
- make aseparate table for joining users and roles

Could be something like this (always using fieldname: datatype, PK = Primary Key, unique value):
user-table:
userID: int  PK
username: text
roleID: int
...

roles-table
roleID: int  PK
rolecode: text

join-table (combination of userID and roleID would be unique)
userID: int
roleID: int

records-table:
recordID int PK
roleID int
field1 whatever

HTH,

Chris
0
 
LVL 11

Expert Comment

by:hart
ID: 9720802
i didn't suggest sessions because sessions have a problem if u r accessing behind a firewall [proxy server]. the session values keep on interchanging..

where as cookies avoid this problem..

Regards
Hart
0
 

Author Comment

by:msukow
ID: 9722714
Thanks -

I am trying your suggestions now and hope to have this fixed later today or tomorrow. I will keep you posted.
0
 
LVL 4

Accepted Solution

by:
procept earned 1500 total points
ID: 9753535
Hi,

> i didn't suggest sessions because sessions have a problem if u r
> accessing behind a firewall [proxy server]. the session values keep on interchanging..
>
> where as cookies avoid this problem..

Per default sessions are identified by one or two cookies (depending on the type of session you have), and the reason that sessions sometimes get lost is that firewalls or proxies either filter out the cookies (not the sessions!) or cache the pages.

If you use cookie-less sessions, your session vars are safe. (From this point of view... not talking about missing locks with session vars in CF 5 and earlier. ;-))

Chris



0

Featured Post

Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question