merging two fields into one + creating list field

7 posts by 2 authors in: Forums > CMS Builder
Last Post: October 28, 2011   (RSS)

By Deborah - April 25, 2011

What I want to do is difficult to explain, but here goes...

I have an existing CMSB client installation into which content has already entered into a table titled 'Artists'. The artist names use two fields (first_name and last_name).

We will be creating a new table for 'Products'. In the 'Products' table editor I would like a dropdown select list of artist names from the 'Artists' table with the first_name and last_name merged to display for the list as 'fullname'.

Can anyone tell me if there is a MySQL advanced query I can assign for the list options in the 'Artists' table that will accomplish this? Otherwise, we will need to manually go through each artist listing in the current database and manually assign a new 'fullname' field and edit each record accordingly.

Thanks for any help, in advance.
~ Deborah

Re: [Deborah] merging two fields into one + creating list field

By Jason - April 26, 2011

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
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] merging two fields into one + creating list field

By Deborah - April 26, 2011

Perfection! Does exactly what I needed to do.

Thank you so much!
~ Deborah

Re: [Deborah] merging two fields into one + creating list field

By Deborah - October 27, 2011

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

Re: [Deborah] merging two fields into one + creating list field

By Jason - October 28, 2011

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 Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] merging two fields into one + creating list field

By Deborah - October 28, 2011

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