Multiselect input field in SQL Forms

ProcFu, in combination with SQL, is incredibly powerful for building fast and efficient web applications. Often, you’ll need a form field that allows users to select multiple values. Here’s a straightforward approach to achieve this functionality.

In this example, we’re working with a product form where the SQL table structure includes a categories column of type SET. The SQL structure looks like this:

Column	Type	Comment
id	int	Auto Increment
name	varchar(255)	NULL
categories	set('Electronics','Books','Clothing','Toys')	NULL

We want users to select multiple categories for the product. Let’s break it down step by step.

Step 1: Retrieve the Categories Field Value

In the before-render event of your form, retrieve the current value of the categories field and store it in a JavaScript variable for manipulation. Use this code:

my_variables["categories"] = current_item_values["categories"];

Step 2: Convert the Single Select to a Multi-Select

Next, use JavaScript to dynamically convert the existing select field into a multi-select field. Here’s the complete code:

const categoryField = document.querySelector('[data-field="categories"] select');
const categoriesContainer = document.querySelector('[data-field="categories"]');

// Convert single select to multi-select
if (categoryField) {
    // Retain old values if in edit mode
const oldValues = typeof my_variables !== 'undefined' && my_variables["categories"]
            ? my_variables["categories"].split(',').map(value => value.trim())
            : [];
    // Create a multi-select
    categoryField.setAttribute('multiple', 'multiple');

    // Preselect old values for edit screen
    if (Array.isArray(oldValues)) {
        Array.from(categoryField.options).forEach(option => {
            if (oldValues.includes(option.value)) {
                option.selected = true;
            }
        });
    }

    // Add listener for changes
    categoryField.addEventListener('change', () => {
        const selectedValues = Array.from(categoryField.selectedOptions).map(option => option.value);
        console.log('Selected categories:', selectedValues); // Handle selected values as needed
    });
}

Explanation:

• Retrieve the field: Locate the select element within the form using querySelector.
• Handle old values: Check if the my_variables object contains the old categories. If yes, split the comma-separated string into an array and trim each value.
• Enable multi-select: Add the multiple attribute to the select element to allow multiple selections.
• Preselect values: Loop through the options and set the selected attribute for those that match the old values.
• Change listener: Attach an event listener to log the selected values or handle them as required.

Step 3: Convert Selected Values to a Comma-Separated String

SQL requires multiple values to be saved as a comma-separated string. Before submitting the form, convert the selected values back into this format:

form_values["categories"] = implode(",", form_values["categories"]);

Done!

Now, when you save the form, the selected categories will be stored as a comma-separated string in your database. This approach ensures flexibility for the user and compatibility with the SQL SET type.