mysql query advanced and :label(s)

2 posts by 2 authors in: Forums > CMS Builder
Last Post: July 3, 2014   (RSS)

By Dave - July 3, 2014

Hi mizrahi, 

No, there's no way to show the values from a single query, CMSB looks them up after the fact for the viewers.

The way to do it in MySQL is with a join.  So say here's your basic query:

SELECT num, createdByUserNum, title FROM cms_news

And you want not just createdByUserNum but the 'fullname' field from the accounts table.  You need to do a query on two joined tables and specify which table each field comes from:

   SELECT cms_news.num, cms_news.createdByUserNum, cms_news.title, cms_accounts.fullname
     FROM cms_news
LEFT JOIN cms_accounts ON (cms_news.createdByUserNum = cms_accounts.num)

Or you can add table aliases to make the code read cleaner:

   SELECT n.num, n.createdByUserNum, n.title, a.fullname
     FROM cms_news n
LEFT JOIN cms_accounts a ON (n.createdByUserNum = a.num)

Here's some docs on that: http://www.w3schools.com/sql/sql_join_left.asp

So it can be a bit tricky MySQL, but that's how it's done.  Hope that helps!

Dave Edis - Senior Developer
interactivetools.com