Database query problem, using cfloop output problem

Get Started
1

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.



<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

your table design is going to cause you oodles and oodles of trouble

but for your current issue, here’s your problem –

WHERE '#q#' <> 'yes'

you’re comparing a string to ‘yes’, instead of a column to ‘yes’

3

hmm… you got something there…

db design is causing trouble indeed, thinking of a work around with cookies.

thnx for your advise !

4

a workaround for bad database design with cookies?

i don’t think so :slight_smile:

5

just tested it with cookies, that works.

I know not everybody accepts cookies, so i will rewrite my code for this.

Think i’m seeing the light… I think need to check

WHERE 1 <> ‘yes’
OR 2 <> ‘yes’

but then i still don’t see how i can make the cross reference between those 2 tables…

6

now you’re comparing numbers to ‘yes’, instead of columns to ‘yes’

that WHERE clause will return TRUE on every single row

once again i’m suggesting a table redesign

:slight_smile:

7

in this case i thought the 1 and 2 would be the same as ID / UserName / etc.

so i thought i was comparing columns, i.e (column)1, (column)2 to yes or a number.

thinkin about how i can redesign the table for this issue…

8

for starters, don’t assign a column name consisting only of digits

to conform to first normal form, you should not have multiple repeating columns of the same type/purpose on the same row

9

in that case i should make a new table with the answers. that way i can get 1 answer result and do the cross reference… then do the loop for the next answer ?

Am i thinkin in the right direction ??

10

best thing you can do is put aside, for the moment, any thoughts of processing, and concentrate only on the data

for starters, if the database is designed properly, you won’t have to “loop for the next answer”

what kinds of questions are they? fill-in-the-blanks? multiple choice?

11

its a row of multiple choice questions. the number of questions can vary between 1 and 60.

I will put it aside for now as u advise :smiley: will have to think this over again with a fresh mind…