Drop Down Values

4 posts by 2 authors in: Forums > CMS Builder
Last Post: November 22, 2017   (RSS)

By Dave - November 21, 2017

Hi s2smedia, 

Where are the values for frame_color specified?  Are they from another table? 

Dave Edis - Senior Developer
interactivetools.com

By s2smedia - November 21, 2017

Yes they are. 

By Dave - November 22, 2017

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!

Dave Edis - Senior Developer
interactivetools.com