filtering some querys 1

[maximos]

Hi everyone, i'm not an expert so please bear with me

I have a simple form for loging in with a user name and password, after the user is logged in, he sees a very simple access database ,

Is there anyway, i could set it up so depending on the user to view his own data line in the database table, here is the database layout :
SO (for examle ) when john logs in, only sees his info and not anyone else, but yet i want to have a master user to view all..


memberID memeberName Password memeberinfo
1 John John1 $40
2 Bob Bob1 $50


Here is my application varification,

<cflogin>


<cfquery name=&quot;qValidLogin&quot; datasource=&quot;stock&quot;>
SELECT *
FROM tblmember
WHERE tblmember.MemberName = '#Form.username#' AND member.Password = '#Form.password#' </cfquery>

<cfif qValidLogin.RecordCount>
<cfloginuser name=&quot;#Form.username#&quot; password=&quot;#Form.password#&quot;
<cfset loggedin=&quot;1&quot;>

<cfelse>
<cfinclude template=&quot;index.cfm&quot;>
<h3>Your information is not valid.Please try again.</h3>

</cfif>
</cfif>
</cfif>

</cflogin>

And here is my page that is viewd after logging in :

<cfquery name=&quot;member_table&quot; datasource=&quot;stock&quot;>
SELECT *
FROM tblmember
</cfquery>

<cfoutput query=&quot;member_table&quot; <td>#member_table.MemberID#</td> <td>#member_table.MemberName#</td>
<td>#member_table.info#</td>
</cfoutput> </table></td>

Any help or reference i'll be greatly appreciated,

i was advised to add a new feild called &quot; upowers&quot; and set it to 0 or 1 (1 for the admin personal and 0 for regular user) and modefy my output query, to

<cfquery name=&quot;member_table&quot; datasource=&quot;stock&quot;>
SELECT *
FROM member
<cfif member.upowers eq 0>
where memberID = #memeberName#
</cfif>
</cfquery>

But it didn't work and , not sure if the where statment should be in the cfif tag, also data type of the new feild is a number, not sure it that is correct or not ?

Thanks alot in advace
Max Tadros

 

[ecobb]

Here's a little trick I learned with cflogin. When you set your cfloginuser, you can combine multiple variables for the &quot;Name&quot; attribute. So, try this:

(by the way, in the code you posted above, the ending &quot;>&quot; is missing from your cfloginuser tag.)

<!--- IF there was a record returned from the qValidLogin query --->
<cfif qValidLogin.RecordCount NEQ 0>
<!--- SET login... --->
<!--- This will take the values MemberID and Username and combine them, seperated by a comma. --->
<cfloginuser name=&quot;#qValidLogin.MemberID#, #Form.username#&quot; password=&quot;#Form.password#&quot;>
<cfset loggedin=&quot;1&quot;>
<cfelse>
<!--- Deny login... --->
<cfinclude template=&quot;index.cfm&quot;>
<h3>Your information is not valid.Please try again.</h3>
</cfif>
<!--- Verify User has logged in--->
<cfif LoggedIn EQ 1>
<!--- Seperate the list of values stored in the &quot;Name&quot; attribute of the cfloginuser tag. --->
<!--- SET the MemberID variable to be the ID (first list value in cfloginuser) of the user who logged in --->
<!--- The getAuthUser() Function is what CF uses to read the values in the &quot;name&quot; attribute of the cfloginuser tag. It can be used to call these values anywhere in your site, provided the user is logged in. --->
<cfset MemberID = ListFirst(getAuthUser())>
<!--- SET the MemberName to be the remaining values in the cfloginuser tag --->
<cfset MemberName = ListRest(getAuthUser())>
</cfif>


Now you can use:

<cfquery name=&quot;member_table&quot; datasource=&quot;stock&quot;>
SELECT *
FROM member
WHERE memberID = #memberID#
</cfquery>

This will only return the results for the specified logged in member.

Hope This Helps!

Ecobb
- I hate computers!

 

[maximos]

Thanks for the reply ECobb,
but i got an error saying that i'm missing the roles element
so does that mean i have to add a feild called roles ?

 

[ecobb]

No, there is no need to add another field. The &quot;roles&quot; attribute of the cfloginuser tag is used to specify roles for that user, so this would probably be a good place to use your &quot;upowers&quot; setting. You could do something like:

