CONCAT

3 posts by 2 authors in: Forums > CMS Builder
Last Post: March 19, 2014   (RSS)

By Toledoh - March 19, 2014

Hi Guys.

I've got an editor with a list field using "get options from mySQL" and I have played around with things to get the following:

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

I have this working, and it makes a pick list with the options being a collection of "Last Name First Name :Num" which is exactly what I want, however I don't understand;

1. What does the "fullname" do?

2. How do I get the list to be in a certain order?  I have the individual table sorted by last_name, but the list is showing ordered by num.

Cheers,

Tim (toledoh.com.au)

By gregThomas - March 19, 2014

Hi Tim,

'fullname' is what the data returned by CONCAT will be called by MySQL, it's treated the same as a column name.

You can change the order of the list by using the MySQL ORDER BY command. For example you could order your results by the fullname field by doing the following:

SELECT num, CONCAT(last_name, ' ', first_name, ' :', num) fullname
FROM `<?php echo $TABLE_PREFIX ?>individuals`
ORDER BY fullname DESC

You can use any field name from your table in the ORDER BY command. You can read more about how to use order by here:

http://www.w3schools.com/php/php_mysql_order_by.asp

Cheers!

Greg

Greg Thomas







PHP Programmer - interactivetools.com