Solved

Access database value passing and proper joining in ColdFusion

Posted on 2003-11-09
7
283 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
WAMP server installation, getting error popups, DLLs missing 5 78
Column Spacing 3 80
whm high memory usage in processes 7 92
Setting Up Local Lan Web Server 1 61
This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to Import and export files in WordPress. 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 : Click on Too…

825 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