Admin Get Options MQSQL Query - Use multiple fields label dropdown

7 posts by 2 authors in: Forums > CMS Builder
Last Post: January 23, 2019   (RSS)

By theclicklab - January 21, 2019 - edited: January 23, 2019

In the admin, I have a list field using "get options from database" where I would like to set the option LABELS to use TWO fields e.g. First Name and Last Name.

For this I assume I need to use the "get options from MYSQL query" ? Just looking for some guidance on how to use this feature. 

See attached admin screen 

Many thanks!

Attachments:

admin-dropdown-list.jpg 147K

Use multiple fields in admin list option label dropdown

By Dave - January 22, 2019

Hi theclicklab, 

So if you select "Get options from mysql query (advanced)" you'll see this default text: 

SELECT fieldname1, fieldname2
  FROM `<?php echo $TABLE_PREFIX ?>tableName`

You can update that to match your tablename and fields:

SELECT num, email
  FROM `<?php echo $TABLE_PREFIX ?>accounts`

First, I'd confirm that works.  Next, you want to join two fields together.  The first thing I do when I don't immediately know the SQL is to try searching for a few ways to ask that question, eg: https://www.google.com/search?q=mysql+join+two+fields+together  From here you see some examples of the CONCAT() mysql function.

The CONCAT() function that joins strings together, so CONCAT(first_name, ' ', last_name) would return what you want.  Try this: 

SELECT num, CONCAT(first_name, ' ', last_name)
  FROM `<?php echo $TABLE_PREFIX ?>accounts`

Let me know if that works for you!

Dave Edis - Senior Developer
interactivetools.com

Use multiple fields in admin list option label dropdown

Hi Dave, perfect thanks for that, have managed to do quite a bit of customisation with those tips. I just have one more issue where I'm getting a SQL syntax error when trying to exclude blank values from a list, I've tried the following two WHERE statements and neither work. Any suggestions?

SELECT num, name
FROM `itineraries`
ORDER BY name ASC
WHERE name IS NOT NULL
WHERE name != ''

Use multiple fields in admin list option label dropdown

By Dave - January 23, 2019

Hi theclicklab, 

Try this: 

WHERE name IS NOT NULL AND name != ''

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com

Use multiple fields in admin list option label dropdown

By theclicklab - January 23, 2019 - edited: January 23, 2019

Im still getting this error

MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE name IS NOT NULL AND name != ''' at line 4

For the following:

SELECT num, name
FROM `<?php echo $TABLE_PREFIX ?>itineraries`
ORDER BY name ASC
WHERE name IS NOT NULL AND name != ''

None of these WHERE options work:

WHERE name IS NOT NULL AND name != ''

WHERE name IS NOT NULL

WHERE name != ''

Could it be related to still using an older version of php?

Use multiple fields in admin list option label dropdown

By Dave - January 23, 2019

Ah, no problem, try swapping the order of "WHERE and ORDER BY" like this: 

WHERE name IS NOT NULL AND name != ''
ORDER BY name ASC

The reasoning is that MySQL wants to sort as few records as possible so it wants you to filter your resultset first.  

Let me know if that works for you.

Dave Edis - Senior Developer
interactivetools.com