EasyCFM.COM ColdFusion Forums / Coding Help! / Help Displaying query results

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Help Displaying query results -- page: 1 2

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

coqui77
05-17-2006 @ 10:51 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 47
Joined: Jul 2005

I need help..

I have 2 tables.  A questions table and a master table.  Question table fields are questionid, question
master table fields are masterid,surveyid,status,score,questionid

This is my query
<cfquery name="getResults" datasource="ees">
select count(masterOID) as num, question,score
FROM lu_questions  
INNER JOIN tbl_master
ON lu_questions.questionoid=tbl_master.questionoid
where status = 'y'
group by lu_questions.question,tbl_master.score
order by question
</cfquery>


I want the data to display as such.  I want the number of records for each question that has a particular score to be displayed under its assigned score. Score range is 1-4 look at example below.

                  
Question              1's  2's  3's  4's

Rate this site?       20    15   1    0
Rate coldfusion      19    2    1    1



This didnt work... it displays the question and scores but not in the format above..thats where I need help

<table width="80%" align="center">
<!--- <td><strong>Question ID</strong></td> ---><td><strong>Question</strong></td><td><td>1's</td><td>2's</td><td>3's</td><td>4's</td>

<cfparam name="thisQuestion" default="">
<cfoutput query="getresults"  >
<cfif thisQuestion neq "#question#">

<tr><td>#question#<td></tr></cfif><tr>
<cfset thisQuestion = "#question#">
<cfloop from="1" to="4" index="i">
<cfif i eq "#score#">
<td> </td><td>#num#</td>
<cfelse>
<td> </td><td>0</td>
</cfif>
</cfloop></tr>
</cfoutput>

</table>


it does this...


Question                      1's 2's 3's 4's
Do you like this site?
                                 0   1   0   0
                                 0   0   1   0
                                 0   0   0   1

This message was edited by coqui77 on 5-18-06 @ 10:08 AM

mquack
05-17-2006 @ 11:56 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1544
Joined: Jan 2005

This may seem like a dumb question, but have you done a <cfdump> to verify that your query is returning the data that you are expecting it to?  (No sense in working with the output if we're not even getting the correct data.)

http://www.rachelqueensg.com

This message was edited by mquack on 5-17-06 @ 11:56 PM

coqui77
05-18-2006 @ 12:00 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 47
Joined: Jul 2005

yep a cfdump was used to verify the correct data was being returned and it is. I still need help with displaying it.

(nice website by the way)

This message was edited by coqui77 on 5-18-06 @ 12:03 AM

mquack
05-18-2006 @ 12:04 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1544
Joined: Jan 2005

Do you by chance have a link to the cfdump so that I can better visualize what we're working with?

http://www.rachelqueensg.com

coqui77
05-18-2006 @ 12:13 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 47
Joined: Jul 2005

dump
Num in the number of people that provided a particular score to the question


This message was edited by coqui77 on 5-18-06 @ 12:14 AM

mquack
05-18-2006 @ 12:48 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1544
Joined: Jan 2005

Okay, I'm still struggling a bit with getting my brain to unscramble itself with this, so this is kinda a shot-in-the-dark here...

<table width="80%" align="center">
      <tr>
            <td>
            <strong>Question
ID</strong></td><td><strong>Question</strong>
            </td>
      </tr>
<cfoutput query="getResults" group="question" >
      <tr>
            <td>#questionoid#</td>
            <td>#question#</td>
      <cfoutput query="getResults" group="score">
            <td>num of people#num#</td>
            <td>score#score#</td>
      </cfoutput>
      </tr>
</cfoutput>
</table>



http://www.rachelqueensg.com

coqui77
05-18-2006 @ 8:59 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 47
Joined: Jul 2005

That didn't work ...error is

Invalid tag nesting configuration

A query driven CFOUTPUT tag is nested inside a CFOUTPUT tag that also has a QUERY= attribute. This is not allowed. Nesting these tags implies that you want to use grouped processing. However, only the top-level tag can specify the query that drives the processing.


remember this is the way i want it to look...

Question              1's  2's  3's  4's

Rate this site?       20    15   1    0
Rate coldfusion      19    2    1    1

This message was edited by coqui77 on 5-18-06 @ 8:59 AM

kevsarg18
05-18-2006 @ 11:40 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 428
Joined: Jun 2004

On the second nested cfoutput, tale off the query attribute. It knows it from the first cfoutput, and will thing it is not right if you use it again.

My CFML Scripts.

coqui77
05-18-2006 @ 11:49 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 47
Joined: Jul 2005

that didnt generate an error but the result is not what was desired..

aadams
05-18-2006 @ 4:17 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 280
Joined: Nov 2005

It would take some nasty looping to achieve this relying on CF to do the work.  Here's an SQL solution for you assuming you know in advance the score range (1-4 in your example).  I can't tell you for sure this will work without seeing your actual table structure but maybe with some tweaking it'll do the trick.


<cfquery name="getResults" datasource="ees">
select question,
     (select count(masterOID) from lu_questions q where score = 1 and q.questionoid = lu_questions.questionoid) as score_one,
     (select count(masterOID) from lu_questions q where score = 2 and q.questionoid = lu_questions.questionoid) as score_two,
     (select count(masterOID) from lu_questions q where score = 3 and q.questionoid = lu_questions.questionoid) as score_three,
     (select count(masterOID) from lu_questions. q where score = 4 and q.questionoid = lu_questions.questionoid) as score_four
FROM lu_questions  
INNER JOIN tbl_master
ON lu_questions.questionoid=tbl_master.questionoid
where status = 'y'
group by lu_questions.question,tbl_master.score
order by question
</cfquery>
<table>
     <tr>
          <th>Question</th>
          <th>1's</th>
          <th>2's</th>
          <th>3's</th>
          <th>4's</th>                              
     </tr>
<cfoutput query="getResults">
     <tr>
          <td>#question#</td>
          <td>#val(score_one)#</td>
          <td>#val(score_two)#</td>
          <td>#val(score_three)#</td>
          <td>#val(score_four)#</td>                              
     </tr>
</cfoutput>
</table>


--Abram
CFXChange.com - ColdFusion Custom Tags and Components

PAGE: 1 2

Website Designed and Developed by Pablo Varando.