Link to home
Start Free TrialLog in
Avatar of James Ryan
James Ryan

asked on

CFLOOP not working to seperate CSV to Multiple Rows

I have an mssql table which has data stored as csv.

I am trying to output the data into individual rows / checkboxes from the field in the database.

EG SQL Query:
<cfquery datasource="#Application.DSN#" dbtype="odbc" name="getgames">
SELECT games
FROM users
WHERE games LIKE '%arma%'
</cfquery>

The query works ands the output is then like:
eso,swtor,wow,rocket league,arma 3

I am using Cold Fusion and trying to output the data as
eso
swtor
wow
rocket league
arma 3

Or potentially as
<cfinput type=checkbox name="games" value="#games#">
where value="#games#", being listed as the individual games

I can't seem to get it to work using a cfloop with delimiters="," OR delimiters="#chr(10)##chr(13)#"

I am looking at something along the lines of
<cfloop query="getgames" index="index" list="#games#" delimiters="#chr(10)##chr(13)#">
#games#
</cfloop>

Or

<cfloop query="getgames" index="index" list="#games#" delimiters=",">
#games#
</cfloop>

Where am I going wrong with this?
ASKER CERTIFIED SOLUTION
Avatar of James Ryan
James Ryan

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of _agx_
>> I have an mssql table which has data stored as csv.

Do you have any choice about the table structure? Because it's generally better NOT to store values as CSV.  Mainly because it's difficult to query which leads to complex and poorly performing  queries. Also, it increases the likelihood of data integrity issues.  Instead, use a Many-To-Many relationship.

https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad
https://stackoverflow.com/questions/4534076/how-should-i-store-a-list-in-a-database
https://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561