Admin Get Options MQSQL Query - Use multiple fields label dropdown

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!

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 != ''

Hi theclicklab, 

Try this: 

WHERE name IS NOT NULL AND name != ''

Hope that helps!

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?

Ah, gotcha, that works, thanks so much, awesome!