i have a table like this where i am trying to get ...
# cfml-beginners
i have a table like this where i am trying to get the correct answer value https://prnt.sc/L87XA1sl89Ie written a function like this, but it is not picking up the value anything wrong i am doing here
Copy code
<cffunction name="getCorrectAnswer" returntype="any" output="true" hint='Gets the correct answer'>
		<cfargument name="questionidfk" type="UUID" required="true">
		<cfset var rsQuery=querynew('')>
		<cfset var answer = ''>
			<cfquery name="rsIdfromQuestion" datasource="#variables.dsn#">
				select id  
				from questions   
				where questionnaireidfk = <cfqueryparam value="#arguments.questionidfk#" cfsqltype="CF_SQL_VARCHAR" maxlength="35"> 
			<cfloop query="rsIdfromQuestion">
				<cfquery name="rsQuery" datasource="#variables.dsn#">
					select correctAnswer,rank,answer,a_id,id,ROW_NUMBER() OVER(ORDER BY id ASC) AS Row   
					from answers 
					where questionidfk = <cfqueryparam value="#rsIdfromQuestion.id#" cfsqltype="CF_SQL_VARCHAR" maxlength="35"> 
					AND correctAnswer IS NOT NULL
				<cfset answer = request.udf.ParagraphFormat2(rsQuery.answer)>
				<cfreturn answer>
Hey @Simone you are getting way better at asking questions. Nice work here. I think some code is missing though. You have a closing
, but no opening one. When you say "not picking up the value", which value? What debugging have you put in here (like
) to check values are what you expect them to be. Have you dumped out
and each
to see what yer getting? NB: you are `var`ing a coupla variables, but you've forgotten to do
Also, re this:
looks like a loop problem but can't be sure
Of course you can be sure. How many times should it be looping, and how many times is it looping? You can check all this stuff. There doesn't need to be any mystery to it.
yep, let me add some debugging to it and see what i get it as a result
👍 1
Looking at your code do you know you are short circuiting and returning the first answer that matches and not all answers. Another question would be what would expect to happen when there is no answer Personally i would have one query that uses a JOIN to find the correct answer then no need for a loop
Copy code
select q.id as QuestionID, correctAnswer,rank,answer,a_id,a.id,ROW_NUMBER() OVER(ORDER BY a.id ASC) AS Row     
				from questions q
inner join answers a on q.id = a.questionidfk and a.correctAnswer is not null
				where questionnaireidfk = <cfqueryparam value="#arguments.questionidfk#" cfsqltype="CF_SQL_VARCHAR" maxlength="35">
Let your DBMS do what it is good at joining and handling Set Data.