Display two fields in List select

4 posts by 3 authors in: Forums > CMS Builder
Last Post: March 17, 2009   (RSS)

Hello,

I have one table, 'cms_contacts' which contains fields:

Forename
Surname

In another table, 'cms_purchases' I want to be able to select the appropriate Contact from the cms_contacts table...

As we have two fields for the Contact Name (forename and surname) I want the drop down within Purchases to display both (forename surname).

The basic 'get options from database' only allows displaying of one field, so can it be done with the 'get options from mySQL'?

I put this in:

SELECT forename,surname FROM cms_contacts

However, it doesn't work - it only displays the SURNAME.

Am I barking up the wrong tree?

Basically I just want the drop down/select on Purchases table to display:

Joe Bloggs
Dave Smith
etc etc

i.e. concatenating the two fields (forename and surname) from the Contacts table.

Many thanks in advanced
Rob

Re: [rjbathgate] Display two fields in List select

By Dave - March 17, 2009

Hi Rob,

The way the "Get Options from MySQL" works is it uses the first field as the "value" (that gets stored in the database) and the second field as the "label" (that gets displayed to the user).

Often you want to store the record num as the value because you know that will never change. In the case of categories if you used the name as a lookup and the name changes then the record won't be associated. That's why we have the two fields.

There's a list of MySQL string functions here: http://dev.mysql.com/doc/refman/4.1/en/string-functions.html
To join two fields with a space we can use CONCAT_WS(). Try this:

SELECT num, CONCAT_WS(' ',forename,surname) FROM cms_contacts

Have the record number also makes it easier later if you need to load the account record. But if your project doesn't need that feel free to just use CONCAT_WS twice for both values.

Hope that helps!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Display two fields in List select

Ah ha cheers, thought Concatenating might be the way forward.

Many thanks, works a treat.