One of the downsides to using ENUM’s in the database is that they are not readily available to be used in client side inputs – for example a select element for choosing a value that is stored as an enum. So if you add a new enum value to the db, you have to update your code as well.
Here’s how you can do this dynamically for MariaDB (and presumably MySQL, but I haven’t tested.)
Create a custom query with the following content (make sure to swap in your own database name):
select col.table_schema as database_name,
col.table_name,
col.ordinal_position as column_id,
col.column_name,
col.data_type,
trim(leading 'enum' from col.column_type) as enum_values
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
and tab.table_name = col.table_name
and tab.table_type = 'BASE TABLE'
where col.data_type in ('enum')
and col.table_schema not in ('information_schema', 'sys',
'performance_schema', 'mysql')
and col.table_schema = 'YOUR_DB_NAME' -- put your database name here
order by col.table_schema,
col.table_name,
col.ordinal_position;
That query will return all the enums for your database:
And if your repeat over those results, you can turn the enum_values into an array and output using set value:
enum_values.replace('(', '').replace(')', '').replace('\'','').split(',')
On the client side, you just choose the enums for your select making sure you substitute your table and column names (in the example below “quotes” and “flow”):
<select id="select_flow" class="form-select form-select-sm" name="flow" dmx-bind:value="this_quote.data.flow" dmx-bind:options="get_globals.data.enums.where(`table_name`, 'quotes', '==').where(`column_name`, 'flow', '==')[0].values" optiontext="$value.capitalize()" optionvalue="$value">
<option value="">Select</option>
</select>
I output all the enums in a globals server connect, but you could of course do the work on the server side and return only the specific enums you need during a workflow.
Last updated: