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
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
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
Thank you so much!
~ Deborah
Re: [Deborah] merging two fields into one + creating list field
By Deborah - October 27, 2011
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
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
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
Re: [Deborah] merging two fields into one + creating list field
By Jason - October 28, 2011
No problem! Glad to hear everything is working now.
Let us know if you have any other questions.
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/