use label in SQL query
5 posts by 3 authors in: Forums > CMS Builder
Last Post: July 8, 2013 (RSS)
By Toledoh - July 7, 2013
Hi Guys,
I'm currently using the following to show a drop-list;
SELECT num, CONCAT(title, ' $', price,' each') product
FROM `<?php echo $TABLE_PREFIX ?>products`
and I also want to show the "location" field... ie.
SELECT num, CONCAT(title, location, ' $', price,' each') product
FROM `<?php echo $TABLE_PREFIX ?>products`
however, the location field is a list which uses the num as the value, to I need to show it's label, but I can't use
SELECT num, CONCAT(title, location:label, ' $', price,' each') product
FROM `<?php echo $TABLE_PREFIX ?>products`
Any thoughts?
Tim (toledoh.com.au)
By jenolan - July 7, 2013
If the location is like 1, 2, 3 etc and you want to translate the number into a 'value' hen you need to do a join on the table that has the names with the location as the where something like (excuse typos if I make any);
SELECT num, CONCAT(title, L.location_name, ' $', price,' each') product
FROM `<?php echo $TABLE_PREFIX ?>products` AS P
LEFT JOIN <?php echo $TABLE_PREFIX ?>locations AS L ON P.location=L.location_id;
Peace and Long Life
By gregThomas - July 8, 2013
Hi Tim,
A slight modification on Larry's suggestion would be to add the select statement that retrieves the location directly to the CONCAT statement. You could do something similar to what I suggested in this post:
http://www.interactivetools.com/forum/forum-posts.php?postNum=2231099#post2231099
Let me know if you have any questions.
Cheers
Greg
PHP Programmer - interactivetools.com
By Toledoh - July 8, 2013
Thanks Guys - I'll try to get my head around that!
Tim (toledoh.com.au)
By jenolan - July 8, 2013
ug subselects .. ;-) gotta be careful with them if I remember my mySQL performance hints.
Peace and Long Life