Thread

SELECT @field_id:=CONCAT('d.field_id_',field_id) FROM exp_category_fields WHERE group_id = '2' AND field_name = 'title_nl';

SET @query = CONCAT('SELECT t.cat_id AS child_category_id, t.cat_name AS child_category_name, ',@field_id,' AS title FROM exp_categories AS t JOIN exp_category_field_data AS d USING(cat_id) WHERE t.parent_id != 0 AND t.group_id = 2 AND ',@field_id,' != \'\' ORDER BY ',@field_id,' ASC;');

PREPARE stmt FROM @query;
EXECUTE stmt;

MySQL ERROR:

Error Number: 1064

Description: 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 'SET @query = CONCAT('SELECT t.cat_id AS child_category_id, t.cat_name AS child_c' at line 1

Query: SELECT @field_id:=CONCAT('d.field_id_',field_id) FROM exp_category_fields WHERE group_id = '2' AND field_name = 'title_nl'; SET @query = CONCAT('SELECT t.cat_id AS child_category_id, t.cat_name AS child_category_name, ',@field_id,' AS title FROM exp_categories AS t JOIN exp_category_field_data AS d USING(cat_id) WHERE t.parent_id != 0 AND t.group_id = 2 AND ',@field_id,' != \'\' ORDER BY ',@field_id,' ASC;'); PREPARE stmt FROM @query; EXECUTE stmt;

{exp:query sql="
SELECT @field_id:=CONCAT('d.field_id_',field_id) FROM exp_category_fields WHERE group_id = '2' AND field_name = 'title_nl';

SET @query = CONCAT('SELECT t.cat_id AS child_category_id, t.cat_name AS child_category_name, ',@field_id,' AS title FROM exp_categories AS t JOIN exp_category_field_data AS d USING(cat_id) WHERE t.parent_id != 0 AND t.group_id = 2 AND ',@field_id,' != \\'\\' ORDER BY ',@field_id,' ASC;');

PREPARE stmt FROM @query;
EXECUTE stmt;"}
<option value="{title}">{title}</option>
{/exp:query}