Solved

Access database value passing and proper joining in ColdFusion

Posted on 2003-11-09
7
262 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
  • 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Assisted Solution

by:procept
procept earned 500 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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now