Select Box - Advanced mySQL - labels
2 posts by 2 authors in: Forums > CMS Builder
Last Post: November 17, 2010 (RSS)
Hey,
Setting up a list field, with advanced mysql statement to concatenate the label of the list field:
So far:
This works, BUT...
field1, field2 and field3 are themselves linked list fields, and the actual values are numbers.
So the above will return:
1 3 4
for example
Where I want it to return the label for each field i.e.
label label label
I foresee this needing to look up each label for each field and then do concat those results...
Is this possible, and if so, where should I start with the mysql code?
Cheers
Setting up a list field, with advanced mysql statement to concatenate the label of the list field:
So far:
SELECT num, CONCAT_WS(' ',field1,field2,field3) FROM cms_tableThis works, BUT...
field1, field2 and field3 are themselves linked list fields, and the actual values are numbers.
So the above will return:
1 3 4
for example
Where I want it to return the label for each field i.e.
label label label
I foresee this needing to look up each label for each field and then do concat those results...
Is this possible, and if so, where should I start with the mysql code?
Cheers
Re: [rjbathgate] Select Box - Advanced mySQL - labels
By Dave - November 17, 2010
Hi Rob,
For most users I'd say that's just not possible, but CMSB does support custom MySQL if you know it, and I know you're fairly technical so here's some pointers.
What you want to do (or learn about) is MySQL Joins:
http://www.google.com/search?q=mysql+join+tutorial
I'd practice in MySQL Query Browser or MySQL Console (our plugin) and get your query working first. It will be way faster that reloading the edit page over and over to see if it works.
Here's an example with a CMS setup with a 'news' section, 'accounts' section and a table prefix of "cms_":
Since you're dealing with multiple tables you need to put them before the fieldnames so MySQL knows which table the field is coming from. You can use table aliases to make the code shorter (and hopefully easier to read), just put an alias word after the tablename like this:
And here's a version using concat:
Hope that helps! Let me know any questions.
For most users I'd say that's just not possible, but CMSB does support custom MySQL if you know it, and I know you're fairly technical so here's some pointers.
What you want to do (or learn about) is MySQL Joins:
http://www.google.com/search?q=mysql+join+tutorial
I'd practice in MySQL Query Browser or MySQL Console (our plugin) and get your query working first. It will be way faster that reloading the edit page over and over to see if it works.
Here's an example with a CMS setup with a 'news' section, 'accounts' section and a table prefix of "cms_":
SELECT cms_news.num, cms_accounts.fullname
FROM cms_news
LEFT JOIN cms_accounts ON cms_news.createdByUserNum = cms_accounts.numSince you're dealing with multiple tables you need to put them before the fieldnames so MySQL knows which table the field is coming from. You can use table aliases to make the code shorter (and hopefully easier to read), just put an alias word after the tablename like this:
SELECT n.num, a.fullname
FROM cms_news n
LEFT JOIN cms_accounts a ON n.createdByUserNum = a.numAnd here's a version using concat:
SELECT n.num, CONCAT_WS(' - ', a.fullname, a.username)
FROM cms_news n
LEFT JOIN cms_accounts a ON n.createdByUserNum = a.numHope that helps! Let me know any questions.
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com