#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    11
    Rep Power
    0

    Db query problem, using cfloop


    Hi !

    A few fields in 1 table in my db either contain a number or "yes".

    With these numbers i need to cross reference another table and output the values of the second table.

    So i have:

    Tables:
    [questions]
    [answers]

    In the answers table i put in seperate fields which question is correct (value is "yes") and if a question is incorrect, i put the number of this question in a corresponding field.

    My answers table contains the fields [1] [2] [3] [4] [5] etc.

    if question 1 is correct, i put "yes" in field [1]. If question 1 is incorrect, i put "1" in field [1]. The same for question 2,3,4,5 etc.

    What i want to do is now check field [1,2,3,4,5,etc] for its value. If the value is "yes" i dont want to do anything, but if the value is a number, i want to check table [questions]. The number corresponds with the question id.

    I then want to output the found questions.

    I.E.

    question 1 is yes
    question 2 is 2
    question 3 is 3
    question 4 is yes

    My output should then be

    question 2
    question 3

    I think i need to loop over the query.

    [code]

    <cfset q = '1'>
    <cfloop from="1" to="#amount#" index="GetAnswers">
    <cfquery name="GetWrongAnswers" datasource="#DS#">
    SELECT * FROM answers
    WHERE '#q#' <> 'yes'
    </cfquery>
    <cfset question = #q#>
    <cfoutput>#question#</cfoutput>
    <cfset q = #q# +1>
    </cfloop>

    This output just gives me

    1 2 3 4

    my table is filled with 1 = yes, 2 = 2, 3 = 3, 4 = yes, so the output should now just be: 2 3.

    Is what i want possible ?
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,278
    Rep Power
    968
    It's almost certainly possible. I would turn on debugging and look at the SQL that is being run, because the problem is what your actual queries look like after the variables have been evaluated.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    1
    Rep Power
    0
    Although this process makes zero sense to me I think you simply forgot to add your if statement... (also make sure to not pass your initial value of 'q' as a string)

    <cfset q = 1>
    <cfloop from="1" to="#amount#" index="GetAnswers">
    <cfquery name="GetWrongAnswers" datasource="#DS#">
    SELECT * FROM answers
    WHERE '#q#' <> 'yes'
    </cfquery>
    <cfif GetWrongAnswers.RecordCount GT 0>
    <cfoutput>#q#<br></cfoutput>
    </cfif>
    <cfset q = q +1>
    </cfloop>

IMN logo majestic logo threadwatch logo seochat tools logo