Flaviano Astolfo Flaviano Astolfo - 4 months ago 7
SQL Question

Insert ID in column with values separated by comma

I have a column that lists all the units ids from a company which are allowed to see a specific document.

Like this:

List of the Unit's IDs

And those ids are selected here ('Selecionar todas' mean 'Select All'):

enter image description here

The problem is when I add a new Unit, it come unselected and it will be a headache to have to enter in all of the documents that are supposed to show to all units and change it to be selected.

Is there a way to insert the new Unit's id into the 'idsunidades' column where there are all the other units selected ?

Sorry for the bad English.

Kld Kld
Answer

To implement a select all option you should use a flag column all_selected(true or false) or you can do it like this:

To select the rows where All units are selected should have the longest value of idsunidades, you can select them like this

SELECT @maxLength:=LENGTH(idsunidades) 
ORDER BY LENGTH(idsunidades ) 
DESC LIMIT 1

You can use CONCAT() to append the new id to the old column data

To add the unit 99 to the column idsunidades to all the rows with all units you can do this

UPDATE table set idsunidades=CONCAT(idsunidades, ',99')
WHERE LENGTH(idsunidades) = (SELECT LENGTH(idsunidades) 
                             ORDER BY LENGTH(idsunidades ) 
                             DESC LIMIT 1)
Comments