<cfloginuser name=&quot;#qValidLogin.MemberID#, #Form.username#&quot; password=&quot;#Form.password#&quot; roles=&quot;#qValidLogin.upowers#&quot;>

This would set each user's &quot;role&quot; to be whatever value you had specifed for the &quot;upowers&quot; field in the database. (0=user, 1=administrator, etc...)

ColdFusion has a built in function to deal with this specific attribute of the cfloginuser tag, it's called IsUserInRole. Basically, it checks the &quot;roles&quot; attribute of the cflogin tag to verify it's value. So, anytime you wanted to check to see if the user had a specific &quot;role&quot; value, you do something like:

<cfif IsUserInRole(1)>
whatever....
<cfelse>
You do not have permissions...
</cfif>

This would only execute IF the user had an &quot;upowers&quot; value of 1, ELSE they do not have permissions.

So, using your inital example, when a user logs in, if he/she is an administrator (upowers=1) they see everything, if they are a regular user (upowers=0) they see only their information, you could try this in your SELECT statement:

<cfquery name=&quot;member_table&quot; datasource=&quot;stock&quot;>
SELECT *
FROM member
<cfif IsUserInRole(0)>
WHERE memberID = #memberID#
</cfif>
</cfquery>

Hope This Helps!

Ecobb
- I hate computers!

 

[maximos]

Thanks again ECobb,
But i have one more imbarassing question,
i keep getting an error (Variable MEMBERID is undefined) is that variable coming from the form ? if so, i don't have any feild in my form for that,

so what i did is i changed the Where statmement to
<cfquery name=&quot;member_table&quot; datasource=&quot;stock&quot;>
SELECT *
FROM member
<cfif IsUserInRole(0)>
<!--- WHERE memberID = #memberID# --->
where memberName = '#memberName#'
</cfif>
</cfquery>

Notice i added the ' ' but still getting the same error (Variable MEMBERNAME is undefined)

i did some reading on the sql where, in theary this should work but its not, not sure what i'm doing wrong, ? or do i have to add anything in my form ?

Thanks again,

 

[ecobb]

Sorry about that, I wasn't thinking about running the &quot;member_table&quot; query on a seperate page. Oops.

This line of code in my original post is where you are setting the variables MemberID and MemberName

<!--- The getAuthUser() Function is what CF uses to read the values in the &quot;name&quot; attribute of the cfloginuser tag. It can be used to call these values anywhere in your site, provided the user is logged in. --->
<cfset MemberID = ListFirst(getAuthUser())>
<!--- SET the MemberName to be the remaining values in the cfloginuser tag --->
<cfset MemberName = ListRest(getAuthUser())>


This needs to be used on the page that your &quot;member_table&quot; query is running on (before the query), or CF won't know what the value of the variable is.

Try this:

<cfset MemberID = ListFirst(getAuthUser())>
<cfset MemberName = ListRest(getAuthUser())>

<cfquery name=&quot;member_table&quot; datasource=&quot;stock&quot;>
SELECT *
FROM member
<cfif IsUserInRole(0)>
WHERE memberID = #memberID#
</cfif>
</cfquery>



Hope This Helps!

Ecobb
- I hate computers!

 

[maximos]

it looks like i'm almost there,
But i got an syntex error when i put the :
<cfif IsUserInRole(0)>
WHERE memberID = #memberID#
</cfif>
inside the query tag, if i put it outside the query tag it works but it returns all data to be viewed,

also the error only shows in macromedia in the application area , and not the web

any clues where i went wrong, ?

just to clarify where i stand so far .. i have 3 pages, the first has the form, the second is the application.cfm, and the third is my result page ;

here is the code my application.cfm file:
-------------------------------------------------------------
<cfquery name=&quot;qValidLogin&quot; datasource=&quot;stock&quot;>
SELECT * FROM tblmember
WHERE tblmember.memberName = '#Form.username#' AND Password = '#Form.password#'
</cfquery>

<cfset MemberID = ListFirst(getAuthUser())>
<cfset MemberName = ListRest(getAuthUser())>
<cfloginuser name=&quot;#Form.username#&quot; password=&quot;#Form.password#&quot; roles=&quot;#qValidLogin.upowers#&quot; >
<cfif IsUserInRole(1)>
whatever....
<cfelse>
You are the admin
</cfif>
<cfif IsUserInRole(0)>
whatever
<cfelse>
you are the user
</cfif>


