Drop Down Values
I am referencing and old thread:
I have it working.. however its showing the "num" instead of the value. any way around this?
heres my code:
SELECT num, CONCAT(item_num, " - ", style, " - ", frame_color, " - ", lens_color)
FROM `<?php echo $TABLE_PREFIX ?>sunglasses_products`
its showing like this: 104310503 - 30 - 17 - 4
number in green is correct.. the red are showing "num"
Ok, this gets into a bit of more advanced MySQL.
Install the Developer Console plugin: https://www.interactivetools.com/add-ons/developer-console/
Activate it under the plugins menu and click on the MySQL Console
Create a query that loads all the fields from your main table and confirm it works. Should be something like this:
SELECT num, item_num, style, frame_color, lens_color FROM <?php echo $TABLE_PREFIX ?>sunglasses_products
Next, create a query for the second table, and make sure you can load the fields you need from that table. Something like:
SELECT num, color FROM <?php echo $TABLE_PREFIX ?>sunglasses_lenscolors
We need to combine both queries and refer to tables and fields from each, so we'll add a table alias to the first query after the tablename. We'll use 'p' to refer to "products" and then add it before all the field names:
SELECT p.num, p.item_num, p.style, p.frame_color, p.lens_color FROM <?php echo $TABLE_PREFIX ?>sunglasses_products p
Then we'll do the same with "c" for colors:
SELECT c.num, c.color FROM <?php echo $TABLE_PREFIX ?>sunglasses_lenscolors c
Next, we'll do a MySQL Left Join. You can Google that for more details but basically it joins both tables on the conditions specified and shows ALL the records from the LEFT (first) table and only matching records from the right. So if there's no match you'll still get the columns from the right table but they'll be blank. Looks something like this:
SELECT p.num, p.item_num, p.style, p.frame_color, p.lens_color, c.color
FROM <?php echo $TABLE_PREFIX ?>sunglasses_products p
LEFT JOIN <?php echo $TABLE_PREFIX ?>sunglasses_lenscolors c ON p.lens_color = c.num
You can then add more LEFT JOINS if you need. Once you have it displaying all the columns needed you change the first name to be p.num, CONCAT(....).
Hope that helps!