[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

MySQL query question

I'm using this in a query:

select
concat(COALESCE(p.FirstName, ' '),' ',COALESCE(p.MiddleName, ' '),' ',COALESCE(p.LastName, ' '), ' ',COALESCE(m.Suffix, ' ')) as name

I need to combine these elements into the variable "name" for use in a report. Not all records have a suffix. I'd like to be able to include a comma after the last name for only those records that do have a suffix. Is there a way to do this?
0
brucecrabtree
Asked:
brucecrabtree
  • 5
  • 4
1 Solution
 
aseusaincCommented:
So you can really only wind up with 2 different values for name:

John Bob Doe Jr. (which you want as "John Bob Doe, Jr.")

or

John Bob Doe

So lets treat name as a space delimited list...This is real quick and dirty but should work provided you ALWAYS have a 1st, middle, and last name.  Which I suppose if you didn't, you could end up with a double space between first and last name.

<cfif listlen(name," ") EQ 4>
  <cfset name = "#listgetat(name,1," ")# #listgetat(name,2," ")# #listgetat(name,3," ")#, #listgetat(name,4," ")#">
</cfif>
0
 
aseusaincCommented:
Actually if you dont have a suffix, you'll have an extra space at the end of the name, so lets trim that as well.

<cfif listlen(name," ") EQ 4>
  <cfset name = "#listgetat(name,1," ")# #listgetat(name,2," ")# #listgetat(name,3," ")#, #listgetat(name,4," ")#">
<cfelse>
  <cfset name = trim(name)>
</cfif>
0
 
brucecrabtreeAuthor Commented:
Most records don't have a middle name. Thanks for the suggestion but I don't think it would work for my situation (I'm using Report Builder). I'd have to loop over the query to combine these elements and then somehow append it to the rest of the query results for each record, instead of concatenating them within the query itself. (This was just the first line of the query -- there's a lot more to it.) That's probably more complicated than it's worth, if it's even possible, I'm not sure.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
brucecrabtreeAuthor Commented:
Maybe I misunderstood. . . .Is there a way to loop over a query and update it in memory?
0
 
aseusaincCommented:
I'm not sure I understand what you are asking.  Either of these will loop over the result set of a query:

<cfloop query="you_query_name">

or

<cfoutput query="your_query_name">
0
 
brucecrabtreeAuthor Commented:
Right, but I need to pass a query to Report Builder in the cfreport tag

<cfreport template="../cfreports/member_directory.cfr" format="pdf" query="getMembers" />

So if my query "getMembers" returns "name" plus 10 other variables (Address, City, State, Zip, etc.), and I loop over it using your code to add a comma to "name" where it's needed, then how do I get the updated "name" variable back into a query result that I can pass in the cfreport tag?
0
 
aseusaincCommented:
Pass it in with <cfreportparam> call it newname so it doesn't conflict w/ any queries

<cfif listlen(name," ") EQ 4>
  <cfset newname = "#listgetat(name,1," ")# #listgetat(name,2," ")# #listgetat(name,3," ")#, #listgetat(name,4," ")#">
<cfelse>
  <cfset newname = trim(name)>
</cfif>

<cfreport template="../cfreports/member_directory.cfr" format="pdf" query="getMembers" />
  <cfreportparam NAME="newname" VALUE="#newname#">
</cfreport>
0
 
brucecrabtreeAuthor Commented:
Thanks. I ran your code and it works, as you said, unless there is no middle name. In those cases it does not add the comma (it doesn't give me a double space in the middle name position). So I guess I need to use a different approach besides treating "name" as a list.
0
 
brucecrabtreeAuthor Commented:
This code gets me the results I need:

<cfloop query="getMembers">
<cfif getMembers.MiddleName NEQ "" and getMembers.Suffix NEQ "">
<cfset name = getMembers.FirstName & " " & getMembers.MiddleName & " " & getMembers.LastName & "," & " " & getMembers.Suffix>
<cfelseif getMembers.MiddleName NEQ "" and getMembers.Suffix EQ "">
<cfset name = getMembers.FirstName & " " & getMembers.MiddleName & " " & getMembers.LastName>
<cfelseif getMembers.MiddleName EQ "" and getMembers.Suffix NEQ "">
<cfset name = getMembers.FirstName & " " & getMembers.LastName & "," & " " & getMembers.Suffix>
<cfelseif getMembers.MiddleName EQ "" and getMembers.Suffix EQ "">
<cfset name = getMembers.FirstName & " " & getMembers.LastName>
</cfif>
</cfloop>

But this code doesn't work to pass the value to the report:

<cfreport template="../cfreports/member_directory.cfr" format="pdf" query="getMembers">
<cfreportparam NAME="name" VALUE="#name#">
</cfreport>

The query returns over 500 records, and so I need to update the variable "name" for each record in the query before passing it to the report. It doesn't appear that cfreportparam does that.
0
 
DarthModCommented:
PAQed with points refunded (500)

DarthMod
Community Support Moderator
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now