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

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, 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.

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