Solved

Access database value passing and proper joining in ColdFusion

Posted on 2003-11-09
7
302 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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

717 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