Display two fields in List select

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

By rjbathgate - March 16, 2009

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 ross - March 17, 2009

Hi Rob

That does sound like it should be something you can do wit the advanced lookup option for list fields. I'll ask Dave to take a look at this one for you when he gets in. He'll let you know what options are available.

Thanks!
-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

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