<cfset loggedin=&quot;1&quot;>
<cfelse>
<cfinclude template=&quot;index.cfm&quot;>
<h3>Your information is not valid.;Please try again.</h3>

</cfif>

</cfif>
</cfif>

</cflogin>

-----------------------------------------------------------
here is the code for the result page:
---------------------------------------------------------
<cfset MemberID = ListFirst(getAuthUser())>
<cfset MemberName = ListRest(getAuthUser())>

<cfquery name=&quot;member_table&quot; datasource=&quot;stock&quot;>
SELECT *
FROM tblmember
<cfif IsUserInRole(0)>
WHERE memberID = #memberID#
</cfif>
</cfquery>

<cfoutput query=&quot;member_table&quot; startRow=&quot;#StartRow_member_table#&quot; maxRows=&quot;#MaxRows_member_table#&quot;>
<tr>
<td>#member_table.MemberID#</td>
<td>#member_table.MemberName#</td>
<td>#member_table.ShareUnits#</td>
<td>#member_table.ShareValue#</td>
<td>#member_table.TotalHolding#</td>
<td>#member_table.Password#</td>
<td>#member_table.upowers#</td>
<td>#member_table.Roles#</td>
</tr>
</cfoutput>

-------------------------------------------------------------
your help is already appreciated so far, and thanks again

Max

 

[ecobb]

In your application.cfcm page, you have to run the cfloginuser before you can set MemberID and MemberName. The function that is used in the SET statements (getAuthUser()) is looking for values set in the cfloginuser tag, so currently it's looking for values before they're created. Try:

<cfloginuser name=&quot;#Form.username#&quot; password=&quot;#Form.password#&quot; roles=&quot;#qValidLogin.upowers#&quot; >
<cfset MemberID = ListFirst(getAuthUser())>
<cfset MemberName = ListRest(getAuthUser())>

