3

I'm trying to display the results of a query, but I'm having a hard time combining two values that the query produces... Here's the query I have

    <cffunction name="getStudentData"  returntype="query">
    <cfargument name="nameVar" type="string" required="yes">
    <cfargument name="timeframe" type="numeric" required="yes">

    <cfquery datasource="#Application.hds#" name="gsd">
    select (s.lastname + ', ' + s.firstname) as StudData,
    ('[' + r.hallname + ' ' + r.roomnumber + ']')  as roomdata,
     s.studentnumber
    from tblstudents s left join 
    (select h.hallname, ra.roomnumber, studentid 
    from tblroomassignments ra, tblhalls h
    where ra.TimeFrame = #Arguments.timeframe# 
    and ra.hallid = h.hallid) r
    on s.studentid = r.studentid
    where s.lastname like '#Arguments.nameVar#%'
    </cfquery>
    <cfreturn #gsd#>
    </cffunction>

What I'm trying to figure out is how to display StudData+' '+roomdata IF they both exist together since some students won't have a room assigned to them. (I'm just trying to produce a list of students that have a dorm/room assigned to them. In my cfselect...

<cfselect name="RecipientName"
          query="studentdata"
          display="StudData+' '+roomdata"???????
          value="studentnumber">
</cfselect>

I don't know how to get StudData and roomdata in the display attribute without the page giving me an query column error. I'm very new to coldfusion and it's my understanding that you can only display one variable? Is there a way to combine StudData and roomdata into a variable and then display that variable?

Anyone have any ideas? Could this be simplified? Hope this all makes sense!

EllensburgMoose
  • 39
  • 2
  • 4
    Can't you just concatenate the values in the query and then reference the new aliased field? – John Whish May 27 '15 at 17:07
  • I guess that's where I'm having troubles. I don't get how to add another line that concatenate's the two values without causing an error. Plus, I'm trying to also figure out how to get some sort of IF statement in here that only pulls a student's name if they have a room assigned. – EllensburgMoose May 27 '15 at 18:48
  • You cannot do it within the cfselect tag. It must be done in SQL. The exact syntax depends on your DBMS (which you did not mention). Please post your DBMS, the query you tried and the error message. – Leigh May 27 '15 at 22:42

1 Answers1

4

I wouldn't use a cfselect at all.

<select name="RecipientName">
  <cfoutput query="studentdata">
    <option value="#studentnumber#">#StudData# #roomdata#</option>
  </cfoutput>
</select>

If you really want to use cfselect, then I'd concatenate the columns in your query.

StudData + roomdata AS expanded_student_data

...

<cfselect name="RecipientName"
  query="studentdata"
  display="expanded_student_data"
  value="studentnumber"
>
</cfselect>
Fish Below the Ice
  • 1,273
  • 13
  • 23
  • Only reason I'm trying to use cfselect is the way this form is being built. A user will select a name from the drop down menu and then other actions on the page will happen. – EllensburgMoose May 27 '15 at 18:51
  • That can be done with jQuery too. It does not require cfselect. Do a search on something like coldfusion jquery chained select and you will find a bunch of examples. – Leigh May 27 '15 at 22:40