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.D SN#" 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?
I am trying to output the data into individual rows / checkboxes from the field in the database.
EG SQL Query:
<cfquery datasource="#Application.D
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(
I am looking at something along the lines of
<cfloop query="getgames" index="index" list="#games#" delimiters="#chr(10)##chr(
#games#
</cfloop>
Or
<cfloop query="getgames" index="index" list="#games#" delimiters=",">
#games#
</cfloop>
Where am I going wrong with this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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