Link to home
Create AccountLog in
Avatar of Dusty
DustyFlag for United States of America

asked on

cfselect display attribute question

I have a cfselect where I need to concat two variables "AgentFName" "AgentLName" in the display attribute. How can this be done?

<cfquery name="agent" datasource="test">
SELECT fagents.Agent,fagents.AgentLname,fagents.AgentFName from fagents
</cfquery>

<cfselect Name = "Agent" query = "agent" value = "Agent" display="AgentFName" queryPosition="below"  class="selectpicker show-menu-arrow span4">
      <option value=" ">Please Select</option>
      
      </cfselect>

Thanks!
Avatar of gdemaria
gdemaria
Flag of United States of America image

Concatenate the columns in the query to create one column and use that in the select

<cfquery name="agent" datasource="test">
 SELECT f.Agent
              ,f.AgentLname + ' ' + f.AgentFName  as AgentFName
       from fagents
 </cfquery>
Avatar of Dusty

ASKER

Hi gdemaria,

When I run that I get no error, but each record is displayed as 0.0 ?? what could cause that? See attached pic."

<cfquery name="agent" datasource="test">
SELECT fagents.Agent,fagents.AgentLname + ' ' + fagents.AgentFName as Name from fagents
</cfquery>

 <cfselect Name = "Agents" query = "agent" value = "Agent" display="Name" queryPosition="below"  class="selectpicker show-menu-arrow span4">
    <option value=" ">Please Select</option>
    </cfselect>

User generated image
ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Dusty

ASKER

I'm using MySQL I will try what you suggested. Thanks!
Check out the Concat_WS function for MySQL:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws
CONCAT_WS(',', 'fagents.AgentLname', 'fagents.AgentFName') as Name

Open in new window

There is also the Concat function:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat
Avatar of Dusty

ASKER

SELECT fagents.Agent, CONCAT(fagents.AgentLname,' , ',fagents.AgentFName) as Name from fagents

Worked! Thanks again
>  each record is displayed as 0.0 ?? what could cause that?

FWIW, it's because MySQL only uses "+" for addition. If you use it with strings, MySQL attempts to convert the strings to numbers first. Since a name like "John" or "Smith" isn't a number, MySQL converts it to zero.  

      SELECT 'John' + 'Smith' AS Name   <== translates to  SELECT '0' + '0' AS Name


The behavior's similar to CF's Val() function. Val() converts any non-numeric values to 0.