how will i display the multiple select ids in the dashboard ?

Report · Feb 13, 2012

I have a form where there is a multiple select. When the form is submit, the ids for the multiple select is stored in the database as concatenate. For example, multiple select : 3,9,5,1.

The ids of the multiple select is found in a table TABLE1 and the insertion is done in another table TABLE2.

Now I have to display them in a dashboard.

My question is how will i display the multiple select ids in the dashboard ?

I have to do an innerjoin with TABLE1 to retrieve the names of the multiple select ids. I don't know how to do it because the column name for multiple select in TABLE2 is of type text and the column id in TABLE1 is of type int.

Please help me solve this problem.

Thanks in advance

Report · Feb 14, 2012

One general advice is, never store comma-delimited lists in the database. In fact, the reason is to avoid problems like the one you now face.

The major suggestion here is to re-design your database. Make sure each selected item (for example 3, 9, 5 or 1) is stored in its own row.

To solve the problem as it now stands, you could do something like

select myItems

from table2

where id in (select id from table 1 where ...etc.)

</cfquery>

Then use ColdFusion's list functions to do what your dashboard requires you to do.