Actually, the 2 above cfset statements will probably need to be after your <cflogin></cflogin> tags. This way, they will run on every page (since they're in application.cfm) and set these variables for every page, so you only need to have them in this one spot. The code inside your <cflogin></cflogin> tags will only run if a user is not logged in.

Is MemberID an Int or Numeric Column in the database? If not, you will probably need to pass it with single quotes around it, like so:

WHERE memberID = '#memberID#'
Insead of
WHERE memberID = #memberID#

BUT, if it IS a Numeric or Int column, then the syntax you currently have should be working, so I'm not sure what that could be.

Move your cfset statements outside of your <cflogin></cflogin> tags and check your database column types and let us know if you're still having problems.

Hope This Helps!

Ecobb
- I hate computers!

 

[maximos]

i moved the
<cfset MemberID = ListFirst(getAuthUser())>
<cfset MemberName = ListRest(getAuthUser())>
outside the cflogin tags, ( i tried before and after the tags )

but still getting that syntax error on the result page

its saying that &quot; syntax error in FROM clause,,

i even tryed the query without the <cfif></cfif> :
<cfquery name=&quot;member_table&quot; datasource=&quot;stock&quot;>
SELECT *
FROM tblmember
WHERE memberID = #MemberID#
</cfquery>

and i get an error (Too few parameters. Expected 1)

i'm really stuck , any more ideas ?

 

[ecobb]

Can you copy/paste the error message you are getting when you run the query with the cfif tags? If you post it in this forum thread, maybe I can get a better idea of what the problem is.

Hope This Helps!

Ecobb
- I hate computers!

 

[maximos]

I'm taking a different root, i this application, i understand (almost) every code in it, and i'm trying to attach my database, its a 2 page , a forum page ( i called it login.cfm ) and the result page which i called it index.cfm , here is the code for the index.cfm

<cfset goodLogin = false>

<cfif isDefined(&quot;form.username&quot and len(trim(form.username)) and
isDefined(&quot;form.password&quot and len(trim(form.password))>




<cfif not compare(trim(form.username),&quot;user&quot and not compare(trim(form.password),&quot;user&quot>
<cfloginuser name=&quot;#form.username#&quot; password=&quot;#trim(form.password)#&quot; roles=&quot;add&quot;>
<cfset goodLogin=true>
<cfelseif not compare(trim(form.username),&quot;admin&quot and not compare(trim(form.password),&quot;admin&quot>
<cfloginuser name=&quot;#form.username#&quot; password=&quot;#form.password#&quot; roles=&quot;edit,add,delete&quot;>
<cfset goodLogin=true>
</cfif>
</cfif>
<cfif not goodLogin>
<!--- Display login form --->
<cfinclude template=&quot;login.cfm&quot;>
<cfabort>
</cfif>
</cflogin>

<!--- my outputs if the login succesfully --->
<cfoutput>
<p>
<b>Welcome, #GetAuthUser()#!</b>
</p> </cfoutput>
<cfif isUserInRole(&quot;add&quot>
<a href=&quot;security/index.cfm&quot;>Add Content</a>
</cfif>
<cfif isUserInRole(&quot;edit&quot>
~ <a href=&quot;security/index.cfm&quot;>Edit Content</a>
</cfif>
<cfif isUserInRole(&quot;delete&quot>
~ <a href=&quot;security/index.cfm&quot;>Delete Content</a>
</cfif>

----------------------------------------------------------

now here is what i've done,
-----------------------------------------------------------
<cfset goodLogin = false>

<cfif isDefined(&quot;form.username&quot and len(trim(form.username)) and
isDefined(&quot;form.password&quot and len(trim(form.password))>

<cfquery name=&quot;qValidLogin&quot; datasource=&quot;stock&quot;>
SELECT *
FROM tblMember </cfquery>
<cfif not compare(trim(form.username),&quot;#qValidLogin.MemberName#&quot and not compare(trim(form.password),&quot;#qValidLogin.Password#&quot>


<cfloginuser name=&quot;#form.username#&quot; password=&quot;#trim(form.password)#&quot; roles=&quot;#qValidlogin.role#&quot;>
<cfset goodLogin=true>
<!---
i deleted this extra cfloginuser
<cfelseif not compare(trim(form.username),&quot;admin&quot and not compare(trim(form.password),&quot;admin&quot>
<cfloginuser name=&quot;#form.username#&quot; password=&quot;#form.password#&quot; roles=&quot;edit,add,delete&quot;>
<cfset goodLogin=true>
--->
</cfif>
</cfif>
<cfif not goodLogin>
<!--- Display login form --->
<cfinclude template=&quot;login.cfm&quot;>
<cfabort>
</cfif>
</cflogin>

<!--- my outputs if the login succesfully --->
<cfoutput>
<p>
<b>Welcome, #GetAuthUser()#!</b>
</p> </cfoutput>
<cfif isUserInRole(&quot;add&quot> <!--- the role is written in the role feild in my database --->
<a href=&quot;security/index.cfm&quot;>Add Content</a>
</cfif>
<cfif isUserInRole(&quot;edit&quot>
~ <a href=&quot;security/index.cfm&quot;>Edit Content</a>
</cfif>
<cfif isUserInRole(&quot;delete&quot>
~ <a href=&quot;security/index.cfm&quot;>Delete Content</a>
</cfif>
---------------------------------------------------------------

the good news i'm not getting any errors, but when i login, it keep me at the login screen, as if i entered the wrong user name and password, i know that the database part is working, cause when i mispell the form feild on purpose (eg, &quot;#qValidLogin.MemberNameeee#&quot; ) i get the an error saying (Element MEMBERNAMEEEE is undefined in QVALIDLOGIN.)

i have a feeling its something simple but i can't figure it out,


Again i really appreciat your time and help so far

Max

 

[ecobb]

Just glancing at it, I think your problem may be here:

<cfif not goodLogin>
<!--- Display login form --->
<cfinclude template=&quot;login.cfm&quot;>
<cfabort>
</cfif>

IF goodLogin is not what? You may not be giving CF enough info to work with here. Try this:

<cfif goodLogin NEQ &quot;True&quot;>
<!--- Display login form --->
<cfinclude template=&quot;login.cfm&quot;>
<cfabort>
</cfif>



Hope This Helps!

Ecobb
- I hate computers!

 

[maximos]

i did these changes still no luck , but it with the original code , its wokring perfectly, all i wanted to do , is replace the static login names, and password to come from the database instead..

Any thing am i missing ?