merging two fields into one + creating list field

Hi Deborah,

You can use the mysql function CONCAT() to combine the first and last names (with a space in between) and use that as the list option label like this:

SELECT num, CONCAT(first_name," ",last_name)
FROM `<?php echo $TABLE_PREFIX ?>artists`

NOTE, this example uses "num" as the value for the list options.

Hope this helps

Perfection! Does exactly what I needed to do.

Thank you so much!
~ Deborah

Hi. I'm recycling this mysql code for another project and am having an issue within CMSB admin. I can see the resulting concatenated list, but I cannot make selections (browsers crash).

The table is "slideshow_photos". For this table I wish to combine category, brand, and title from the table "furniture_items".

For 'slideshow_photos' I entered this for 'Get options from MySQL query':

SELECT num, CONCAT(category," ",brand," ",title)
FROM `<?php echo $TABLE_PREFIX ?>furniture_items`

This is correctly showing me a drop-down select list in CMSB for the "slideshow_photos" list editor, with category, brand, and title combined.

Problem is when attempting to add a record to the "slideshow_photos" editor, I click the arrow to select the concatenated list option, then can click on an item in the concantenated list. Upon releasing my mouse the select list reverts back to the 'select' mode and the browser crashes (IE and FFox).

The fields 'category' and 'brand' are using 'Get options from other database, if that has any bearing on the issue.

Hoping someone here can help out. Thanks in advance!

~ Deborah

Hi Deborah,

Does this happen if you don't use CONCAT? For example if you tried:

SELECT num, title
FROM `<?php echo $TABLE_PREFIX ?>furniture_items`

would you have the same problem?

Do you have a filter set up so that the selection from this list populates another list?

If you like, you can fill out a [url http://www.interactivetools.com/support]2nd Level Support Request[/url] and I can take a quick look at it for you.

Hope this helps

Jason,

I think figured out what the problem was.

It is necessary to use a left single quote for both escape characters, instead of the single quote marks I typed from my keyboard.

Using the single quote marks throws a mySQL syntax error. If I modify the example from the CMSB drop-down all works fine. It's just if I type it myself with standard quotes that I see the problem.

My not having enough understanding of mySQL, it appears?

Thanks for your help. I doubt I would have figured that out without your suggestion.

~ Deborah