mysql query advanced and :label(s)
2 posts by 2 authors in: Forums > CMS Builder
Last Post: July 3, 2014 (RSS)
By mizrahi - July 1, 2014
I am trying to use an advanced mysql query with a list, but the field I am referencing is also a list and the query is returning the values, rather than the labels. See query below. Is there any to show the labels instead?
Current:
<?php $numValue = (@$ESCAPED_FILTER_VALUE )? $ESCAPED_FILTER_VALUE : '0'; ?>
SELECT num, CONCAT(clientname,' / ',title)
FROM `<?php echo $TABLE_PREFIX ?>portfolio_projects`
WHERE num != <?php echo $numValue; ?>
Idea that doesn't work:
<?php $numValue = (@$ESCAPED_FILTER_VALUE )? $ESCAPED_FILTER_VALUE : '0'; ?>
SELECT num, CONCAT(clientname:label,' / ',title)
FROM `<?php echo $TABLE_PREFIX ?>portfolio_projects`
WHERE num != <?php echo $numValue; ?>
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!
interactivetools.com