I found it!
It was back in January of this year that I ran into a similar problem.
Ewika and Phil helped me then and I even wrote a lengthy email to the
list about the solution.
In this instance it may be hard to break out the talents into proper
fields since I am going to have to do a Batch update from an Excel file
which has a fixed format.
I don't know if I can get the company to change this format, and then
use a CF page to parse the Excel file and insert records into the
database properly.
I do know, however, that they aren't paying me enough for a reusable
Import routine for their data.
Here is the code which uses a single SQL LIKE statement:
<CFLOOP list="#form.coatings#" index="i">
or ',' & coatings & ',' LIKE '%,#i#,%'
</CFLOOP>
This obviates the need to modify entries in the database itself.
But I suppose it would still be better to find a way to break this out
in a separate table?
-Gel
-----Original Message-----
From: Bryan Love [mailto:[EMAIL PROTECTED]
First of all the DB design is incorrect for what you are trying to do...
That being said, I've had to deal with this before and here is the best
possible solution without changing the DB...
1) modify the GUI you use for data entry so that the talents list is
inserted into the database with a comma at the beginning and at the end.
So a list of talents looks like this in the DB:
",running,dancing,cooking,"
2) run a query that will modify the data that is already in the DB by
adding a comma before and after the talents string: UPDATE [table] SET
talents = ',' + talents + ',' WHERE talents IS NOT NULL AND talents <>
''
3) now you can run queries like this:
SELECT *
FROM [table]
WHERE talents LIKE '%,#aTalent#,%'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:4
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Get the mailserver that powers this list at
http://www.coolfusion.com
