Skip to main content
A
Anonymous
March 16, 2011
Answered

Checking if values exist in the database

  • March 16, 2011
  • 2 replies
  • 685 views

Hi,

I have problem with the values that are not in the database.

I have a form and there are two types of users:normal and the Admins.

If the user is normal user, he cannot access anyone else entries from the database. But the Admin can access all the entries.

I have done a check to so that the normal user can access only his entries:

<cfif NOT session.Admin AND process.addedBy NEQ #session.AccountID# >
            <cflocation url="main.cfm" addtoken="no">
        <cfelse>

<!--- rest of the form--->

By these, a normal useer cannot access any entries other than his own.

But for admin, a admin can access all the entries but not the entries that are not in the database. The query related to these form is:

<cfquery name="xx" datasource="yy">
            Select entryid, addedBy, deletedBy,processedBy
            From yy
            where entryid = #URL.entryid# AND processedBy IS NULL AND deletedBy IS NULL
                <cfif NOT session.Admin> AND addedBy = #Session.AccountID#
                 </cfif>

    This topic has been closed for replies.
    Correct answer

    most database flavors have CASE functionality. with case statements you can create checks on the data in the database..

    example

    (CASE

       WHEN animal = 'cat' THEN 'fish'

       WHEN animal = 'mouse' THEN 'cheese'

      ELSE 'candy' END ) as animalFood

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    March 18, 2011

    Look at it the other way round. Why would you use values that are not in the database? If processedBy and deletedBy are NULL, why don't you leave them out, and do something like

    <cfquery name="xx" datasource="yy">
    Select entryid, addedBy
    From yy
    where entryid = <cfqueryparam value="#URL.entryid#" CFSQLType="cf_sql_numeric">
    <cfif NOT session.Admin> AND addedBy = #Session.AccountID#
    </cfif>

    A
    Anonymous
    March 21, 2011

    Hi,

    Thanks for the replies the idea of not including the entries that are not in the database out worked.

    Thanks for the valuable suggestions and for the valuable feedback. I really appreciate it.

    Vijayvijay77.

    A
    Anonymous
    Correct answer
    March 18, 2011

    most database flavors have CASE functionality. with case statements you can create checks on the data in the database..

    example

    (CASE

       WHEN animal = 'cat' THEN 'fish'

       WHEN animal = 'mouse' THEN 'cheese'

      ELSE 'candy' END ) as animalFood

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices